How would I define a function that takes a multi-dimensional array as an input parameter and returns json_tbl PIPELINED that has been defined as
CREATE OR REPLACE TYPE CIC3.json_t as OBJECT (JSON_TEXT varchar2(30000));
CREATE OR REPLACE TYPE CIC3.json_tbl as TABLE OF json_t;
I need to pass in some key/value pairs to this function and build my sql dynamically based on that input.
I've tried dozens of things and can't get them to work. We're using
10g Rel:10.2.0.3.0 I would prefer, sending the input as JSON but if I can send an array from .Net, that would work, too.
This would be a sample of input
Array:
[
[KEYWORD = 'KW_1', VALUE = 'VAL_1'],
[KEYWORD = 'KW_2', VALUE = 'VAL_2'],
[KEYWORD = 'KW_3', VALUE = 'VAL_3']
]
JSON:
[
{"KEYWORD":"KW_1", "VALUE":"VAL_1"},
{"KEYWORD":"KW_2", "VALUE":"VAL_2"},
{"KEYWORD":"KW_3", "VALUE":"VAL_3"}
]
Not sure exactly when you mean - but here's a demo transposing associative arrays to some json style output.
SQL> CREATE OR REPLACE TYPE json_t as OBJECT (JSON_TEXT varchar2(30000));
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE json_tbl as TABLE OF json_t;
2 /
Type created.
SQL>
SQL> create or replace
2 package pkg is
3 type str_list is table of varchar2(200) index by pls_integer;
4
5 function f(p_name str_list, p_val str_list) return json_tbl pipelined;
6 end;
7 /
Package created.
SQL>
SQL>
SQL> create or replace
2 package body pkg is
3 function f(p_name str_list, p_val str_list) return json_tbl pipelined is
4 begin
5 for i in 1 .. p_name.count loop
6 pipe row ( json_t ( '{ '||p_name(i)||':'||p_val(i)||'}' ) ) ;
7 end loop;
8 return;
9 end;
10 end;
11 /
Package body created.
SQL> sho err
No errors.
SQL>
SQL> set serverout on
SQL> declare
2 n pkg.str_list;
3 v pkg.str_list;
4 begin
5 for i in 1 .. 10 loop
6 n(i) := 'KEY'||i;
7 v(i) := 'VAL'||i;
8 end loop;
9
10 for i in ( select * from table(pkg.f(n,v)) )
11 loop
12 dbms_output.put_line(i.json_text);
13 end loop;
14 end;
15 /
{ KEY1:VAL1}
{ KEY2:VAL2}
{ KEY3:VAL3}
{ KEY4:VAL4}
{ KEY5:VAL5}
{ KEY6:VAL6}
{ KEY7:VAL7}
{ KEY8:VAL8}
{ KEY9:VAL9}
{ KEY10:VAL10}
PL/SQL procedure successfully completed.