DBA Hub

📋Steps in this guide1/5

Loading SODA Collections with SQL*Loader in Oracle Database 23ai/26ai

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

oracle 23configurationintermediate
by OracleDba
16 views
1

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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

[
  {
    "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
  }
]
2

Create a SODA Collection

We use SODA for PL/SQL to create a new collection. The collection currently has no rows.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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>

select count(*) from "TestCollection1";

  COUNT(*)
----------
         0

SQL>
3

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. Once installed we use the JQ command to turn the array into a list of JSON Objects. The "/tmp/objects2.json" file now has the following contents. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
dnf install -y jq

cat /tmp/objects.json | jq -cr '.[]' > /tmp/objects2.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}
4

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. We run SQL*Loader using the control file, connecting to our test user. We can get more details about the load operation by checking the log file. From SQL*Plus we can see the collection now contains the 100 rows we loaded.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
load data
infile '/tmp/objects2.json'
append
into collection TestCollection1
fields terminated by '0x02'
($CONTENT)

$ 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.
$

$ 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
$

select count(*) from "TestCollection1";

  COUNT(*)
----------
       100

SQL>
5

Drop the SODA Collection

We can drop the SODA collection with the following code. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!