728 lines
26 KiB
Markdown
728 lines
26 KiB
Markdown
# PLJSON_PRINTER
|
|
|
|
## Package Specification
|
|
|
|
```sql
|
|
package pljson_printer as
|
|
/*
|
|
Copyright (c) 2010 Jonas Krogsboell
|
|
Permission is hereby granted, free of charge, to any person obtaining a copy
|
|
of this software and associated documentation files (the "Software"), to deal
|
|
in the Software without restriction, including without limitation the rights
|
|
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
|
|
copies of the Software, and to permit persons to whom the Software is
|
|
furnished to do so, subject to the following conditions:
|
|
The above copyright notice and this permission notice shall be included in
|
|
all copies or substantial portions of the Software.
|
|
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
|
|
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
|
|
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
|
|
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
|
|
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
|
|
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
|
|
THE SOFTWARE.
|
|
*/
|
|
indent_string varchar2(10 char) := ' '; --chr(9); for tab
|
|
newline_char varchar2(2 char) := chr(13)||chr(10); -- Windows style
|
|
--newline_char varchar2(2) := chr(10); -- Mac style
|
|
--newline_char varchar2(2) := chr(13); -- Linux style
|
|
ascii_output boolean not null := true;
|
|
empty_string_as_null boolean not null := false;
|
|
escape_solidus boolean not null := false;
|
|
|
|
function pretty_print(obj pljson, spaces boolean default true, line_length number default 0) return varchar2;
|
|
function pretty_print_list(obj pljson_list, spaces boolean default true, line_length number default 0) return varchar2;
|
|
function pretty_print_any(json_part pljson_element, spaces boolean default true, line_length number default 0) return varchar2;
|
|
procedure pretty_print(obj pljson, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true);
|
|
procedure pretty_print_list(obj pljson_list, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true);
|
|
procedure pretty_print_any(json_part pljson_element, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true);
|
|
|
|
procedure dbms_output_clob(my_clob clob, delim varchar2, jsonp varchar2 default null);
|
|
procedure htp_output_clob(my_clob clob, jsonp varchar2 default null);
|
|
-- made public just for testing/profiling...
|
|
function escapeString(str varchar2) return varchar2;
|
|
|
|
end pljson_printer;```
|
|
|
|
## Package Body
|
|
|
|
```sql
|
|
package body pljson_printer as
|
|
max_line_len number := 0;
|
|
cur_line_len number := 0;
|
|
|
|
-- associative array used inside escapeString to cache the escaped version of every character
|
|
-- escaped so far (example: char_map('"') contains the '\"' string)
|
|
-- (if the character does not need to be escaped, the character is stored unchanged in the array itself)
|
|
-- type Rmap_char is record(buf varchar2(40), len integer);
|
|
type Tmap_char_string is table of varchar2(40) index by varchar2(1 char); /* index by unicode char */
|
|
char_map Tmap_char_string;
|
|
-- since char_map the associative array is a global variable reused across multiple calls to escapeString,
|
|
-- i need to be able to detect that the escape_solidus or ascii_output global parameters have been changed,
|
|
-- in order to clear it and avoid using escape sequences that have been cached using the previous values
|
|
char_map_escape_solidus boolean := escape_solidus;
|
|
char_map_ascii_output boolean := ascii_output;
|
|
|
|
function llcheck(str in varchar2) return varchar2 as
|
|
begin
|
|
--dbms_output.put_line(cur_line_len || ' : ' || str);
|
|
if (max_line_len > 0 and length(str)+cur_line_len > max_line_len) then
|
|
cur_line_len := length(str);
|
|
return newline_char || str;
|
|
else
|
|
cur_line_len := cur_line_len + length(str);
|
|
return str;
|
|
end if;
|
|
end llcheck;
|
|
|
|
-- escapes a single character.
|
|
function escapeChar(ch char) return varchar2 deterministic is
|
|
result varchar2(20);
|
|
begin
|
|
--backspace b = U+0008
|
|
--formfeed f = U+000C
|
|
--newline n = U+000A
|
|
--carret r = U+000D
|
|
--tabulator t = U+0009
|
|
result := ch;
|
|
|
|
case ch
|
|
when chr( 8) then result := '\b';
|
|
when chr( 9) then result := '\t';
|
|
when chr(10) then result := '\n';
|
|
when chr(12) then result := '\f';
|
|
when chr(13) then result := '\r';
|
|
when chr(34) then result := '\"';
|
|
when chr(47) then if (escape_solidus) then result := '\/'; end if;
|
|
when chr(92) then result := '\\';
|
|
/* WARNING: ascii() returns PLS_INTEGER and large unicode code points can be negative */
|
|
else if (ascii(ch) >= 0 and ascii(ch) < 32) then
|
|
result := '\u' || replace(substr(to_char(ascii(ch), 'XXXX'), 2, 4), ' ', '0');
|
|
elsif (ascii_output) then
|
|
result := replace(asciistr(ch), '\', '\u');
|
|
end if;
|
|
end case;
|
|
return result;
|
|
end;
|
|
|
|
function escapeString(str varchar2) return varchar2 as
|
|
sb varchar2(32767 byte) := '';
|
|
buf varchar2(40);
|
|
ch varchar2(1 char); /* unicode char */
|
|
begin
|
|
if (str is null) then return ''; end if;
|
|
|
|
-- clear the cache if global parameters have been changed
|
|
if char_map_escape_solidus <> escape_solidus or
|
|
char_map_ascii_output <> ascii_output
|
|
then
|
|
char_map.delete;
|
|
char_map_escape_solidus := escape_solidus;
|
|
char_map_ascii_output := ascii_output;
|
|
end if;
|
|
|
|
for i in 1 .. length(str) loop
|
|
ch := substr(str, i, 1 ) ;
|
|
|
|
begin
|
|
-- it this char has already been processed, I have cached its escaped value
|
|
buf:=char_map(ch);
|
|
exception when no_Data_found then
|
|
-- otherwise, i convert the value and add it to the cache
|
|
buf := escapeChar(ch);
|
|
char_map(ch) := buf;
|
|
end;
|
|
|
|
sb := sb || buf;
|
|
end loop;
|
|
return sb;
|
|
end escapeString;
|
|
|
|
function newline(spaces boolean) return varchar2 as
|
|
begin
|
|
cur_line_len := 0;
|
|
if (spaces) then return newline_char; else return ''; end if;
|
|
end;
|
|
|
|
/* function get_schema return varchar2 as
|
|
begin
|
|
return sys_context('userenv', 'current_schema');
|
|
end;
|
|
*/
|
|
function tab(indent number, spaces boolean) return varchar2 as
|
|
i varchar(200) := '';
|
|
begin
|
|
if (not spaces) then return ''; end if;
|
|
for x in 1 .. indent loop i := i || indent_string; end loop;
|
|
return i;
|
|
end;
|
|
|
|
function getCommaSep(spaces boolean) return varchar2 as
|
|
begin
|
|
if (spaces) then return ', '; else return ','; end if;
|
|
end;
|
|
|
|
function getMemName(mem pljson_element, spaces boolean) return varchar2 as
|
|
begin
|
|
if (spaces) then
|
|
return llcheck('"'||escapeString(mem.mapname)||'"') || llcheck(' : ');
|
|
else
|
|
return llcheck('"'||escapeString(mem.mapname)||'"') || llcheck(':');
|
|
end if;
|
|
end;
|
|
|
|
/* Clob method start here */
|
|
procedure add_to_clob(buf_lob in out nocopy clob, buf_str in out nocopy varchar2, str varchar2) as
|
|
begin
|
|
-- if (length(str) > 5000 - length(buf_str)) then
|
|
if (lengthb(str) > 32767 - lengthb(buf_str)) then
|
|
-- dbms_lob.writeappend(buf_lob, length2(buf_str), buf_str);
|
|
dbms_lob.append(buf_lob, buf_str);
|
|
buf_str := str;
|
|
else
|
|
buf_str := buf_str || str;
|
|
end if;
|
|
end add_to_clob;
|
|
|
|
procedure flush_clob(buf_lob in out nocopy clob, buf_str in out nocopy varchar2) as
|
|
begin
|
|
-- dbms_lob.writeappend(buf_lob, length2(buf_str), buf_str);
|
|
dbms_lob.append(buf_lob, buf_str);
|
|
end flush_clob;
|
|
|
|
procedure ppObj(obj pljson, indent number, buf in out nocopy clob, spaces boolean, buf_str in out nocopy varchar2);
|
|
|
|
procedure ppString(elem pljson_string, buf in out nocopy clob, buf_str in out nocopy varchar2) is
|
|
offset number := 1;
|
|
/* E.I.Sarmas (github.com/dsnz) 2016-01-21 limit to 5000 chars */
|
|
v_str varchar(5000 char);
|
|
amount number := 5000; /* chunk size for use in escapeString, less than this number may be copied */
|
|
begin
|
|
if empty_string_as_null and elem.extended_str is null and elem.str is null then
|
|
add_to_clob(buf, buf_str, 'null');
|
|
else
|
|
add_to_clob(buf, buf_str, case when elem.num = 1 then '"' else '/**/' end);
|
|
if (elem.extended_str is not null) then --clob implementation
|
|
while (offset <= dbms_lob.getlength(elem.extended_str)) loop
|
|
dbms_lob.read(elem.extended_str, amount, offset, v_str);
|
|
if (elem.num = 1) then
|
|
add_to_clob(buf, buf_str, escapeString(v_str));
|
|
else
|
|
add_to_clob(buf, buf_str, v_str);
|
|
end if;
|
|
offset := offset + amount;
|
|
end loop;
|
|
else
|
|
if (elem.num = 1) then
|
|
while (offset <= length(elem.str)) loop
|
|
v_str:=substr(elem.str, offset, amount);
|
|
add_to_clob(buf, buf_str, escapeString(v_str));
|
|
offset := offset + amount;
|
|
end loop;
|
|
else
|
|
add_to_clob(buf, buf_str, elem.str);
|
|
end if;
|
|
end if;
|
|
add_to_clob(buf, buf_str, case when elem.num = 1 then '"' else '/**/' end);
|
|
end if;
|
|
end;
|
|
|
|
procedure ppEA(input pljson_list, indent number, buf in out nocopy clob, spaces boolean, buf_str in out nocopy varchar2) as
|
|
elem pljson_element;
|
|
arr pljson_element_array := input.list_data;
|
|
numbuf varchar2(4000);
|
|
begin
|
|
for y in 1 .. arr.count loop
|
|
elem := arr(y);
|
|
if (elem is not null) then
|
|
case elem.typeval
|
|
/* number */
|
|
when 4 then
|
|
numbuf := treat(elem as pljson_number).number_toString();
|
|
add_to_clob(buf, buf_str, llcheck(numbuf));
|
|
/* string */
|
|
when 3 then
|
|
ppString(treat(elem as pljson_string), buf, buf_str);
|
|
/* bool */
|
|
when 5 then
|
|
if (elem.get_bool()) then
|
|
add_to_clob(buf, buf_str, llcheck('true'));
|
|
else
|
|
add_to_clob(buf, buf_str, llcheck('false'));
|
|
end if;
|
|
/* null */
|
|
when 6 then
|
|
add_to_clob(buf, buf_str, llcheck('null'));
|
|
/* array */
|
|
when 2 then
|
|
add_to_clob(buf, buf_str, llcheck('['));
|
|
ppEA(treat(elem as pljson_list), indent, buf, spaces, buf_str);
|
|
add_to_clob(buf, buf_str, llcheck(']'));
|
|
/* object */
|
|
when 1 then
|
|
ppObj(treat(elem as pljson), indent, buf, spaces, buf_str);
|
|
else
|
|
add_to_clob(buf, buf_str, llcheck(elem.get_type));
|
|
end case;
|
|
end if;
|
|
if (y != arr.count) then add_to_clob(buf, buf_str, llcheck(getCommaSep(spaces))); end if;
|
|
end loop;
|
|
end ppEA;
|
|
|
|
procedure ppMem(mem pljson_element, indent number, buf in out nocopy clob, spaces boolean, buf_str in out nocopy varchar2) as
|
|
numbuf varchar2(4000);
|
|
begin
|
|
add_to_clob(buf, buf_str, llcheck(tab(indent, spaces)) || llcheck(getMemName(mem, spaces)));
|
|
case mem.typeval
|
|
/* number */
|
|
when 4 then
|
|
numbuf := treat(mem as pljson_number).number_toString();
|
|
add_to_clob(buf, buf_str, llcheck(numbuf));
|
|
/* string */
|
|
when 3 then
|
|
ppString(treat(mem as pljson_string), buf, buf_str);
|
|
/* bool */
|
|
when 5 then
|
|
if (mem.get_bool()) then
|
|
add_to_clob(buf, buf_str, llcheck('true'));
|
|
else
|
|
add_to_clob(buf, buf_str, llcheck('false'));
|
|
end if;
|
|
/* null */
|
|
when 6 then
|
|
add_to_clob(buf, buf_str, llcheck('null'));
|
|
/* array */
|
|
when 2 then
|
|
add_to_clob(buf, buf_str, llcheck('['));
|
|
ppEA(treat(mem as pljson_list), indent, buf, spaces, buf_str);
|
|
add_to_clob(buf, buf_str, llcheck(']'));
|
|
/* object */
|
|
when 1 then
|
|
ppObj(treat(mem as pljson), indent, buf, spaces, buf_str);
|
|
else
|
|
add_to_clob(buf, buf_str, llcheck(mem.get_type));
|
|
end case;
|
|
end ppMem;
|
|
|
|
procedure ppObj(obj pljson, indent number, buf in out nocopy clob, spaces boolean, buf_str in out nocopy varchar2) as
|
|
begin
|
|
add_to_clob(buf, buf_str, llcheck('{') || newline(spaces));
|
|
for m in 1 .. obj.json_data.count loop
|
|
ppMem(obj.json_data(m), indent+1, buf, spaces, buf_str);
|
|
if (m != obj.json_data.count) then
|
|
add_to_clob(buf, buf_str, llcheck(',') || newline(spaces));
|
|
else
|
|
add_to_clob(buf, buf_str, newline(spaces));
|
|
end if;
|
|
end loop;
|
|
add_to_clob(buf, buf_str, llcheck(tab(indent, spaces)) || llcheck('}')); -- || chr(13);
|
|
end ppObj;
|
|
|
|
procedure pretty_print(obj pljson, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true) as
|
|
buf_str varchar2(32767);
|
|
amount number := dbms_lob.getlength(buf);
|
|
begin
|
|
if (erase_clob and amount > 0) then
|
|
dbms_lob.trim(buf, 0);
|
|
-- dbms_lob.erase(buf, amount);
|
|
end if;
|
|
|
|
max_line_len := line_length;
|
|
cur_line_len := 0;
|
|
ppObj(obj, 0, buf, spaces, buf_str);
|
|
flush_clob(buf, buf_str);
|
|
end;
|
|
|
|
procedure pretty_print_list(obj pljson_list, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true) as
|
|
buf_str varchar2(32767);
|
|
amount number := dbms_lob.getlength(buf);
|
|
begin
|
|
if (erase_clob and amount > 0) then
|
|
dbms_lob.trim(buf, 0);
|
|
-- dbms_lob.erase(buf, amount);
|
|
end if;
|
|
|
|
max_line_len := line_length;
|
|
cur_line_len := 0;
|
|
add_to_clob(buf, buf_str, llcheck('['));
|
|
ppEA(obj, 0, buf, spaces, buf_str);
|
|
add_to_clob(buf, buf_str, llcheck(']'));
|
|
flush_clob(buf, buf_str);
|
|
end;
|
|
|
|
procedure pretty_print_any(json_part pljson_element, spaces boolean default true, buf in out nocopy clob, line_length number default 0, erase_clob boolean default true) as
|
|
buf_str varchar2(32767) := '';
|
|
numbuf varchar2(4000);
|
|
amount number := dbms_lob.getlength(buf);
|
|
begin
|
|
if (erase_clob and amount > 0) then
|
|
dbms_lob.trim(buf, 0);
|
|
-- dbms_lob.erase(buf, amount);
|
|
end if;
|
|
|
|
case json_part.typeval
|
|
/* number */
|
|
when 4 then
|
|
numbuf := treat(json_part as pljson_number).number_toString();
|
|
add_to_clob(buf, buf_str, numbuf);
|
|
/* string */
|
|
when 3 then
|
|
ppString(treat(json_part as pljson_string), buf, buf_str);
|
|
/* bool */
|
|
when 5 then
|
|
if (json_part.get_bool()) then
|
|
add_to_clob(buf, buf_str, 'true');
|
|
else
|
|
add_to_clob(buf, buf_str, 'false');
|
|
end if;
|
|
/* null */
|
|
when 6 then
|
|
add_to_clob(buf, buf_str, 'null');
|
|
/* array */
|
|
when 2 then
|
|
pretty_print_list(pljson_list(json_part), spaces, buf, line_length);
|
|
return;
|
|
/* object */
|
|
when 1 then
|
|
pretty_print(pljson(json_part), spaces, buf, line_length);
|
|
return;
|
|
else
|
|
add_to_clob(buf, buf_str, 'unknown type:' || json_part.get_type);
|
|
end case;
|
|
flush_clob(buf, buf_str);
|
|
end;
|
|
|
|
/* Clob method end here */
|
|
|
|
/* Varchar2 method start here */
|
|
procedure add_buf (buf in out nocopy varchar2, str in varchar2) as
|
|
begin
|
|
if (lengthb(str)>32767-lengthb(buf)) then
|
|
raise_application_error(-20001,'Length of result JSON more than 32767 bytes. Use to_clob() procedures');
|
|
end if;
|
|
buf := buf || str;
|
|
end;
|
|
|
|
procedure ppString(elem pljson_string, buf in out nocopy varchar2) is
|
|
offset number := 1;
|
|
/* E.I.Sarmas (github.com/dsnz) 2016-01-21 limit to 5000 chars */
|
|
v_str varchar(5000 char);
|
|
amount number := 5000; /* chunk size for use in escapeString, less than this number may be copied */
|
|
begin
|
|
if empty_string_as_null and elem.extended_str is null and elem.str is null then
|
|
add_buf(buf, 'null');
|
|
else
|
|
add_buf(buf, case when elem.num = 1 then '"' else '/**/' end);
|
|
if (elem.extended_str is not null) then --clob implementation
|
|
while (offset <= dbms_lob.getlength(elem.extended_str)) loop
|
|
dbms_lob.read(elem.extended_str, amount, offset, v_str);
|
|
if (elem.num = 1) then
|
|
add_buf(buf, escapeString(v_str));
|
|
else
|
|
add_buf(buf, v_str);
|
|
end if;
|
|
offset := offset + amount;
|
|
end loop;
|
|
else
|
|
if (elem.num = 1) then
|
|
while (offset <= length(elem.str)) loop
|
|
v_str:=substr(elem.str, offset, amount);
|
|
add_buf(buf, escapeString(v_str));
|
|
offset := offset + amount;
|
|
end loop;
|
|
else
|
|
add_buf(buf, elem.str);
|
|
end if;
|
|
end if;
|
|
add_buf(buf, case when elem.num = 1 then '"' else '/**/' end);
|
|
end if;
|
|
end;
|
|
|
|
procedure ppObj(obj pljson, indent number, buf in out nocopy varchar2, spaces boolean);
|
|
|
|
procedure ppEA(input pljson_list, indent number, buf in out varchar2, spaces boolean) as
|
|
elem pljson_element;
|
|
arr pljson_element_array := input.list_data;
|
|
str varchar2(400);
|
|
begin
|
|
for y in 1 .. arr.count loop
|
|
elem := arr(y);
|
|
if (elem is not null) then
|
|
case elem.typeval
|
|
/* number */
|
|
when 4 then
|
|
str := treat(elem as pljson_number).number_toString();
|
|
add_buf(buf, llcheck(str));
|
|
/* string */
|
|
when 3 then
|
|
ppString(treat(elem as pljson_string), buf);
|
|
/* bool */
|
|
when 5 then
|
|
if (elem.get_bool()) then
|
|
add_buf (buf, llcheck('true'));
|
|
else
|
|
add_buf (buf, llcheck('false'));
|
|
end if;
|
|
/* null */
|
|
when 6 then
|
|
add_buf (buf, llcheck('null'));
|
|
/* array */
|
|
when 2 then
|
|
add_buf( buf, llcheck('['));
|
|
ppEA(treat(elem as pljson_list), indent, buf, spaces);
|
|
add_buf( buf, llcheck(']'));
|
|
/* object */
|
|
when 1 then
|
|
ppObj(treat(elem as pljson), indent, buf, spaces);
|
|
else
|
|
add_buf (buf, llcheck(elem.get_type)); /* should never happen */
|
|
end case;
|
|
end if;
|
|
if (y != arr.count) then add_buf(buf, llcheck(getCommaSep(spaces))); end if;
|
|
end loop;
|
|
end ppEA;
|
|
|
|
procedure ppMem(mem pljson_element, indent number, buf in out nocopy varchar2, spaces boolean) as
|
|
str varchar2(400) := '';
|
|
begin
|
|
add_buf(buf, llcheck(tab(indent, spaces)) || getMemName(mem, spaces));
|
|
case mem.typeval
|
|
/* number */
|
|
when 4 then
|
|
str := treat(mem as pljson_number).number_toString();
|
|
add_buf(buf, llcheck(str));
|
|
/* string */
|
|
when 3 then
|
|
ppString(treat(mem as pljson_string), buf);
|
|
/* bool */
|
|
when 5 then
|
|
if (mem.get_bool()) then
|
|
add_buf(buf, llcheck('true'));
|
|
else
|
|
add_buf(buf, llcheck('false'));
|
|
end if;
|
|
/* null */
|
|
when 6 then
|
|
add_buf(buf, llcheck('null'));
|
|
/* array */
|
|
when 2 then
|
|
add_buf(buf, llcheck('['));
|
|
ppEA(treat(mem as pljson_list), indent, buf, spaces);
|
|
add_buf(buf, llcheck(']'));
|
|
/* object */
|
|
when 1 then
|
|
ppObj(treat(mem as pljson), indent, buf, spaces);
|
|
else
|
|
add_buf(buf, llcheck(mem.get_type)); /* should never happen */
|
|
end case;
|
|
end ppMem;
|
|
|
|
procedure ppObj(obj pljson, indent number, buf in out nocopy varchar2, spaces boolean) as
|
|
begin
|
|
add_buf (buf, llcheck('{') || newline(spaces));
|
|
for m in 1 .. obj.json_data.count loop
|
|
ppMem(obj.json_data(m), indent+1, buf, spaces);
|
|
if (m != obj.json_data.count) then
|
|
add_buf(buf, llcheck(',') || newline(spaces));
|
|
else
|
|
add_buf(buf, newline(spaces));
|
|
end if;
|
|
end loop;
|
|
add_buf(buf, llcheck(tab(indent, spaces)) || llcheck('}')); -- || chr(13);
|
|
end ppObj;
|
|
|
|
function pretty_print(obj pljson, spaces boolean default true, line_length number default 0) return varchar2 as
|
|
buf varchar2(32767 byte) := '';
|
|
begin
|
|
max_line_len := line_length;
|
|
cur_line_len := 0;
|
|
ppObj(obj, 0, buf, spaces);
|
|
return buf;
|
|
end pretty_print;
|
|
|
|
function pretty_print_list(obj pljson_list, spaces boolean default true, line_length number default 0) return varchar2 as
|
|
buf varchar2(32767 byte) :='';
|
|
begin
|
|
max_line_len := line_length;
|
|
cur_line_len := 0;
|
|
add_buf(buf, llcheck('['));
|
|
ppEA(obj, 0, buf, spaces);
|
|
add_buf(buf, llcheck(']'));
|
|
return buf;
|
|
end;
|
|
|
|
function pretty_print_any(json_part pljson_element, spaces boolean default true, line_length number default 0) return varchar2 as
|
|
buf varchar2(32767) := '';
|
|
begin
|
|
case json_part.typeval
|
|
/* number */
|
|
when 4 then
|
|
buf := treat(json_part as pljson_number).number_toString();
|
|
/* string */
|
|
when 3 then
|
|
ppString(treat(json_part as pljson_string), buf);
|
|
/* bool */
|
|
when 5 then
|
|
if (json_part.get_bool()) then buf := 'true'; else buf := 'false'; end if;
|
|
/* null */
|
|
when 6 then
|
|
buf := 'null';
|
|
/* array */
|
|
when 2 then
|
|
buf := pretty_print_list(pljson_list(json_part), spaces, line_length);
|
|
/* object */
|
|
when 1 then
|
|
buf := pretty_print(pljson(json_part), spaces, line_length);
|
|
else
|
|
buf := 'weird error: ' || json_part.get_type;
|
|
end case;
|
|
return buf;
|
|
end;
|
|
|
|
procedure dbms_output_clob(my_clob clob, delim varchar2, jsonp varchar2 default null) as
|
|
prev number := 1;
|
|
indx number := 1;
|
|
size_of_nl number := length2(delim);
|
|
v_str varchar2(32767);
|
|
amount number;
|
|
max_string_chars number := 5000; /* chunk size, less than this number may be copied */
|
|
begin
|
|
if (jsonp is not null) then dbms_output.put_line(jsonp||'('); end if;
|
|
while (indx != 0) loop
|
|
--read every line
|
|
indx := dbms_lob.instr(my_clob, delim, prev+1);
|
|
--dbms_output.put_line(prev || ' to ' || indx);
|
|
|
|
if (indx = 0) then
|
|
--emit from prev to end;
|
|
amount := max_string_chars;
|
|
--dbms_output.put_line(' mycloblen ' || dbms_lob.getlength(my_clob));
|
|
loop
|
|
dbms_lob.read(my_clob, amount, prev, v_str);
|
|
dbms_output.put_line(v_str);
|
|
prev := prev+amount;
|
|
exit when prev >= dbms_lob.getlength(my_clob);
|
|
end loop;
|
|
else
|
|
amount := indx - prev;
|
|
if (amount > max_string_chars) then
|
|
amount := max_string_chars;
|
|
--dbms_output.put_line(' mycloblen ' || dbms_lob.getlength(my_clob));
|
|
loop
|
|
dbms_lob.read(my_clob, amount, prev, v_str);
|
|
dbms_output.put_line(v_str);
|
|
prev := prev+amount;
|
|
amount := indx - prev;
|
|
exit when prev >= indx - 1;
|
|
if (amount > max_string_chars) then
|
|
amount := max_string_chars;
|
|
end if;
|
|
end loop;
|
|
prev := indx + size_of_nl;
|
|
else
|
|
dbms_lob.read(my_clob, amount, prev, v_str);
|
|
dbms_output.put_line(v_str);
|
|
prev := indx + size_of_nl;
|
|
end if;
|
|
end if;
|
|
|
|
end loop;
|
|
if (jsonp is not null) then dbms_output.put_line(')'); end if;
|
|
|
|
/* while (amount != 0) loop
|
|
indx := dbms_lob.instr(my_clob, delim, prev+1);
|
|
|
|
-- dbms_output.put_line(prev || ' to ' || indx);
|
|
if (indx = 0) then
|
|
indx := dbms_lob.getlength(my_clob)+1;
|
|
end if;
|
|
if (indx-prev > 32767) then
|
|
indx := prev+32767;
|
|
end if;
|
|
-- dbms_output.put_line(prev || ' to ' || indx);
|
|
--substr doesnt work properly on all platforms! (come on oracle - error on Oracle VM for virtualbox)
|
|
-- dbms_output.put_line(dbms_lob.substr(my_clob, indx-prev, prev));
|
|
amount := indx-prev;
|
|
-- dbms_output.put_line('amount'||amount);
|
|
dbms_lob.read(my_clob, amount, prev, v_str);
|
|
dbms_output.put_line(v_str);
|
|
prev := indx+size_of_nl;
|
|
if (amount = 32767) then prev := prev-size_of_nl-1; end if;
|
|
end loop;
|
|
if (jsonp is not null) then dbms_output.put_line(')'); end if;*/
|
|
end;
|
|
|
|
/* procedure dbms_output_clob(my_clob clob, delim varchar2, jsonp varchar2 default null) as
|
|
prev number := 1;
|
|
indx number := 1;
|
|
size_of_nl number := length2(delim);
|
|
v_str varchar2(32767);
|
|
amount number;
|
|
begin
|
|
if (jsonp is not null) then dbms_output.put_line(jsonp||'('); end if;
|
|
while (indx != 0) loop
|
|
indx := dbms_lob.instr(my_clob, delim, prev+1);
|
|
|
|
--dbms_output.put_line(prev || ' to ' || indx);
|
|
if (indx-prev > 32767) then
|
|
indx := prev+32767;
|
|
end if;
|
|
--dbms_output.put_line(prev || ' to ' || indx);
|
|
--substr doesnt work properly on all platforms! (come on oracle - error on Oracle VM for virtualbox)
|
|
if (indx = 0) then
|
|
--dbms_output.put_line(dbms_lob.substr(my_clob, dbms_lob.getlength(my_clob)-prev+size_of_nl, prev));
|
|
amount := dbms_lob.getlength(my_clob)-prev+size_of_nl;
|
|
dbms_lob.read(my_clob, amount, prev, v_str);
|
|
else
|
|
--dbms_output.put_line(dbms_lob.substr(my_clob, indx-prev, prev));
|
|
amount := indx-prev;
|
|
--dbms_output.put_line('amount'||amount);
|
|
dbms_lob.read(my_clob, amount, prev, v_str);
|
|
end if;
|
|
dbms_output.put_line(v_str);
|
|
prev := indx+size_of_nl;
|
|
if (amount = 32767) then prev := prev-size_of_nl-1; end if;
|
|
end loop;
|
|
if (jsonp is not null) then dbms_output.put_line(')'); end if;
|
|
end;
|
|
*/
|
|
|
|
procedure htp_output_clob(my_clob clob, jsonp varchar2 default null) as
|
|
/*amount number := 4096;
|
|
pos number := 1;
|
|
len number;
|
|
*/
|
|
l_amt number default 4096;
|
|
l_off number default 1;
|
|
l_str varchar2(32000);
|
|
begin
|
|
if (jsonp is not null) then htp.prn(jsonp||'('); end if;
|
|
|
|
begin
|
|
loop
|
|
dbms_lob.read( my_clob, l_amt, l_off, l_str );
|
|
|
|
-- it is vital to use htp.PRN to avoid
|
|
-- spurious line feeds getting added to your
|
|
-- document
|
|
htp.prn( l_str );
|
|
l_off := l_off+l_amt;
|
|
end loop;
|
|
exception
|
|
when no_data_found then NULL;
|
|
end;
|
|
|
|
/*
|
|
len := dbms_lob.getlength(my_clob);
|
|
|
|
while (pos < len) loop
|
|
htp.prn(dbms_lob.substr(my_clob, amount, pos)); -- should I replace substr with dbms_lob.read?
|
|
--dbms_output.put_line(dbms_lob.substr(my_clob, amount, pos));
|
|
pos := pos + amount;
|
|
end loop;
|
|
*/
|
|
if (jsonp is not null) then htp.prn(')'); end if;
|
|
end;
|
|
|
|
end pljson_printer;```
|