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;
/specification
sql
CREATE OR REPLACE PACKAGE odbvue.pck_api_yaml IS -- Package for handling YAML
FUNCTION init -- Function to initialize empty yaml object
RETURN CLOB; -- Returns a clob representing an empty yaml object
FUNCTION EXISTS ( -- Function to check if a path exists in a yaml object
c IN CLOB, -- The yaml object as clob
p IN VARCHAR2 -- The path to check for existence
) RETURN BOOLEAN; -- Returns true if the key exists, false otherwise
FUNCTION read ( -- Function reads by path form a yaml object
c IN CLOB, -- The yaml object as clob
p IN VARCHAR2 -- The path to read
) RETURN CLOB; -- Returns the value at the specified path as clob
FUNCTION typeof ( -- Function to get the type of the value at a specified path
c IN CLOB, -- The yaml object as clob
p IN VARCHAR2 -- The path to check the type of
) RETURN VARCHAR2; -- Returns the type as a string
FUNCTION elcount ( -- Function to count elements in an array at a specified path
c IN CLOB, -- The yaml object as clob
p IN VARCHAR2 -- The path to the array
) RETURN PLS_INTEGER; -- Returns the count of elements
FUNCTION keys ( -- Function to get the keys of an object at a specified path
c IN CLOB, -- The yaml object as clob
p IN VARCHAR2 -- The path to the object
) RETURN VARCHAR2; -- Returns a list of keys
PROCEDURE write ( -- Procedure to replace a value at a specified path
c IN OUT NOCOPY CLOB, -- The yaml object as clob
p IN VARCHAR2, -- The path to insert the value at (object path only, arrays not supported yet)
v IN CLOB -- The value to insert
);
PROCEDURE print ( -- Procedure to pretty-print a yaml object
c IN OUT NOCOPY CLOB -- The yaml object as clob
);
FUNCTION to_xml ( -- Function to convert yaml object to xml representation
c CLOB -- The yaml object as clob
) RETURN CLOB; -- Returns the xml representation as clob
FUNCTION to_json ( -- Function to convert yaml object to JSON representation
c IN CLOB --- The yaml object as clob
) RETURN CLOB; -- Returns the JSON representation as clob
END pck_api_yaml;
/
-- sqlcl_snapshot {"hash":"981a0251d087b9a1374cd606074ac6e5df561eac","type":"PACKAGE_SPEC","name":"PCK_API_YAML","schemaName":"ODBVUE","sxml":""}implementation
sql
CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_yaml IS
FUNCTION init RETURN CLOB IS
BEGIN
RETURN '';
END init;
FUNCTION EXISTS (
c CLOB,
p VARCHAR2
) RETURN BOOLEAN IS
r CLOB := to_json(c);
BEGIN
RETURN pck_api_json.EXISTS(r, p);
END EXISTS;
FUNCTION read (
c CLOB,
p VARCHAR2
) RETURN CLOB IS
r CLOB := to_json(c);
t VARCHAR2(30 CHAR);
BEGIN
t := pck_api_json.typeof(r, p);
r := pck_api_json.read(r, p);
IF ( t = 'object' )
OR ( t = 'array' ) THEN
RETURN pck_api_json.to_yaml(r);
END IF;
RETURN r;
END read;
FUNCTION typeof (
c CLOB,
p VARCHAR2
) RETURN VARCHAR2 IS
r CLOB := to_json(c);
BEGIN
RETURN pck_api_json.typeof(r, p);
END typeof;
FUNCTION elcount (
c CLOB,
p VARCHAR2
) RETURN PLS_INTEGER IS
r CLOB := to_json(c);
BEGIN
RETURN pck_api_json.elcount(r, p);
END elcount;
FUNCTION keys (
c CLOB,
p VARCHAR2
) RETURN VARCHAR2 IS
r CLOB := to_json(c);
BEGIN
RETURN pck_api_json.keys(r, p);
END keys;
PROCEDURE write (
c IN OUT NOCOPY CLOB,
p VARCHAR2,
v CLOB
) IS
r CLOB := to_json(c);
t VARCHAR2(30 CHAR);
c2 CLOB;
r2 CLOB;
v2 CLOB;
BEGIN
c2 := to_json(c);
BEGIN
v2 := to_json(coalesce(v, 'null'));
EXCEPTION
WHEN OTHERS THEN
v2 := v;
END;
pck_api_json.write(c2, p, v2);
c := pck_api_json.to_yaml(c2);
END write;
PROCEDURE print (
c IN OUT NOCOPY CLOB
) IS
BEGIN
NULL;
END print;
FUNCTION to_xml (
c IN CLOB
) RETURN CLOB IS
BEGIN
RETURN pck_api_json.to_xml(to_json(c));
END;
FUNCTION to_json (
c IN CLOB
) RETURN CLOB IS
out CLOB;
pos PLS_INTEGER := 1;
len PLS_INTEGER := dbms_lob.getlength(c);
line VARCHAR2(32767);
SUBTYPE t_kind IS VARCHAR2(3); -- 'obj' or 'arr'
TYPE t_frame IS RECORD (
kind t_kind,
indent PLS_INTEGER,
first BOOLEAN
);
TYPE t_stack IS
TABLE OF t_frame INDEX BY PLS_INTEGER;
st t_stack;
sp PLS_INTEGER := 0;
pending_key VARCHAR2(32767);
pending_indent PLS_INTEGER := -1;
FUNCTION json_escape (
p IN VARCHAR2
) RETURN VARCHAR2 IS
v VARCHAR2(32767) := nvl(p, '');
BEGIN
v := replace(v, '\', '\\');
v := replace(v, '"', '\"');
v := replace(v,
chr(8),
'\b');
v := replace(v,
chr(9),
'\t');
v := replace(v,
chr(10),
'\n');
v := replace(v,
chr(12),
'\f');
v := replace(v,
chr(13),
'\r');
RETURN v;
END;
FUNCTION to_json_scalar (
p_raw IN VARCHAR2
) RETURN VARCHAR2 IS
v VARCHAR2(32767) := trim(p_raw);
BEGIN
IF v IS NULL THEN
RETURN 'null';
END IF;
IF (
substr(v, 1, 1) IN ( '"', '''' )
AND substr(v, -1, 1) = substr(v, 1, 1)
) THEN
v := substr(v,
2,
length(v) - 2);
RETURN '"'
|| json_escape(v)
|| '"';
END IF;
IF lower(v) IN ( 'true', 'false', 'null' ) THEN
RETURN lower(v);
END IF;
IF regexp_like(v, '^[+-]?(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?$') THEN
RETURN v;
END IF;
RETURN '"'
|| json_escape(v)
|| '"';
END;
PROCEDURE append (
io IN OUT CLOB,
s IN VARCHAR2
) IS
BEGIN
dbms_lob.writeappend(io,
length(s),
s);
END;
FUNCTION leading_spaces (
p IN VARCHAR2
) RETURN PLS_INTEGER IS
BEGIN
RETURN nvl(
length(regexp_substr(p, '^\s*')),
0
);
END;
FUNCTION strip_line (
p IN VARCHAR2
) RETURN VARCHAR2 IS
l VARCHAR2(32767) := rtrim(p);
BEGIN
IF l IS NULL
OR regexp_like(l, '^\s*(#|$)') THEN
RETURN NULL;
END IF;
RETURN l;
END;
FUNCTION next_line (
cl IN CLOB,
pos IN OUT PLS_INTEGER,
len IN PLS_INTEGER
) RETURN VARCHAR2 IS
ch VARCHAR2(1);
buf VARCHAR2(32767);
BEGIN
IF pos > len THEN
RETURN NULL;
END IF;
LOOP
ch := dbms_lob.substr(cl, 1, pos);
pos := pos + 1;
IF ch = chr(10) THEN
EXIT;
END IF;
IF ch <> chr(13) THEN
buf := buf || ch;
END IF;
EXIT WHEN pos > len;
END LOOP;
RETURN buf;
END;
PROCEDURE push (
kind t_kind,
indent PLS_INTEGER
) IS
BEGIN
sp := sp + 1;
st(sp).kind := kind;
st(sp).indent := indent;
st(sp).first := TRUE;
IF out IS NULL THEN
dbms_lob.createtemporary(out, TRUE);
END IF;
append(out,
CASE kind
WHEN 'obj' THEN
'{'
ELSE
'['
END
);
END;
PROCEDURE add_comma IS
BEGIN
IF sp > 0 THEN
IF st(sp).first THEN
st(sp).first := FALSE;
ELSE
append(out, ',');
END IF;
END IF;
END;
PROCEDURE close_one IS
BEGIN
IF sp = 0 THEN
RETURN;
END IF;
append(out,
CASE st(sp).kind
WHEN 'obj' THEN
'}'
ELSE
']'
END
);
sp := sp - 1;
END;
PROCEDURE close_to (
indent PLS_INTEGER
) IS
BEGIN
WHILE
sp > 0
AND indent <= st(sp).indent
LOOP
close_one;
END LOOP;
END;
PROCEDURE materialize_pending_as (
kind t_kind
) IS
BEGIN
IF sp = 0
OR st(sp).kind <> 'obj' THEN
push('obj', pending_indent - 2);
END IF;
add_comma;
append(out,
'"'
|| json_escape(pending_key)
|| '":');
push(kind, pending_indent);
pending_key := NULL;
pending_indent := -1;
END;
BEGIN
IF out IS NULL THEN
dbms_lob.createtemporary(out, TRUE);
END IF;
LOOP
line := next_line(c, pos, len);
EXIT WHEN line IS NULL;
line := strip_line(line);
IF line IS NULL THEN
CONTINUE;
END IF;
DECLARE
ind PLS_INTEGER := leading_spaces(line);
raw VARCHAR2(32767) := ltrim(line);
BEGIN
close_to(ind);
IF
pending_key IS NOT NULL
AND ind > pending_indent
THEN
IF regexp_like(raw, '^\-\s') THEN
materialize_pending_as('arr');
ELSE
materialize_pending_as('obj');
END IF;
END IF;
IF regexp_like(raw, '^\-\s') THEN
raw := regexp_replace(raw, '^\-\s*', '');
IF sp = 0
OR st(sp).kind <> 'arr' THEN
IF pending_key IS NOT NULL THEN
materialize_pending_as('arr');
ELSE
push('arr', ind - 2);
END IF;
END IF;
IF regexp_like(raw, '^\w[^:]*:\s*') THEN
add_comma;
append(out, '{');
sp := sp + 1;
st(sp).kind := 'obj';
st(sp).indent := ind;
st(sp).first := TRUE;
DECLARE
k VARCHAR2(32767) := regexp_substr(raw, '^([^:]+):', 1, 1, NULL,
1);
v VARCHAR2(32767) := trim(regexp_substr(raw, '^[^:]+:\s*(.*)$', 1, 1, NULL,
1));
BEGIN
add_comma;
append(out,
'"'
|| json_escape(trim(k))
|| '":'
||
CASE
WHEN v IS NULL THEN
'null'
ELSE
to_json_scalar(v)
END
);
END;
ELSE
add_comma;
append(out,
to_json_scalar(raw));
END IF;
ELSIF regexp_like(raw, '^[^:]+:\s*(.*)$') THEN
DECLARE
k VARCHAR2(32767) := regexp_substr(raw, '^([^:]+):', 1, 1, NULL,
1);
v VARCHAR2(32767) := trim(regexp_substr(raw, '^[^:]+:\s*(.*)$', 1, 1, NULL,
1));
BEGIN
IF sp = 0
OR st(sp).kind <> 'obj' THEN
push('obj', ind - 2);
END IF;
IF v IS NULL
OR v = '' THEN
pending_key := trim(k);
pending_indent := ind;
ELSE
add_comma;
append(out,
'"'
|| json_escape(trim(k))
|| '":'
|| to_json_scalar(v));
END IF;
END;
ELSE
IF sp = 0
OR st(sp).kind <> 'arr' THEN
push('arr', ind - 2);
END IF;
add_comma;
append(out,
to_json_scalar(raw));
END IF;
END;
END LOOP;
IF pending_key IS NOT NULL THEN
IF sp = 0
OR st(sp).kind <> 'obj' THEN
push('obj', pending_indent - 2);
END IF;
add_comma;
append(out,
'"'
|| json_escape(pending_key)
|| '":{}');
pending_key := NULL;
END IF;
WHILE sp > 0 LOOP
close_one;
END LOOP;
RETURN out;
END to_json;
END pck_api_yaml;
/
-- sqlcl_snapshot {"hash":"23943b99a2d7d1a581e5711838ff481a569dc25c","type":"PACKAGE_BODY","name":"PCK_API_YAML","schemaName":"ODBVUE","sxml":""}ELCOUNT
Function to count elements in an array at a specified path
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | BINARY_INTEGER | Returns the count of elements | ||
| C | IN | CLOB | Function to count elements in an array at a specified path | |
| P | IN | VARCHAR2 | Function to count elements in an array at a specified path |
EXISTS
Function to check if a path exists in a yaml object
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | PL/SQL BOOLEAN | Returns true if the key exists, false otherwise | ||
| C | IN | CLOB | Function to check if a path exists in a yaml object | |
| P | IN | VARCHAR2 | Function to check if a path exists in a yaml object |
INIT
Function to initialize empty yaml object
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | CLOB | Returns true if the key exists, false otherwise |
KEYS
Function to get the keys of an object at a specified path
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | VARCHAR2 | Returns a list of keys | ||
| C | IN | CLOB | Function to get the keys of an object at a specified path | |
| P | IN | VARCHAR2 | Function to get the keys of an object at a specified path |
PRINT
Procedure to pretty-print a yaml object
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| C | IN/OUT | CLOB | Procedure to pretty-print a yaml object |
READ
Function reads by path form a yaml object
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | CLOB | Returns the value at the specified path as clob | ||
| C | IN | CLOB | Function reads by path form a yaml object | |
| P | IN | VARCHAR2 | Function reads by path form a yaml object |
TO_JSON
Function to convert yaml object to JSON representation
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | CLOB | Returns the JSON representation as clob | ||
| C | IN | CLOB | Function to convert yaml object to JSON representation |
TO_XML
Function to convert yaml object to xml representation
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | CLOB | Returns the xml representation as clob | ||
| C | IN | CLOB | Function to convert yaml object to xml representation |
TYPEOF
Function to get the type of the value at a specified path
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| OUT | VARCHAR2 | Returns the type as a string | ||
| C | IN | CLOB | Function to get the type of the value at a specified path | |
| P | IN | VARCHAR2 | Function to get the type of the value at a specified path |
WRITE
Procedure to replace a value at a specified path
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| C | IN/OUT | CLOB | Procedure to replace a value at a specified path | |
| P | IN | VARCHAR2 | Procedure to replace a value at a specified path | |
| V | IN | CLOB | Procedure to replace a value at a specified path |