342 lines
13 KiB
Markdown
342 lines
13 KiB
Markdown
# PLJSON_DYN
|
|
|
|
## Package Specification
|
|
|
|
```sql
|
|
package pljson_dyn authid current_user 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.
|
|
*/
|
|
|
|
null_as_empty_string boolean not null := true; --varchar2
|
|
include_dates boolean not null := true; --date
|
|
include_clobs boolean not null := true;
|
|
include_blobs boolean not null := false;
|
|
include_arrays boolean not null := true; -- pljson_varray or pljson_narray
|
|
|
|
/* list with objects */
|
|
function executeList(stmt varchar2, bindvar pljson default null, cur_num number default null, bindvardateformats pljson default null) return pljson_list;
|
|
|
|
/* object with lists */
|
|
function executeObject(stmt varchar2, bindvar pljson default null, cur_num number default null) return pljson;
|
|
|
|
|
|
/* usage example:
|
|
* declare
|
|
* res json_list;
|
|
* begin
|
|
* res := json_dyn.executeList(
|
|
* 'select :bindme as one, :lala as two from dual where dummy in :arraybind',
|
|
* json('{bindme:"4", lala:123, arraybind:[1, 2, 3, "X"]}')
|
|
* );
|
|
* res.print;
|
|
* end;
|
|
*/
|
|
|
|
/* --11g functions
|
|
function executeList(stmt in out sys_refcursor) return json_list;
|
|
function executeObject(stmt in out sys_refcursor) return json;
|
|
*/
|
|
end pljson_dyn;```
|
|
|
|
## Package Body
|
|
|
|
```sql
|
|
package body pljson_dyn as
|
|
/*
|
|
-- 11gR2
|
|
function executeList(stmt in out sys_refcursor) return json_list as
|
|
l_cur number;
|
|
begin
|
|
l_cur := dbms_sql.to_cursor_number(stmt);
|
|
return json_dyn.executeList(null, null, l_cur);
|
|
end;
|
|
|
|
-- 11gR2
|
|
function executeObject(stmt in out sys_refcursor) return json as
|
|
l_cur number;
|
|
begin
|
|
l_cur := dbms_sql.to_cursor_number(stmt);
|
|
return json_dyn.executeObject(null, null, l_cur);
|
|
end;
|
|
*/
|
|
|
|
procedure bind_json(l_cur number, bindvar pljson, bindvardateformats pljson default null) as
|
|
keylist pljson_list := bindvar.get_keys();
|
|
begin
|
|
for i in 1 .. keylist.count loop
|
|
if (bindvar.get(i).is_number()) then
|
|
dbms_sql.bind_variable(l_cur, ':'||keylist.get(i).get_string(), bindvar.get(i).get_number());
|
|
elsif (bindvar.get(i).is_array()) then
|
|
declare
|
|
v_bind dbms_sql.varchar2_table;
|
|
v_arr pljson_list := pljson_list(bindvar.get(i));
|
|
begin
|
|
for j in 1 .. v_arr.count loop
|
|
v_bind(j) := v_arr.get(j).value_of();
|
|
end loop;
|
|
dbms_sql.bind_array(l_cur, ':'||keylist.get(i).get_string(), v_bind);
|
|
end;
|
|
else
|
|
if bindvardateformats is not null then
|
|
if bindvardateformats.exist(keylist.get(i).get_string()) then
|
|
dbms_sql.bind_variable(l_cur, ':'||keylist.get(i).get_string(), to_date(bindvar.get(i).value_of(), bindvardateformats.get(keylist.get(i).get_string()).get_string() ));
|
|
else
|
|
dbms_sql.bind_variable(l_cur, ':'||keylist.get(i).get_string(), bindvar.get(i).value_of());
|
|
end if;
|
|
else
|
|
dbms_sql.bind_variable(l_cur, ':'||keylist.get(i).get_string(), bindvar.get(i).value_of());
|
|
end if;
|
|
end if;
|
|
end loop;
|
|
end bind_json;
|
|
|
|
/* list with objects */
|
|
function executeList(stmt varchar2, bindvar pljson, cur_num number, bindvardateformats pljson default null) return pljson_list as
|
|
l_cur number;
|
|
l_dtbl dbms_sql.desc_tab3;
|
|
l_cnt number;
|
|
l_status number;
|
|
l_val varchar2(4000);
|
|
outer_list pljson_list := pljson_list();
|
|
inner_obj pljson;
|
|
conv number;
|
|
read_date date;
|
|
read_clob clob;
|
|
read_blob blob;
|
|
col_type number;
|
|
read_varray pljson_varray;
|
|
read_narray pljson_narray;
|
|
begin
|
|
if (cur_num is not null) then
|
|
l_cur := cur_num;
|
|
else
|
|
l_cur := dbms_sql.open_cursor;
|
|
dbms_sql.parse(l_cur, stmt, dbms_sql.native);
|
|
if (bindvar is not null) then bind_json(l_cur, bindvar, bindvardateformats); end if;
|
|
end if;
|
|
/* E.I.Sarmas (github.com/dsnz) 2018-05-01 handling of varray, narray in select */
|
|
dbms_sql.describe_columns3(l_cur, l_cnt, l_dtbl);
|
|
for i in 1..l_cnt loop
|
|
col_type := l_dtbl(i).col_type;
|
|
--dbms_output.put_line(col_type);
|
|
if (col_type = 12) then
|
|
dbms_sql.define_column(l_cur, i, read_date);
|
|
elsif (col_type = 112) then
|
|
dbms_sql.define_column(l_cur, i, read_clob);
|
|
elsif (col_type = 113) then
|
|
dbms_sql.define_column(l_cur, i, read_blob);
|
|
elsif (col_type in (1, 2, 96)) then
|
|
dbms_sql.define_column(l_cur, i, l_val, 4000);
|
|
/* E.I.Sarmas (github.com/dsnz) 2018-05-01 handling of pljson_varray in select */
|
|
elsif (col_type = 109 and l_dtbl(i).col_type_name = 'PLJSON_VARRAY') then
|
|
dbms_sql.define_column(l_cur, i, read_varray);
|
|
/* E.I.Sarmas (github.com/dsnz) 2018-05-01 handling of pljson_narray in select */
|
|
elsif (col_type = 109 and l_dtbl(i).col_type_name = 'PLJSON_NARRAY') then
|
|
dbms_sql.define_column(l_cur, i, read_narray);
|
|
/* E.I.Sarmas (github.com/dsnz) 2018-05-01 record unhandled col_type */
|
|
else
|
|
dbms_output.put_line('unhandled col_type =' || col_type);
|
|
end if;
|
|
end loop;
|
|
|
|
if (cur_num is null) then l_status := dbms_sql.execute(l_cur); end if;
|
|
|
|
--loop through rows
|
|
while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
|
|
inner_obj := pljson(); --init for each row
|
|
inner_obj.check_for_duplicate := 0;
|
|
--loop through columns
|
|
for i in 1..l_cnt loop
|
|
case true
|
|
--handling string types
|
|
when l_dtbl(i).col_type in (1, 96) then -- varchar2
|
|
dbms_sql.column_value(l_cur, i, l_val);
|
|
if (l_val is null) then
|
|
if (null_as_empty_string) then
|
|
inner_obj.put(l_dtbl(i).col_name, ''); --treat as emptystring?
|
|
else
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_null()); --null
|
|
end if;
|
|
else
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_string(l_val)); --null
|
|
end if;
|
|
--dbms_output.put_line(l_dtbl(i).col_name||' --> '||l_val||'varchar2' ||l_dtbl(i).col_type);
|
|
--handling number types
|
|
when l_dtbl(i).col_type = 2 then -- number
|
|
dbms_sql.column_value(l_cur, i, l_val);
|
|
conv := l_val;
|
|
inner_obj.put(l_dtbl(i).col_name, conv);
|
|
-- dbms_output.put_line(l_dtbl(i).col_name||' --> '||l_val||'number ' ||l_dtbl(i).col_type);
|
|
when l_dtbl(i).col_type = 12 then -- date
|
|
if (include_dates) then
|
|
dbms_sql.column_value(l_cur, i, read_date);
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_ext.to_json_string(read_date));
|
|
end if;
|
|
--dbms_output.put_line(l_dtbl(i).col_name||' --> '||l_val||'date ' ||l_dtbl(i).col_type);
|
|
when l_dtbl(i).col_type = 112 then --clob
|
|
if (include_clobs) then
|
|
dbms_sql.column_value(l_cur, i, read_clob);
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_string(read_clob));
|
|
end if;
|
|
when l_dtbl(i).col_type = 113 then --blob
|
|
if (include_blobs) then
|
|
dbms_sql.column_value(l_cur, i, read_blob);
|
|
if (dbms_lob.getlength(read_blob) > 0) then
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_ext.encode(read_blob));
|
|
else
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_null());
|
|
end if;
|
|
end if;
|
|
/* E.I.Sarmas (github.com/dsnz) 2018-05-01 handling of pljson_varray in select */
|
|
when l_dtbl(i).col_type = 109 and l_dtbl(i).col_type_name = 'PLJSON_VARRAY' then
|
|
if (include_arrays) then
|
|
dbms_sql.column_value(l_cur, i, read_varray);
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_list(read_varray));
|
|
end if;
|
|
/* E.I.Sarmas (github.com/dsnz) 2018-05-01 handling of pljson_narray in select */
|
|
when l_dtbl(i).col_type = 109 and l_dtbl(i).col_type_name = 'PLJSON_NARRAY' then
|
|
if (include_arrays) then
|
|
dbms_sql.column_value(l_cur, i, read_narray);
|
|
inner_obj.put(l_dtbl(i).col_name, pljson_list(read_narray));
|
|
end if;
|
|
|
|
else null; --discard other types
|
|
end case;
|
|
end loop;
|
|
inner_obj.check_for_duplicate := 1;
|
|
outer_list.append(inner_obj);
|
|
end loop;
|
|
dbms_sql.close_cursor(l_cur);
|
|
return outer_list;
|
|
end executeList;
|
|
|
|
/* object with lists */
|
|
function executeObject(stmt varchar2, bindvar pljson, cur_num number) return pljson as
|
|
l_cur number;
|
|
l_dtbl dbms_sql.desc_tab;
|
|
l_cnt number;
|
|
l_status number;
|
|
l_val varchar2(4000);
|
|
inner_list_names pljson_list := pljson_list();
|
|
inner_list_data pljson_list := pljson_list();
|
|
data_list pljson_list;
|
|
outer_obj pljson := pljson();
|
|
conv number;
|
|
read_date date;
|
|
read_clob clob;
|
|
read_blob blob;
|
|
col_type number;
|
|
begin
|
|
if (cur_num is not null) then
|
|
l_cur := cur_num;
|
|
else
|
|
l_cur := dbms_sql.open_cursor;
|
|
dbms_sql.parse(l_cur, stmt, dbms_sql.native);
|
|
if (bindvar is not null) then bind_json(l_cur, bindvar); end if;
|
|
end if;
|
|
dbms_sql.describe_columns(l_cur, l_cnt, l_dtbl);
|
|
for i in 1..l_cnt loop
|
|
col_type := l_dtbl(i).col_type;
|
|
if (col_type = 12) then
|
|
dbms_sql.define_column(l_cur, i, read_date);
|
|
elsif (col_type = 112) then
|
|
dbms_sql.define_column(l_cur, i, read_clob);
|
|
elsif (col_type = 113) then
|
|
dbms_sql.define_column(l_cur, i, read_blob);
|
|
elsif (col_type in (1, 2, 96)) then
|
|
dbms_sql.define_column(l_cur, i, l_val, 4000);
|
|
end if;
|
|
end loop;
|
|
if (cur_num is null) then l_status := dbms_sql.execute(l_cur); end if;
|
|
|
|
--build up name_list
|
|
for i in 1..l_cnt loop
|
|
case l_dtbl(i).col_type
|
|
when 1 then inner_list_names.append(l_dtbl(i).col_name);
|
|
when 96 then inner_list_names.append(l_dtbl(i).col_name);
|
|
when 2 then inner_list_names.append(l_dtbl(i).col_name);
|
|
when 12 then if (include_dates) then inner_list_names.append(l_dtbl(i).col_name); end if;
|
|
when 112 then if (include_clobs) then inner_list_names.append(l_dtbl(i).col_name); end if;
|
|
when 113 then if (include_blobs) then inner_list_names.append(l_dtbl(i).col_name); end if;
|
|
else null;
|
|
end case;
|
|
end loop;
|
|
|
|
--loop through rows
|
|
while ( dbms_sql.fetch_rows(l_cur) > 0 ) loop
|
|
data_list := pljson_list();
|
|
--loop through columns
|
|
for i in 1..l_cnt loop
|
|
case true
|
|
--handling string types
|
|
when l_dtbl(i).col_type in (1, 96) then -- varchar2
|
|
dbms_sql.column_value(l_cur, i, l_val);
|
|
if (l_val is null) then
|
|
if (null_as_empty_string) then
|
|
data_list.append(''); --treat as emptystring?
|
|
else
|
|
data_list.append(pljson_null()); --null
|
|
end if;
|
|
else
|
|
data_list.append(pljson_string(l_val)); --null
|
|
end if;
|
|
--dbms_output.put_line(l_dtbl(i).col_name||' --> '||l_val||'varchar2' ||l_dtbl(i).col_type);
|
|
--handling number types
|
|
when l_dtbl(i).col_type = 2 then -- number
|
|
dbms_sql.column_value(l_cur, i, l_val);
|
|
conv := l_val;
|
|
data_list.append(conv);
|
|
-- dbms_output.put_line(l_dtbl(i).col_name||' --> '||l_val||'number ' ||l_dtbl(i).col_type);
|
|
when l_dtbl(i).col_type = 12 then -- date
|
|
if (include_dates) then
|
|
dbms_sql.column_value(l_cur, i, read_date);
|
|
data_list.append(pljson_ext.to_json_string(read_date));
|
|
end if;
|
|
--dbms_output.put_line(l_dtbl(i).col_name||' --> '||l_val||'date ' ||l_dtbl(i).col_type);
|
|
when l_dtbl(i).col_type = 112 then --clob
|
|
if (include_clobs) then
|
|
dbms_sql.column_value(l_cur, i, read_clob);
|
|
data_list.append(pljson_string(read_clob));
|
|
end if;
|
|
when l_dtbl(i).col_type = 113 then --blob
|
|
if (include_blobs) then
|
|
dbms_sql.column_value(l_cur, i, read_blob);
|
|
if (dbms_lob.getlength(read_blob) > 0) then
|
|
data_list.append(pljson_ext.encode(read_blob));
|
|
else
|
|
data_list.append(pljson_null());
|
|
end if;
|
|
end if;
|
|
else null; --discard other types
|
|
end case;
|
|
end loop;
|
|
inner_list_data.append(data_list);
|
|
end loop;
|
|
|
|
outer_obj.put('names', inner_list_names);
|
|
outer_obj.put('data', inner_list_data);
|
|
dbms_sql.close_cursor(l_cur);
|
|
return outer_obj;
|
|
end executeObject;
|
|
|
|
end pljson_dyn;```
|