8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

Loading SODA Collections with SQL*Loader in Oracle Database 23c

In Oracle 23c we can use SQL*Loader to load JSON data into a Simple Oracle Document Access (SODA) collection.

Related 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:

Hope this helps. Regards Tim...

Back to the Top.