API Reference
Audit
Audit Package
example
sql
BEGIN
pck_api_audit.info(
p_message => 'App Audit Package Deployed',
p_attributes => pck_api_audit.attributes( -- key value pairs
'uuid', LOWER(SYS_GUID()),
'username', 'test'
)
);
END;
/
DECLARE
c_data CLOB := '[
{
"severity": "INFO",
"message": "Bulk Log Entry 1",
"attributes": {
"uuid": "433c66c3ccfe9b02e0630301590ac258",
"username": "test1"
},
"created": "2023-10-01T12:00:00Z"
},
{
"severity": "ERROR",
"message": "Bulk Log Entry 2",
"attributes": {
"uuid": "fffc66c3ccfe9b02e0630301590ac258",
"username": "test2"
},
"created": "2023-10-01T12:00:00Z"
}
]';
BEGIN
pck_api_audit.bulk(c_data);
END;
/
SELECT * FROM app_audit
ORDER BY created DESC
FETCH FIRST 10 ROWS ONLY;
/Authentication
Package provides methods for issuing and validating tokens
example
sql
set serveroutput on;
/
DECLARE
v_uuid CHAR(32 CHAR);
v_token VARCHAR2(2000 char);
BEGIN
SELECT uuid INTO v_uuid FROM app_users WHERE username = UPPER('admin@odbvue.com');
v_token := pck_api_auth.issue_token(v_uuid,'ACCESS');
dbms_output.put_line('Issued Token: ');
dbms_output.put_line(v_token);
v_uuid := pck_api_auth.uuid_from_token(v_token);
dbms_output.put_line('UUID from Token: ');
dbms_output.put_line(v_uuid);
END;
/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;
/Consents
Package for managing user consents
example
sql
SET SERVEROUTPUT ON;
DECLARE
c CLOB;
id app_consents.id%TYPE;
BEGIN
c := pck_api_consents.lookup();
DBMS_OUTPUT.PUT_LINE('Consents: ' || SUBSTR(c,1,200) || '...');
id := JSON_VALUE(c, '$[0].id');
DBMS_OUTPUT.PUT_LINE('First Consent ID: ' || id);
c:= pck_api_consents.download(id);
DBMS_OUTPUT.PUT_LINE('Consent Content: ' || SUBSTR(c,1,200) || '...');
END;
/Emails
Package for sending emails
example
sql
DECLARE
v_id app_emails.id%TYPE;
BEGIN
IF pck_api_settings.read('APP_EMAILS_SMTP_USERNAME') IS NULL OR pck_api_settings.read('APP_EMAILS_SMTP_PASSWORD') IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'SMTP Credentials not set up!');
END IF;
pck_api_emails.mail(v_id, 'admin@odbvue.com', 'Admin User', 'Test email 123', 'This is a <b>test</b> email!');
pck_api_emails.send(v_id);
END;
/Labels
Package provides methods for managing labels
example
sql
BEGIN
-- Numeric
pck_api_labels.link_label_nm('Important', 'document', 123);
pck_api_labels.link_label_nm('Important', 'document', 234);
pck_api_labels.link_label_nm('Important', 'document', 345);
pck_api_labels.link_label_nm('Nice to have', 'document', 456);
pck_api_labels.link_label_nm('Nice to have', 'document', 567);
pck_api_labels.unlink_label_nm('Important', 'document', 234);
pck_api_labels.unlink_label_nm('Important', 'document', 678); -- non-existing link
-- Variable Character
pck_api_labels.link_label_vc('Important', 'folder', 'abc123');
pck_api_labels.link_label_vc('Important', 'folder', 'def456');
pck_api_labels.link_label_vc('Important', 'folder', 'ghi789');
pck_api_labels.link_label_vc('Nice to have', 'folder', 'jkl012');
pck_api_labels.link_label_vc('Nice to have', 'folder', 'mno345');
pck_api_labels.unlink_label_vc('Important', 'folder', 'def456');
pck_api_labels.unlink_label_vc('Important', 'folder', 'xyz999'); -- non-existing link
-- done
COMMIT;
END;
/
SELECT
ll.entity_name,
COALESCE(ll.entity_id_vc, TO_CHAR(ll.entity_id_nm)) AS entity_id,
l.name AS label_name
FROM app_label_links ll
JOIN app_labels l ON ll.label_id = l.id
ORDER BY ll.entity_name, l.code;LOB
Package for LOB processing. Credit: https://github.com/paulzip-dev/Base64
example
sql
DECLARE
v_text CLOB := 'Hello, World!';
v_blob BLOB;
v_b64 CLOB;
v_blob2 BLOB;
v_text2 CLOB;
BEGIN
v_blob := odbvue.pck_api_lob.clob_to_blob(v_text);
v_b64 := odbvue.pck_api_lob.blob_to_base64(v_blob, 0);
v_blob2 := odbvue.pck_api_lob.base64_to_blob(v_b64);
v_text2 := odbvue.pck_api_lob.blob_to_clob(v_blob2);
DBMS_OUTPUT.PUT_LINE('Original: ' || v_text);
DBMS_OUTPUT.PUT_LINE('Base64: ' || SUBSTR(v_b64, 1, 50));
DBMS_OUTPUT.PUT_LINE('Final: ' || v_text2);
END;
/HTTP
Package for HTTP call processing
example
sql
DECLARE
v_req utl_http.req;
v_text CLOB;
BEGIN
BEGIN
pck_api_http.request(v_req, 'GET', 'https://api.chucknorris.io/jokes/random');
pck_api_http.response_text(v_req, v_text);
DBMS_OUTPUT.PUT_LINE(JSON_VALUE(v_text, '$.value'));
EXCEPTION
WHEN OTHERS THEN
DECLARE
v_error_stack VARCHAR2(4000) := DBMS_UTILITY.FORMAT_ERROR_STACK();
BEGIN
IF v_error_stack LIKE '%ORA-24247%' THEN
DBMS_OUTPUT.PUT_LINE('ERROR (ORA-24247): Network access denied by Access Control List (ACL).');
DBMS_OUTPUT.PUT_LINE('ACL must be enabled first using pck_api_admin.acl_append_host.');
DBMS_OUTPUT.PUT_LINE('exec admin.pck_api_admin.acl_append_host(''ODBVUE'', ''api.chucknorris.io'', 443, 443, ''http'');');
ELSE
RAISE;
END IF;
END;
END;
END;
/Jobs
Package for managing jobs
example
sql
BEGIN
pck_api_jobs.add('test','pck_api_audit.info', '[{"type":"VARCHAR2", "name": "p_message", "value":"Test job"}]', 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=0; BYMINUTE=0; BYSECOND=0', 'Weekly Test Job');
pck_api_jobs.run('test');
pck_api_jobs.remove('test');
END;
/
SELECT *
FROM user_scheduler_job_run_details
WHERE job_name = 'TEST_JOB'
ORDER BY log_date DESC
FETCH FIRST 10 ROWS ONLY;JSON
Package for handling json
example
sql
DECLARE
c clob;
r clob;
v clob;
p VARCHAR2(2000 CHAR);
b boolean;
n PLS_INTEGER;
PROCEDURE print(c clob DEFAULT NULL)
AS
BEGIN
dbms_output.put_line(dbms_lob.substr(c, 4000));
END;
FUNCTION tf(b IN boolean) RETURN VARCHAR2
AS
BEGIN
RETURN CASE WHEN b THEN 'true' ELSE 'false' END;
END;
BEGIN
print('Init');
c:= pck_api_json.init;
print(c);
print;
print('Exists');
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
p := '$.a';
b := pck_api_json.exists(c, p);
print(p || ' in ' || c || ' => ' || tf(b)); -- true
p := '$.b.c';
b := pck_api_json.exists(c, p);
print(p || ' in ' || c || ' => ' || tf(b)); -- true
p := '$.b.d';
b := pck_api_json.exists(c, p);
print(p || ' in ' || c || ' => ' || tf(b)); -- false
p := '$.x';
b := pck_api_json.exists(c, p);
print(p || ' in ' || c || ' => ' || tf(b)); -- false
p := '$.d[1]';
b := pck_api_json.exists(c, p);
print(p || ' in ' || c || ' => ' || tf(b)); -- true
p := '$.d[10]';
b := pck_api_json.exists(c, p);
print(p || ' in ' || c || ' => ' || tf(b)); -- false
print;
print('Read');
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
p := '$.a';
r := pck_api_json.read(c, p);
print(p || ' from ' || c || ' => ' || r); -- 1
p := '$.b';
r := pck_api_json.read(c, p);
print(p || ' from ' || c || ' => ' || r); -- {"c":2}
p := '$.d[2]';
r := pck_api_json.read(c, p);
print(p || ' from ' || c || ' => ' || r); -- 3 (index starts with 0)
p := '$.d[10]';
r := pck_api_json.read(c, p);
print(p || ' from ' || c || ' => ' || r); -- NULL
print;
print('Typeof');
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
p := '$.a';
r := pck_api_json.typeof(c, p);
print(p || ' type in ' || c || ' => ' || r); -- number
p := '$.b';
r := pck_api_json.typeof(c, p);
print(p || ' type in ' || c || ' => ' || r); -- object
p := '$.d';
r := pck_api_json.typeof(c, p);
print(p || ' type in ' || c || ' => ' || r); -- array
p := '$.x';
r := pck_api_json.typeof(c, p);
print(p || ' type in ' || c || ' => ' || r); -- undefined
print;
print('Elcount');
c:='{"a":1,"b":{"c":2},"d":[1,2,3],"e":{"f":4,"g":5}}';
p := '$.a';
n := pck_api_json.elcount(c, p);
print(p || ' element count in ' || c || ' => ' || n); -- 1
p := '$.d';
n := pck_api_json.elcount(c, p);
print(p || ' element count in ' || c || ' => ' || n); -- 3
p := '$.e';
n := pck_api_json.elcount(c, p);
print(p || ' element count in ' || c || ' => ' || n); -- 2
print;
print('Keys');
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
p := '$';
r := pck_api_json.keys(c, p);
print(p || ' keys in ' || c || ' => ' || r); -- 1
p := '$.b';
r := pck_api_json.keys(c, p);
print(p || ' keys in ' || c || ' => ' || r); -- 1
p := '$.d';
r := pck_api_json.keys(c, p);
print(p || ' keys in ' || c || ' => ' || r); -- []
print;
print ('Write');
--
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
r:=c;
p := '$.a';
v:='42';
pck_api_json.write(r, p, v);
print(v || ' at ' || p || ' in ' || c || ' => ' || r);
--
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
r:=c;
p := '$.b.c';
v:='{"e":"f"}';
pck_api_json.write(r, p, v);
print(v || ' at ' || p || ' in ' || c || ' => ' || r);
--
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
r:=c;
p := '$.d';
v:=null;
pck_api_json.write(r, p, v);
print(COALESCE(v, 'null') || ' at ' || p || ' in ' || c || ' => ' || r);
--c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
r:=c;
p := '$';
v:=null;
pck_api_json.write(r, p, v);
print(COALESCE(v, 'null') || ' at ' || p || ' in ' || c || ' => ' || r);
--
print;
print('Print');
c:='{"a":1,"b":{"c":2},"d":[1,2,3]}';
r:=c;
pck_api_json.print(r);
print(c || ' => ' || CHR(10) || r);
print;
print('To XML');
c:='{"a":1,"b":{"c":{"d":"e"}},"f":[1,2,3],"g":"hello world","h":true,"i":null,"j":3.14}';
r:=pck_api_json.to_xml(c);
print(c || ' => ' || CHR(10) || r);
print;
print('To YAML');
c:='{"a":1,"b":{"c":{"d":"e"}},"f":[1,2,3],"g":"hello world","h":true,"i":null,"j":3.14}';
r:=pck_api_json.to_yaml(c);
print(c || ' => ' || CHR(10) || r);
print;
end;
/Markdown
Package for Markdown document generation and export to PDF and HTML
example
sql
DECLARE
c clob;
b blob;
h clob;
BEGIN
pck_api_md.init(c);
pck_api_md.h1(c, 'Title');
pck_api_md.h2(c, 'Subtitle 1');
pck_api_md.p(c, 'Paragraph with some text.');
pck_api_md.li(c, 'item 1');
pck_api_md.li(c, 'item 2');
pck_api_md.lf(c);
pck_api_md.h2(c, 'Subtitle 2');
pck_api_md.p(c, 'Another paragraph under subtitle 2.');
pck_api_md.link(c, 'Link text', 'https://odbvue.com');
pck_api_md.md_table(
c,
'[{"col1":"abc","col2":"123"},{"col1":"def","col2":"456"}]',
'{"col1":{"title":"Column 1"},"col2":{"title":"Column 2"}}'
);
pck_api_md.image(c, 'https://wiki.odbvue.com/logo.svg', 'logo');
pck_api_md.code_inline(c, 'inline code example');
pck_api_md.code_block(c, 'SELECT * FROM dual;', 'sql');
pck_api_md.h2(c, 'Subtitle 3');
pck_api_md.quote(c, 'This is a ' || pck_api_md.b('blockquote') || ' example.');
pck_api_md.note(c, 'This is a note.');
dbms_output.put_line('');
dbms_output.put_line('--- Generated MD ---');
dbms_output.put_line(dbms_lob.substr(c, 32000, 1));
dbms_output.put_line('');
dbms_output.put_line('--- Converted to HTML ---');
pck_api_md.to_html(c, h);
dbms_output.put_line(dbms_lob.substr(h, 32000, 1));
dbms_output.put_line('');
dbms_output.put_line('--- Converted to PDF ---');
pck_api_md.to_pdf(c, b);
dbms_output.put_line('Size: ' || dbms_lob.getlength(b));
dbms_output.put_line(pck_api_lob.blob_to_base64(b));
pck_api_md.finalize(c);
END;
/Openai
Package provides implementation of Open AI API
example
sql
DECLARE
c_api_key VARCHAR2(2000 CHAR) := '****************';
r_message CLOB;
r_error VARCHAR2(2000 CHAR);
BEGIN
DBMS_OUTPUT.PUT_LINE('Testing OpenAI API');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('RESPONSES');
pck_api_openai.responses(
p_api_key => c_api_key,
p_model => 'gpt-5',
p_input => 'Please tell me a joke about databases.',
r_output => r_message,
r_error => r_error
);
IF r_error IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || r_error);
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE(r_message);
DBMS_OUTPUT.PUT_LINE('');
END;
/Pdf
Package for creating PDF documents, credits to https://github.com/antonscheffer/as_pdf
example
sql
DECLARE
v_pdf_blob blob;
BEGIN
-- Initialize PDF
pck_api_pdf.init();
-- Set document info
pck_api_pdf.set_info(
p_title => 'Simple PDF Example',
p_author => 'PL/SQL',
p_subject => 'Basic PDF Generation'
);
-- Add first page
pck_api_pdf.new_page();
-- Write title
pck_api_pdf.set_font(p_family => 'helvetica', p_style => 'B', p_fontsize_pt => 24);
pck_api_pdf.insert_txt(
p_x => 50,
p_y => 750,
p_txt => 'Simple PDF Example',
p_fontsize => 24,
p_color => '0000FF'
);
-- Write paragraph
pck_api_pdf.set_font(p_family => 'helvetica', p_style => 'N', p_fontsize_pt => 12);
pck_api_pdf.multi_cell(
p_txt => 'This is a simple example of PDF generation using the pck_api_pdf package. ' ||
'This paragraph demonstrates basic text output in a PDF document. ' ||
'The PDF library supports various fonts, sizes, colors, and formatting options.',
p_x => 50,
p_y => 700,
p_width => 300,
p_fontsize => 12,
p_txt_color => '000000'
);
-- Generate PDF
v_pdf_blob := pck_api_pdf.finish_pdf();
-- Display info
dbms_output.put_line('PDF generated successfully!');
dbms_output.put_line('Size: ' || dbms_lob.getlength(v_pdf_blob) || ' bytes');
dbms_output.put_line(pck_api_lob.blob_to_base64(v_pdf_blob));
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error: ' || sqlerrm);
END;
/Settings
Package provides methods for managing application settings
example
sql
SET SERVEROUTPUT ON;
DECLARE
v_value app_settings.value%TYPE;
BEGIN
pck_api_settings.write('DUMMY', 'Dummy setting', '1');
COMMIT;
pck_api_settings.read('DUMMY', v_value);
DBMS_OUTPUT.PUT_LINE('Value of DUMMY setting: ' || v_value);
END;
/
DECLARE
v_value VARCHAR2(200 CHAR) := 'This is a secret value';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_value);
DBMS_OUTPUT.PUT_LINE('=>');
v_value := pck_api_settings.enc(v_value);
DBMS_OUTPUT.PUT_LINE(v_value);
DBMS_OUTPUT.PUT_LINE('=>');
v_value := pck_api_settings.dec(v_value);
DBMS_OUTPUT.PUT_LINE(v_value);
DBMS_OUTPUT.PUT_LINE('=>');
END;
/Storage
Package for storing and processing large binary objects
example
sql
DECLARE
id app_storage.id%TYPE;
b app_storage.content%TYPE;
n app_storage.file_name%TYPE;
s PLS_INTEGER;
e VARCHAR2(100);
m VARCHAR2(100);
BEGIN
-- local
b := utl_raw.cast_to_raw('Hello World!');
n := 'hello.txt';
pck_api_storage.upload(b, n, id);
dbms_output.put_line(' uploaded file ID: ' || id);
COMMIT;
b := EMPTY_BLOB();
pck_api_storage.download(id, b, n, s, e, m);
dbms_output.put_line(' downloaded file name: ' || n);
dbms_output.put_line(' downloaded file size: ' || s);
dbms_output.put_line(' downloaded file extension: ' || e);
dbms_output.put_line(' downloaded file mime type: ' || m);
dbms_output.put_line(' -- ');
dbms_output.put_line(' downloaded file actual size: ' || dbms_lob.getlength(b));
-- s3
-- pck_api_settings.write('APP_STORAGE_S3_URI', 'Storage URI for S3 files', 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket/o/');
pck_api_settings.write('APP_STORAGE_S3_URI', 'Storage URI for S3 files', 'https://objectstorage.eu-stockholm-1.oraclecloud.com/n/axuctz2iko0g/b/odbvue-obj/o/');
pck_api_storage.upload(b, n, id, TRUE);
dbms_output.put_line(' uploaded file ID to S3: ' || id);
COMMIT;
b := EMPTY_BLOB();
pck_api_storage.download(id, b, n, s, e, m);
dbms_output.put_line(' downloaded file name from S3: ' || n);
dbms_output.put_line(' downloaded file size from S3: ' || s);
dbms_output.put_line(' downloaded file extension from S3: ' || e);
dbms_output.put_line(' downloaded file mime type from S3: ' || m);
dbms_output.put_line(' -- ');
dbms_output.put_line(' downloaded file actual size from S3: ' || dbms_lob.getlength(b));
END;
/XML
Package for handling xml
example
sql
DECLARE
c clob := '<root>
<a>1</a>
<b>
<c>
<d>e</d>
</c>
</b>
<f>
<item>1</item>
<item>2</item>
<item>3</item>
</f>
<g>hello world</g>
<h>true</h>
<i>null</i>
<j>3.14</j>
</root>';
r clob;
v clob;
p VARCHAR2(2000 CHAR);
b boolean;
n PLS_INTEGER;
PROCEDURE print(c clob DEFAULT NULL)
AS
BEGIN
dbms_output.put_line(dbms_lob.substr(c, 4000));
END;
FUNCTION tf(b IN boolean) RETURN VARCHAR2
AS
BEGIN
RETURN CASE WHEN b THEN 'true' ELSE 'false' END;
END;
BEGIN
print('Begin');
print(c);
print;
print('Init');
r:= pck_api_xml.init;
print(r);
print;
print('Exists');
p := '$.a';
b := pck_api_xml.exists(c, p);
print(p || ' => ' || tf(b)); -- true
p := '$.b.c';
b := pck_api_xml.exists(c, p);
print(p || ' => ' || tf(b)); -- true
p := '$.b.d';
b := pck_api_xml.exists(c, p);
print(p || ' => ' || tf(b)); -- false
p := '$.x';
b := pck_api_xml.exists(c, p);
print(p || ' => ' || tf(b)); -- false
p := '$.f[1]';
b := pck_api_xml.exists(c, p);
print(p || ' => ' || tf(b)); -- true
p := '$.f[10]';
b := pck_api_xml.exists(c, p);
print(p || ' => ' || tf(b)); -- false
print;
print('Read');
p := '$.a';
r := pck_api_xml.read(c, p);
print(p || ' => ' || r); -- 1
p := '$.b';
r := pck_api_xml.read(c, p);
print(p || ' => ' || r); -- <c>2</c>
p := '$.f[3]';
r := pck_api_xml.read(c, p);
print(p || ' => ' || r); -- 3
p := '$.f[10]';
r := pck_api_xml.read(c, p);
print(p || ' => ' || r); -- NULL
print;
print('Typeof');
p := '$.a';
r := pck_api_xml.typeof(c, p);
print(p || ' => ' || r); -- string or number
p := '$.b';
r := pck_api_xml.typeof(c, p);
print(p || ' => ' || r); -- object
p := '$.f';
r := pck_api_xml.typeof(c, p);
print(p || ' => ' || r); -- array
p := '$.x';
r := pck_api_xml.typeof(c, p);
print(p || ' => ' || r); -- undefined
print;
print('Elcount');
p := '$.a';
n := pck_api_xml.elcount(c, p);
print(p || ' element count => ' || n); -- 1
p := '$.f';
n := pck_api_xml.elcount(c, p);
print(p || ' element count in => ' || n); -- 3
p := '$';
n := pck_api_xml.elcount(c, p);
print(p || ' element count in => ' || n); -- 7
print;
print('Keys');
p := '$';
r := pck_api_xml.keys(c, p);
print(p || ' keys => ' || r); -- ["a","b","f","g","h","i","j"]
p := '$.b';
r := pck_api_xml.keys(c, p);
print(p || ' keys => ' || r); -- ["c"]
p := '$.f';
r := pck_api_xml.keys(c, p);
print(p || ' keys => ' || r); -- []
print;
print ('Write');
--
r:=c;
p := '$.a';
v:='42';
pck_api_xml.write(r, p, v);
print(v || ' at ' || p || ' => ' || r);
--
r:=c;
p := '$.a.b.c';
v:='<x><y>z</y></x>';
pck_api_xml.write(r, p, v);
print(v || ' at ' || p || ' => ' || r);
--
r:=c;
p := '$.b';
v:=null;
pck_api_xml.write(r, p, v);
print(COALESCE(v, 'null') || ' at ' || p || ' => ' || r);
r:=c;
p := '$';
v:=null;
pck_api_xml.write(r, p, v);
print(COALESCE(v, 'null') || ' at ' || p || ' => ' || r);
--
print;
print('Print');
r:='<root><a>1</a><b><c>2</c></b><d><item>1</item><item>2</item><item>3</item></d></root>';
v:=r;
pck_api_xml.print(v);
print(r || ' => ' || CHR(10) || v);
print;
print('To YAML');
r:=pck_api_xml.to_yaml(c);
print(r);
print;
print('To JSON');
r:=pck_api_xml.to_json(c);
print(r);
print;
end;
/Validate
Package provides methods for validating values against rules
example
sql
SET FEEDBACK OFF;
PROMPT ========== REQUIRED ==========
-- TEST 1.1: required - POSITIVE (non-empty value)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('John', '{"type":"required","message":"Name is required"}');
DBMS_OUTPUT.put_line('TEST 1.1 (required - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 1.2: required - NEGATIVE (empty value)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('', '{"type":"required","message":"Name is required"}');
DBMS_OUTPUT.put_line('TEST 1.2 (required - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== MIN-LENGTH ==========
-- TEST 2.1: min-length - POSITIVE (length meets minimum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hello', '{"type":"min-length","params":3,"message":"Min 3 chars"}');
DBMS_OUTPUT.put_line('TEST 2.1 (min-length - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 2.2: min-length - NEGATIVE (length below minimum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hi', '{"type":"min-length","params":3,"message":"Min 3 chars"}');
DBMS_OUTPUT.put_line('TEST 2.2 (min-length - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== MAX-LENGTH ==========
-- TEST 3.1: max-length - POSITIVE (length within maximum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hi', '{"type":"max-length","params":5,"message":"Max 5 chars"}');
DBMS_OUTPUT.put_line('TEST 3.1 (max-length - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 3.2: max-length - NEGATIVE (length exceeds maximum)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('HelloWorld', '{"type":"max-length","params":5,"message":"Max 5 chars"}');
DBMS_OUTPUT.put_line('TEST 3.2 (max-length - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== EQUALS ==========
-- TEST 4.1: equals - POSITIVE (values match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('admin', '{"type":"equals","params":"admin","message":"Must be admin"}');
DBMS_OUTPUT.put_line('TEST 4.1 (equals - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 4.2: equals - NEGATIVE (values do not match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user', '{"type":"equals","params":"admin","message":"Must be admin"}');
DBMS_OUTPUT.put_line('TEST 4.2 (equals - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== EQUALS-NOT ==========
-- TEST 5.1: equals-not - POSITIVE (values are different)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user', '{"type":"equals-not","params":"admin","message":"Cannot be admin"}');
DBMS_OUTPUT.put_line('TEST 5.1 (equals-not - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 5.2: equals-not - NEGATIVE (values are the same)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('admin', '{"type":"equals-not","params":"admin","message":"Cannot be admin"}');
DBMS_OUTPUT.put_line('TEST 5.2 (equals-not - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== SAME-AS ==========
-- TEST 6.1: same-as - POSITIVE (values match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('SecurePass123', '{"type":"same-as","params":"SecurePass123","message":"Passwords must match"}');
DBMS_OUTPUT.put_line('TEST 6.1 (same-as - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 6.2: same-as - NEGATIVE (values do not match)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('SecurePass123', '{"type":"same-as","params":"DifferentPass","message":"Passwords must match"}');
DBMS_OUTPUT.put_line('TEST 6.2 (same-as - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== STARTS-WITH ==========
-- TEST 7.1: starts-with - POSITIVE (starts with prefix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('https://example.com', '{"type":"starts-with","params":"https","message":"Must start with https"}');
DBMS_OUTPUT.put_line('TEST 7.1 (starts-with - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 7.2: starts-with - NEGATIVE (does not start with prefix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('http://example.com', '{"type":"starts-with","params":"https","message":"Must start with https"}');
DBMS_OUTPUT.put_line('TEST 7.2 (starts-with - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== ENDS-WITH ==========
-- TEST 8.1: ends-with - POSITIVE (ends with suffix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user@example.com', '{"type":"ends-with","params":".com","message":"Must end with .com"}');
DBMS_OUTPUT.put_line('TEST 8.1 (ends-with - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 8.2: ends-with - NEGATIVE (does not end with suffix)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user@example.org', '{"type":"ends-with","params":".com","message":"Must end with .com"}');
DBMS_OUTPUT.put_line('TEST 8.2 (ends-with - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== CONTAINS ==========
-- TEST 9.1: contains - POSITIVE (contains substring)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hello World', '{"type":"contains","params":"World","message":"Must contain World"}');
DBMS_OUTPUT.put_line('TEST 9.1 (contains - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 9.2: contains - NEGATIVE (does not contain substring)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Hello There', '{"type":"contains","params":"World","message":"Must contain World"}');
DBMS_OUTPUT.put_line('TEST 9.2 (contains - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== GREATER-THAN ==========
-- TEST 10.1: greater-than - POSITIVE (value greater than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('100', '{"type":"greater-than","params":50,"message":"Must be > 50"}');
DBMS_OUTPUT.put_line('TEST 10.1 (greater-than - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 10.2: greater-than - NEGATIVE (value not greater than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('25', '{"type":"greater-than","params":50,"message":"Must be > 50"}');
DBMS_OUTPUT.put_line('TEST 10.2 (greater-than - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== LESS-THAN ==========
-- TEST 11.1: less-than - POSITIVE (value less than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('30', '{"type":"less-than","params":50,"message":"Must be < 50"}');
DBMS_OUTPUT.put_line('TEST 11.1 (less-than - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 11.2: less-than - NEGATIVE (value not less than param)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('75', '{"type":"less-than","params":50,"message":"Must be < 50"}');
DBMS_OUTPUT.put_line('TEST 11.2 (less-than - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== IN-RANGE ==========
-- TEST 12.1: in-range - POSITIVE (value within range)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('15', '{"type":"in-range","params":[10,20],"message":"Must be between 10 and 20"}');
DBMS_OUTPUT.put_line('TEST 12.1 (in-range - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 12.2: in-range - NEGATIVE (value outside range)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('25', '{"type":"in-range","params":[10,20],"message":"Must be between 10 and 20"}');
DBMS_OUTPUT.put_line('TEST 12.2 (in-range - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== INCLUDES ==========
-- TEST 13.1: includes - POSITIVE (value in list)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('green', '{"type":"includes","params":["red","green","blue"],"message":"Invalid color"}');
DBMS_OUTPUT.put_line('TEST 13.1 (includes - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 13.2: includes - NEGATIVE (value not in list)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('yellow', '{"type":"includes","params":["red","green","blue"],"message":"Invalid color"}');
DBMS_OUTPUT.put_line('TEST 13.2 (includes - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== SET ==========
-- TEST 14.1: set - POSITIVE (value in set)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('active', '{"type":"set","params":["active","inactive","pending"],"message":"Invalid status"}');
DBMS_OUTPUT.put_line('TEST 14.1 (set - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 14.2: set - NEGATIVE (value not in set)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('deleted', '{"type":"set","params":["active","inactive","pending"],"message":"Invalid status"}');
DBMS_OUTPUT.put_line('TEST 14.2 (set - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== EMAIL ==========
-- TEST 15.1: email - POSITIVE (valid email)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('user@example.com', '{"type":"email","message":"Invalid email"}');
DBMS_OUTPUT.put_line('TEST 15.1 (email - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 15.2: email - NEGATIVE (invalid email)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('notanemail', '{"type":"email","message":"Invalid email"}');
DBMS_OUTPUT.put_line('TEST 15.2 (email - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== URL ==========
-- TEST 16.1: url - POSITIVE (valid URL)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('https://example.com/path', '{"type":"url","message":"Invalid URL"}');
DBMS_OUTPUT.put_line('TEST 16.1 (url - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 16.2: url - NEGATIVE (invalid URL)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('not a url', '{"type":"url","message":"Invalid URL"}');
DBMS_OUTPUT.put_line('TEST 16.2 (url - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== PASSWORD ==========
-- TEST 17.1: password - POSITIVE (valid password)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('SecurePass123', '{"type":"password","message":"Invalid password"}');
DBMS_OUTPUT.put_line('TEST 17.1 (password - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 17.2: password - NEGATIVE (password too short)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('Pass1', '{"type":"password","message":"Invalid password"}');
DBMS_OUTPUT.put_line('TEST 17.2 (password - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== IP ==========
-- TEST 18.1: ip - POSITIVE (valid IPv4)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('192.168.1.1', '{"type":"ip","message":"Invalid IP"}');
DBMS_OUTPUT.put_line('TEST 18.1 (ip - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 18.2: ip - NEGATIVE (invalid IPv4)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('999.999.999.999', '{"type":"ip","message":"Invalid IP"}');
DBMS_OUTPUT.put_line('TEST 18.2 (ip - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== REGEXP ==========
-- TEST 19.1: regexp - POSITIVE (matches pattern)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('ABC123', '{"type":"regexp","params":"^[A-Z]{3}[0-9]{3}$","message":"Invalid format"}');
DBMS_OUTPUT.put_line('TEST 19.1 (regexp - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 19.2: regexp - NEGATIVE (does not match pattern)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('XYZ', '{"type":"regexp","params":"^[A-Z]{3}[0-9]{3}$","message":"Invalid format"}');
DBMS_OUTPUT.put_line('TEST 19.2 (regexp - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== IS-JSON ==========
-- TEST 20.1: is-json - POSITIVE (valid JSON)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('{"key":"value"}', '{"type":"is-json","message":"Invalid JSON"}');
DBMS_OUTPUT.put_line('TEST 20.1 (is-json - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 20.2: is-json - NEGATIVE (invalid JSON)
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('not json', '{"type":"is-json","message":"Invalid JSON"}');
DBMS_OUTPUT.put_line('TEST 20.2 (is-json - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== MULTI-RULE CASE ==========
-- TEST 21: Multiple rules - all must pass
DECLARE
v_msg VARCHAR2(4000);
v_rules CLOB := '[
{"type":"required","message":"Email is required"},
{"type":"min-length","params":5,"message":"Email must be at least 5 chars"},
{"type":"email","message":"Must be a valid email"}
]';
BEGIN
v_msg := pck_api_validate.validate('user@example.com', v_rules);
DBMS_OUTPUT.put_line('TEST 21 (multi-rule - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 22: Multiple rules - fails on first violation
DECLARE
v_msg VARCHAR2(4000);
v_rules CLOB := '[
{"type":"required","message":"Email is required"},
{"type":"min-length","params":5,"message":"Email must be at least 5 chars"},
{"type":"email","message":"Must be a valid email"}
]';
BEGIN
v_msg := pck_api_validate.validate('a@b.c', v_rules);
DBMS_OUTPUT.put_line('TEST 22 (multi-rule - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/
PROMPT ========== NO MESSAGE CASE ==========
-- TEST 23: Rule without custom message - uses default
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('123', '{"type":"required"}');
DBMS_OUTPUT.put_line('TEST 23 (no message - POSITIVE): '||NVL(v_msg,'OK'));
END;
/
-- TEST 24: Rule without custom message - negative case shows default message
DECLARE
v_msg VARCHAR2(4000);
BEGIN
v_msg := pck_api_validate.validate('', '{"type":"required"}');
DBMS_OUTPUT.put_line('TEST 24 (no message - NEGATIVE): '||NVL(v_msg,'OK'));
END;
/YAML
Package for handling YAML
example
sql
DECLARE
c CLOB := 'a: 1
b:
c:
d: e
f:
- 1
- 2
- 3
g: hello world
h: true
i: null
j: 3.14';
r CLOB;
p VARCHAR2(2000 CHAR);
v CLOB;
b BOOLEAN;
n PLS_INTEGER;
PROCEDURE print(c clob DEFAULT NULL)
AS
BEGIN
dbms_output.put_line(dbms_lob.substr(c, 4000));
END;
FUNCTION tf(b IN boolean) RETURN VARCHAR2
AS
BEGIN
RETURN CASE WHEN b THEN 'true' ELSE 'false' END;
END;
BEGIN
print('YAML Input:');
print('-------------------');
print(c);
print('-------------------');
print;
print('Init');
r:= pck_api_yaml.init;
print(r || ' => ' || '(0 bytes)');
print;
print('Exists');
p := '$.a';
b := pck_api_yaml.exists(c, p);
print(p || ' => ' || tf(b)); -- true
p := '$.b.c';
b := pck_api_yaml.exists(c, p);
print(p || ' => ' || tf(b)); -- true
p := '$.c.d';
b := pck_api_yaml.exists(c, p);
print(p || ' => ' || tf(b)); -- false
p := '$.x';
b := pck_api_yaml.exists(c, p);
print(p || ' => ' || tf(b)); -- false
p := '$.f[0]';
b := pck_api_yaml.exists(c, p);
print(p || ' => ' || tf(b)); -- true
p := '$.f[10]';
b := pck_api_yaml.exists(c, p);
print(p || ' => ' || tf(b)); -- false
print;
print('Read');
p := '$.a';
r := pck_api_yaml.read(c, p);
print(p || ' => ' || r); -- 1
p := '$.b';
r := pck_api_yaml.read(c, p);
print(p || ' => ' || r); -- {c: {d: e}}
p := '$.f[2]';
r := pck_api_yaml.read(c, p);
print(p || ' => ' || r); -- {item: 3}
p := '$.f[10]';
r := pck_api_yaml.read(c, p);
print(p || ' => ' || r); -- NULL
print;
print('Typeof');
p := '$.a';
r := pck_api_yaml.typeof(c, p);
print(p || ' => ' || r); -- number
p := '$.b';
r := pck_api_yaml.typeof(c, p);
print(p || ' => ' || r); -- object
p := '$.f';
r := pck_api_yaml.typeof(c, p);
print(p || ' => ' || r); -- array
p := '$.x';
r := pck_api_yaml.typeof(c, p);
print(p || ' => ' || r); -- undefined
print;
print('Elcount');
p := '$.a';
n := pck_api_yaml.elcount(c, p);
print(p || ' element count => ' || n); -- 1
p := '$.f';
n := pck_api_yaml.elcount(c, p);
print(p || ' element count in => ' || n); -- 3
p := '$.root';
n := pck_api_yaml.elcount(c, p);
print(p || ' element count in => ' || n); -- 7
print;
print('Keys');
p := '$';
r := pck_api_yaml.keys(c, p);
print(p || ' keys => ' || r); -- ["a","b","f","g","h","i","j"]
p := '$.b';
r := pck_api_yaml.keys(c, p);
print(p || ' keys => ' || r); -- ["c"]
p := '$.f';
r := pck_api_yaml.keys(c, p);
print(p || ' keys => ' || r); -- []
print;
print ('Write');
--
r:=c;
p := '$.a';
v:='42';
pck_api_yaml.write(r, p, v);
print(v || ' at ' || p || ' => ' );
print('----');
print(r);
print('----');
--
r:=c;
p := '$.b.c';
v:='x:
y:
z: data';
pck_api_yaml.write(r, p, v);
print(v || ' at ' || p || ' => ');
print('----');
print(r);
print('----');
--
r:=c;
p := '$.b';
v:=null;
pck_api_yaml.write(r, p, v);
print(COALESCE(v, 'null') || ' at ' || p || ' => ');
print('----');
print(r);
print('----');
--
p := '$';
v:=null;
pck_api_yaml.write(r, p, v);
print(COALESCE(v, 'null') || ' at ' || p || ' => ');
--
print('----');
print(r);
print('----');
print('Print');
pck_api_yaml.print(c);
print(r || ' => ' || CHR(10) || c);
print;
print('To XML');
r:=pck_api_yaml.to_xml(c);
print(r);
print;
print('To JSON');
r:=pck_api_yaml.to_json(c);
print(r);
print;
END;
/ZIP
Package for handling zip files, Credit: https://github.com/antonscheffer/as_zip
example
sql
DECLARE
v_zip BLOB;
v_content BLOB;
v_files odbvue.pck_api_zip.t_file_list;
BEGIN
v_zip := NULL;
v_content := utl_raw.cast_to_raw('Hello from ZIP!');
odbvue.pck_api_zip.add(v_zip, 'test.txt', v_content);
v_files := odbvue.pck_api_zip.list(v_zip);
DBMS_OUTPUT.PUT_LINE('Files in ZIP: ' || v_files.COUNT);
DBMS_OUTPUT.PUT_LINE('File: ' || v_files(1));
odbvue.pck_api_zip.extract(v_zip, 'test.txt', v_content);
DBMS_OUTPUT.PUT_LINE('Content: ' || utl_raw.cast_to_varchar2(v_content));
END;
/