◉ JSON Data Type
The JSON data type was introduced in Oracle database 21c. You can find more information about the JSON data type here.
The initial version of the JSON data type constructor was quite simplistic. In Oracle database 23c the constructor is more flexible, and can now accept collections, object types and record types as input.
◉ Associative Array (Index By Table)
In the following example we create an associative array (index by table) populated with three items. We use the JSON constructor to convert the collection into JSON. When we display the contents of the JSON, we see a JSON object containing the three numbered items.
set serveroutput on
declare
type t_tab is table of varchar2(10) index by pls_integer;
l_tab t_tab;
l_json json;
begin
l_tab := t_tab('banana', 'apple', 'orange');
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
{"1":"banana","2":"apple","3":"orange"}
PL/SQL procedure successfully completed.
SQL>
We can do a similar thing when the associative array is indexed by a VARCHAR2.
set serveroutput on
declare
type t_tab is table of varchar2(10) index by varchar2(10);
l_tab t_tab;
l_json json;
begin
l_tab := t_tab('fruit1' => 'banana',
'fruit2' => 'apple',
'fruit3' => 'orange');
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
{"fruit1":"banana","fruit2":"apple","fruit3":"orange"}
PL/SQL procedure successfully completed.
SQL>
◉ Nested Table
In the following example we create a nested table populated with three items. We use the JSON constructor to convert the nested table into JSON. When we display the contents of the JSON, we see a JSON object containing the three numbered items.
set serveroutput on
declare
type t_tab is table of varchar2(10);
l_tab t_tab;
l_json json;
begin
l_tab := t_tab('banana', 'apple', 'orange');
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
{"1":"banana","2":"apple","3":"orange"}
PL/SQL procedure successfully completed.
SQL>
◉ Varrays
In the following example we create a varray populated with three items. We use the JSON constructor to convert the varray into JSON. When we display the contents of the JSON, we see a JSON array containing the three items.
set serveroutput on
declare
type t_tab is varray(5) of varchar2(10);
l_tab t_tab;
l_json json;
begin
l_tab := t_tab('banana', 'apple', 'orange');
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
["banana","apple","orange"]
PL/SQL procedure successfully completed.
SQL>
◉ Record Types
In the following example we create a record type populated with some data. We use the JSON constructor to convert the record type into JSON. When we display the contents of the JSON, we see a JSON object matching the record type definition.
set serveroutput on
declare
type t_rec is record (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
l_rec t_rec;
l_json json;
begin
l_rec := t_rec(id => 1,
val1 => 'banana',
val2 => 'apple');
l_json := json(l_rec);
dbms_output.put_line(json_serialize(l_json));
end;
/
{"ID":1,"VAL1":"banana","VAL2":"apple"}
PL/SQL procedure successfully completed.
SQL>
◉ Object Types
In the following example we create an object type, and in our code we create an instance of that object type populated with some data. We use the JSON constructor to convert the object type into JSON. When we display the contents of the JSON, we see a JSON object matching the object type definition.
create or replace type t_obj as object (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
/
set serveroutput on
declare
l_obj t_obj;
l_json json;
begin
l_obj := t_obj(id => 1,
val1 => 'banana',
val2 => 'apple');
l_json := json(l_obj);
dbms_output.put_line(json_serialize(l_json));
end;
/
{"ID":1,"VAL1":"banana","VAL2":"apple"}
PL/SQL procedure successfully completed.
SQL>
◉ Collections of Record Types and Object Types
We've seen examples of the JSON constructor working with collections, record types and object types. It can also work with collections of record types and object types, as demonstrated below.
◉ Associative Array of Record Types
In this example we create a record type, and an associative array based on that record type. We populate the associative array with some data. We use the JSON constructor to convert the associative array into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.
set serveroutput on
declare
type t_rec is record (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
type t_tab is table of t_rec index by pls_integer;
l_tab t_tab;
l_json json;
begin
l_tab(1) := t_rec(id => 1,
val1 => 'banana1',
val2 => 'apple1');
l_tab(2) := t_rec(id => 2,
val1 => 'banana2',
val2 => 'apple2');
l_tab(3) := t_rec(id => 3,
val1 => 'banana3',
val2 => 'apple3');
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
{
"1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
"2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
"3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}
PL/SQL procedure successfully completed.
SQL>
◉ Associative Array of Object Types
In this example we create an object type, and an associative array based on that object type. We populate the associative array with some data. We use the JSON constructor to convert the associative array into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.
create or replace type t_obj as object (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
/
set serveroutput on
declare
type t_tab is table of t_obj index by pls_integer;
l_tab t_tab;
l_json json;
begin
l_tab(1) := t_obj(id => 1,
val1 => 'banana1',
val2 => 'apple1');
l_tab(2) := t_obj(id => 2,
val1 => 'banana2',
val2 => 'apple2');
l_tab(3) := t_obj(id => 3,
val1 => 'banana3',
val2 => 'apple3');
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
{
"1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
"2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
"3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}
PL/SQL procedure successfully completed.
SQL>
◉ Nested Table of Record Types
In this example we create an record type, and a nested table based on that record type. We populate the nested table with some data. We use the JSON constructor to convert the nested table into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.
set serveroutput on
declare
type t_rec is record (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
type t_tab is table of t_rec index by pls_integer;
l_tab t_tab;
l_json json;
begin
l_tab := t_tab(t_rec(id => 1,
val1 => 'banana1',
val2 => 'apple1'),
t_rec(id => 2,
val1 => 'banana2',
val2 => 'apple2'),
t_rec(id => 3,
val1 => 'banana3',
val2 => 'apple3'));
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
{
"1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
"2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
"3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}
SQL>
◉ Nested Table of Object Types
In this example we create an object type, and a nested table based on that object type. We populate the nested table with some data. We use the JSON constructor to convert the nested table into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.
create or replace type t_obj as object (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
/
set serveroutput on
declare
type t_tab is table of t_obj;
l_tab t_tab;
l_json json;
begin
l_tab := t_tab(t_obj(id => 1,
val1 => 'banana1',
val2 => 'apple1'),
t_obj(id => 2,
val1 => 'banana2',
val2 => 'apple2'),
t_obj(id => 3,
val1 => 'banana3',
val2 => 'apple3'));
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
{
"1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
"2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
"3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}
SQL>
◉ Varray of Record Types
In this example we create an record type, and a varray based on that record type. We populate the varray with some data. We use the JSON constructor to convert the varray into JSON. When we display the contents of the JSON, we see a JSON array containing a JSON object for each row in the varray.
set serveroutput on
declare
type t_rec is record (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
type t_tab is varray(5) of t_rec;
l_tab t_tab;
l_json json;
begin
l_tab := t_tab(t_rec(id => 1,
val1 => 'banana1',
val2 => 'apple1'),
t_rec(id => 2,
val1 => 'banana2',
val2 => 'apple2'),
t_rec(id => 3,
val1 => 'banana3',
val2 => 'apple3'));
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
[
{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
]
SQL>
◉ Varray of Object Types
In this example we create an object type, and a varray based on that object type. We populate the varray with some data. We use the JSON constructor to convert the varray into JSON. When we display the contents of the JSON, we see a JSON array containing a JSON object for each row in the varray.
create or replace type t_obj as object (
id number,
val1 varchar2(10),
val2 varchar2(10)
);
/
set serveroutput on
declare
type t_tab is varray(5) of t_obj;
l_tab t_tab;
l_json json;
begin
l_tab := t_tab(t_obj(id => 1,
val1 => 'banana1',
val2 => 'apple1'),
t_obj(id => 2,
val1 => 'banana2',
val2 => 'apple2'),
t_obj(id => 3,
val1 => 'banana3',
val2 => 'apple3'));
l_json := json(l_tab);
dbms_output.put_line(json_serialize(l_json));
end;
/
[
{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
]
SQL>
Source: oracle-base.com
Posts
0 comments:
Post a Comment