Classifiers
Package provides classifier lookup
example
sql
SET SERVEROUTPUT ON;
DECLARE
c CLOB;
BEGIN
DBMS_OUTPUT.PUT_LINE('Countries:');
c:=pck_api_classifiers.lookup(
'countries',
'be',
'Y',
10,
0
);
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Currencies:');
c:=pck_api_classifiers.lookup(
'currencies',
'dollar',
'Y',
10,
0
);
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Languages:');
c:=pck_api_classifiers.lookup(
'languages',
'po',
'Y',
10,
0
);
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE('');
END;
/specification
sql
CREATE OR REPLACE PACKAGE odbvue.pck_api_classifiers AS -- Package provides classifier lookup
PROCEDURE lookup ( -- Returns a ref cursor with the results
p_classifier IN VARCHAR2, -- Classifier type: 'languages', 'countries', 'currencies'
p_search IN VARCHAR2, -- Search term to filter results
p_active IN CHAR DEFAULT 'Y', -- Filter by active status ('Y' or 'N')
p_limit IN PLS_INTEGER DEFAULT 10, -- Maximum number of results to return
p_offset IN PLS_INTEGER DEFAULT 0, -- Number of results to skip
r_result OUT SYS_REFCURSOR -- Output ref cursor with the results
);
FUNCTION lookup ( -- Returns results as a JSON CLOB
p_classifier IN VARCHAR2, -- Classifier type: 'languages', 'countries', 'currencies'
p_search IN VARCHAR2, -- Search term to filter results
p_active IN CHAR DEFAULT 'Y', -- Filter by active status ('Y' or 'N')
p_limit IN PLS_INTEGER DEFAULT 10, -- Maximum number of results to return
p_offset IN PLS_INTEGER DEFAULT 0 -- Number of results to skip
) RETURN CLOB; -- JSON CLOB with the results
END pck_api_classifiers;
/
-- sqlcl_snapshot {"hash":"5ed526f2f7b6ce6ddb02dbef0d1259613c6be488","type":"PACKAGE_SPEC","name":"PCK_API_CLASSIFIERS","schemaName":"ODBVUE","sxml":""}implementation
sql
CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_classifiers AS
PROCEDURE lookup (
p_classifier IN VARCHAR2,
p_search IN VARCHAR2,
p_active IN CHAR DEFAULT 'Y',
p_limit IN PLS_INTEGER DEFAULT 10,
p_offset IN PLS_INTEGER DEFAULT 0,
r_result OUT SYS_REFCURSOR
) AS
BEGIN
IF p_classifier = 'languages' THEN
OPEN r_result FOR SELECT
id,
iso3,
name,
native
FROM
app_languages
WHERE
( p_search IS NULL
OR lower(name) LIKE lower('%'
|| p_search || '%') )
AND ( p_active IS NULL
OR active = p_active )
ORDER BY
name
OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY;
RETURN;
END IF;
IF p_classifier = 'countries' THEN
OPEN r_result FOR SELECT
id,
iso3,
name,
native
FROM
app_countries
WHERE
( p_search IS NULL
OR lower(name) LIKE lower('%'
|| p_search || '%') )
AND ( p_active IS NULL
OR active = p_active )
ORDER BY
name
OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY;
RETURN;
END IF;
IF p_classifier = 'currencies' THEN
OPEN r_result FOR SELECT
id,
name,
symbol
FROM
app_currencies
WHERE
( p_search IS NULL
OR lower(name) LIKE lower('%'
|| p_search || '%') )
AND ( p_active IS NULL
OR active = p_active )
ORDER BY
name
OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY;
RETURN;
END IF;
raise_application_error(-20001, 'Unknown classifier: ' || p_classifier);
END lookup;
FUNCTION lookup (
p_classifier IN VARCHAR2,
p_search IN VARCHAR2,
p_active IN CHAR DEFAULT 'Y',
p_limit IN PLS_INTEGER DEFAULT 10,
p_offset IN PLS_INTEGER DEFAULT 0
) RETURN CLOB AS
c CLOB;
BEGIN
IF p_classifier = 'languages' THEN
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id' VALUE id,
'iso3' VALUE iso3,
'name' VALUE name,
'native' VALUE native
)
)
INTO c
FROM
(
SELECT
id,
iso3,
name,
native
FROM
app_languages
WHERE
( p_search IS NULL
OR lower(name) LIKE lower('%'
|| p_search || '%') )
AND ( p_active IS NULL
OR active = p_active )
ORDER BY
name
OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY
);
RETURN c;
END IF;
IF p_classifier = 'countries' THEN
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id' VALUE id,
'iso3' VALUE iso3,
'name' VALUE name,
'native' VALUE native
)
)
INTO c
FROM
(
SELECT
id,
iso3,
name,
native
FROM
app_countries
WHERE
( p_search IS NULL
OR lower(name) LIKE lower('%'
|| p_search || '%') )
AND ( p_active IS NULL
OR active = p_active )
ORDER BY
name
OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY
);
RETURN c;
END IF;
IF p_classifier = 'currencies' THEN
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id' VALUE id,
'name' VALUE name,
'symbol' VALUE symbol
)
)
INTO c
FROM
(
SELECT
id,
name,
symbol
FROM
app_currencies
WHERE
( p_search IS NULL
OR lower(name) LIKE lower('%'
|| p_search || '%') )
AND ( p_active IS NULL
OR active = p_active )
ORDER BY
name
OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY
);
RETURN c;
END IF;
raise_application_error(-20001, 'Unknown classifier: ' || p_classifier);
END lookup;
END pck_api_classifiers;
/
-- sqlcl_snapshot {"hash":"36fe1eed1fe7a8d31f7e33cc7c13583c19233a89","type":"PACKAGE_BODY","name":"PCK_API_CLASSIFIERS","schemaName":"ODBVUE","sxml":""}LOOKUP
Returns a ref cursor with the results
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_CLASSIFIER | IN | VARCHAR2 | Classifier type: 'languages', 'countries', 'currencies' | |
| P_SEARCH | IN | VARCHAR2 | Search term to filter results | |
| P_ACTIVE | IN | CHAR | 'Y' | Filter by active status ('Y' or 'N') |
| P_LIMIT | IN | BINARY_INTEGER | 10 | Maximum number of results to return |
| P_OFFSET | IN | BINARY_INTEGER | 0 | Number of results to skip |
| R_RESULT | OUT | REF CURSOR | Output ref cursor with the results |
LOOKUP
Returns a ref cursor with the results
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | CLOB | JSON CLOB with the results | ||
| P_CLASSIFIER | IN | VARCHAR2 | Classifier type: 'languages', 'countries', 'currencies' | |
| P_SEARCH | IN | VARCHAR2 | Search term to filter results | |
| P_ACTIVE | IN | CHAR | 'Y' | Filter by active status ('Y' or 'N') |
| P_LIMIT | IN | BINARY_INTEGER | 10 | Maximum number of results to return |
| P_OFFSET | IN | BINARY_INTEGER | 0 | Number of results to skip |