In Oracle 23c we can use SQL*Loader to load JSON data into a Simple Oracle Document Access (SODA) collection.
◉ Create Test Data
We need some test JSON data to work with. This could be an export from a document store, but we will generate some JSON data to load. We spool out the data from a query as a JSON array in pretty print format, so each entry is spread over multiple lines.
conn testuser1/testuser1@//localhost:1521/freepdb1
set long 1000000 pagesize 10000 trimspool on
spool /tmp/objects.json
select json_serialize(
json_array(select json_object('owner' : owner,
'object-name' : object_name,
'subobject-name' : subobject_name,
'object-id' : object_id)
from all_objects
where rownum <= 100
returning json
)
returning clob pretty) as output;
spool off
We will need to tidy up the resulting file, removing the query from the start and the "SQL>" prompt from the end.
We end up with a "/tmp/objects.json" file containing the following JSON.
[
{
"owner" : "SYS",
"object-name" : "ORA$BASE",
"subobject-name" : null,
"object-id" : 138
},
... Edited for brevity
{
"owner" : "SYS",
"object-name" : "V_$HVMASTER_INFO",
"subobject-name" : null,
"object-id" : 2048
}
]
◉ Create a SODA Collection
We use SODA for PL/SQL to create a new collection.
conn testuser1/testuser1@//localhost:1521/freepdb1
set serveroutput on
declare
l_collection soda_collection_t;
begin
l_collection := dbms_soda.create_collection('TestCollection1');
if l_collection is not null then
dbms_output.put_line('Collection ID : ' || l_collection.get_name());
else
dbms_output.put_line('Collection does not exist.');
end if;
end;
/
Collection ID : TestCollection1
PL/SQL procedure successfully completed.
SQL>
The collection currently has no rows.
select count(*) from "TestCollection1";
COUNT(*)
----------
0
SQL>
◉ Prepare the File Using the JQ Command
The file needs to be in a specific format to work with SQL*Loader. We could have created the file in the correct format, but creating it as an array is more like what we would see when we export the data from some document store utilities.
We need the JQ command to transform a file before we can load it with SQL*Loader. We install it on our operating system with the following command, run as the "root" user.
dnf install -y jq
Once installed we use the JQ command to turn the array into a list of JSON Objects.
cat /tmp/objects.json | jq -cr '.[]' > /tmp/objects2.json
The "/tmp/objects2.json" file now has the following contents.
{"owner":"SYS","object-name":"ORA$BASE","subobject-name":null,"object-id":138}
... Edited for brevity
{"owner":"SYS","object-name":"V_$HVMASTER_INFO","subobject-name":null,"object-id":2048}
Notice the surrounding "[]" has been removed, the comma after each JSON Object has been removed, and each JSON Object is compacted into a single line.
◉ Use SQL*Loader to Populate the Collection
We create a SQL*Loader control file called "/tmp/objects.ctl" with the following contents. Notice it references the formatted "/tmp/objects2.json" file for the data, and loads it into the "TestCollection1" collection we created earlier.
load data
infile '/tmp/objects2.json'
append
into collection TestCollection1
fields terminated by '0x02'
($CONTENT)
We run SQL*Loader using the control file, connecting to our test user.
$ cd /tmp
$ sqlldr userid=testuser1/testuser1@//localhost:1521/freepdb1 \
control=/tmp/objects.ctl \
log=/tmp/objects.log
SQL*Loader: Release 23.0.0.0.0 - Developer-Release on Wed Apr 26 09:33:50 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Path used: SODA Collection
Commit point reached - logical record count 100
SODA Collection TestCollection1:
100 Rows successfully loaded.
Check the log file:
objects.log
for more information about the load.
$
We can get more details about the load operation by checking the log file.
$ cat /tmp/objects.log
SQL*Loader: Release 23.0.0.0.0 - Developer-Release on Wed Apr 26 09:53:59 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Path used: SODA Collection
Commit point reached - logical record count 100
SODA Collection TestCollection1:
100 Rows successfully loaded.
Check the log file:
/tmp/objects.log
for more information about the load.
[oracle@localhost tmp]$ cat /tmp/objects.log
SQL*Loader: Release 23.0.0.0.0 - Developer-Release on Wed Apr 26 09:53:59 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Control File: /tmp/objects.ctl
Data File: /tmp/objects2.json
Bad File: /tmp/objects2.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: SODA Collection
SODA Collection TestCollection1, loaded from every logical record.
Insert option in effect for this SODA collection: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
$CONTENT FIRST * CHARACTER
Terminator string : '0x02'
SODA Collection TestCollection1:
100 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 64500 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 100
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed Apr 26 09:53:59 2023
Run ended on Wed Apr 26 09:53:59 2023
Elapsed time was: 00:00:00.10
CPU time was: 00:00:00.03
$
From SQL*Plus we can see the collection now contains the 100 rows we loaded.
select count(*) from "TestCollection1";
COUNT(*)
----------
100
SQL>
◉ Drop the SODA Collection
We can drop the SODA collection with the following code.
set serveroutout on
declare
l_status number := 0;
begin
l_status := dbms_soda.drop_collection('TestCollection1');
dbms_output.put_line('status : ' || l_status);
end;
/
status : 1
PL/SQL procedure successfully completed.
SQL>
Source: oracle-base.com
Posts
0 comments:
Post a Comment