8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Loading SODA Collections with SQL*Loader in Oracle Database 23ai
In Oracle 23ai we can use SQL*Loader to load JSON data into a Simple Oracle Document Access (SODA) collection.
- Create Test Data
- Create a SODA Collection
- Prepare the File Using the JQ Command
- Use SQL*Loader to Populate the Collection
- Drop the SODA Collection
Related articles.
- Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
- JSON Support Enhancements in Oracle Database 23ai
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
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>
For more information see:
- SODA Collections and SQL*Loader
- Examples of Loading SODA Collections
- Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
- JSON Support Enhancements in Oracle Database 23ai
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
Hope this helps. Regards Tim...