Oracle Data Pump Export/Import SODA Collection Data

Simple Oracle Document Access (SODA ) collections can be exported and imported using Oracle Data Pump Utilities starting with version 19.6.

In Oracle database, a SODA collection is analogous to database table. A SODA collection is mapped to /backed by a table. A SODA document is analogous to, and is in fact backed by, a row of a database table. The row has one column for each document component: key, content, version, and so on.

When a SODA colleciton is created, a database table (mapped table) is created under current user and related collection metadata is saved under schema XDB. When inserting a document into a collection, it actually insert a record into the collection mapped table.

Therefore, export/import SODA include two parts,

  * Export/import the mapped table with Data Pump utility. Utility has to be 19.6 or higher

  * Copy collection metadata. Currently, Data Pump does not have specific export path for SODA Collection Metadata, it means there is no database object type for it used in export/import parameters INCLUDE & EXCLUDE. As workaround, the metadata can be 'copied' by generating creation script in source database and running generated script in destination database.

Step 1. Export the mapped table (contains SODA documents) with Data Pump

The SODA Collection mapped tables can be exported individually or together with other tables/objects in SCHEMA mode or FULL mode.

For example, user soda_user logs into database and list his SODA collections and tables as following

soda_user@db01> select uri_name,object_type,object_schema,object_name from user_soda_collections;

URI_NAME          OBJECT_TYPE  OBJECT_SCHEMA   OBJECT_NAME
----------------- ------------ --------------- ------------------------------
SodaCollection1   TABLE        SODA_USER       SodaCollection1
SodaCollection2   TABLE        SODA_USER       SodaCollection2

soda_user@db01> select table_name from user_tables;

TABLE_NAME
------------------------------
SodaCollection1
SodaCollection2

The first SQL list all SODA Collections for current user 'SODA_USER', the two collections' names are 'SodaCollection1' and 'SodaCollection2' (column URI_NAME of view USER_SODA_COLLECITONS) and their respective mapped tables are created under current schema 'SODA_USER' with same names as collection names. 

Note: the collection name and table name are case sensitive.

The sencod SQL list all tables owned by current user, the output confirms that the two mapped tables listed in first SQL do be there.

The mapped tables can be exported with command

   expdp username/password schemas=<schema_name> include=<table_list> dumpfile=<dump_file> logfile=<log_file>

or

   expdp username/password schemas=<schema_name> dumpfile=<dump_file> logfile=<log_file>

If possible, I prefer the second command because I do not have to list all tables if many, especially the headache to escape the quota (double & single) characters in the INCLUDE parameter.

For example, export specified tables as following

expdp soda_user/password schemas=soda_user include=TABLE:\"in (\'SodaCollection1\',\'SodaCollection2\')\"  dumpfile=soda_collection.dmp logfile=soda_collection_exp.log

Step 2. Unload SODA collection metadata by generatting collection-creating script from source database

Log into source database as user who created the collection (soda_user in my example database), run following PL/SQL to generate scripts

set serveroutput on
set feedback off
set linesize 32767
begin
  dbms_output.put_line('set serveroutput on');
  dbms_output.put_line('declare');
  dbms_output.put_line('  soda_c soda_collection_t;');
  dbms_output.put_line('begin');
  for c1 in (select uri_name, json_descriptor from user_soda_collections) loop
    dbms_output.put_line('  -- Collection '''||c1.uri_name||'''');
    dbms_output.put_line('  begin');
    dbms_output.put_line('    soda_c:=dbms_soda.create_collection(collection_Name=>'''||c1.uri_name||''', metadata=>'''||c1.json_descriptor||''');');
    dbms_output.put_line('    dbms_output.put_line(''  [INFO]  SODA Collection ['||c1.uri_name||'] created successfully'');');
    dbms_output.put_line('  exception');
    dbms_output.put_line('    when others then');
    dbms_output.put_line('      dbms_output.put_line(''  [ERROR] SODA Collection ['||c1.uri_name||'] creation failed with errors:'');');
    dbms_output.put_line('      dbms_output.put_line(SQLERRM);');
    dbms_output.put_line('  end;');
  end loop;
  dbms_output.put_line('end;');
  dbms_output.put_line('/');
end;
/

The script reads SODA collections' names (column URI_NAME of view USER_SODA_COLLECTIONS) and respective collections' metadata (column JSON_DESCRIPTION of view USER_SODA_COLLECTION), and generates creation statements for each collection.

Save the output as script file. If it is run with SQL*Plus or SQLcl, the output can be saved with SPOOL command.

Sample output as following

set serveroutput on
declare
  soda_c soda_collection_t;
begin
  -- Collection 'SodaCollection1'
  begin
    soda_c:=dbms_soda.create_collection(collection_Name=>'SodaCollection1', metadata=>'{"schemaName":"SODA_USER","tableName":"SodaCollection1","keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"CLIENT"},"contentColumn":{"name":"DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},"lastModifiedColumn":{"name":"MODIFIEDON"},"mediaTypeColumn":{"name":"MEDIATYPE"},"creationTimeColumn":{"name":"CREATEDON"},"readOnly":false}');
    dbms_output.put_line('  [INFO]  SODA Collection [SodaCollection1] created successfully');
  exception
    when others then
      dbms_output.put_line('  [ERROR] SODA Collection [SodaCollection1] creation failed with errors:');
      dbms_output.put_line(SQLERRM);
  end;
  -- Collection 'SodaCollection2'
  begin
    soda_c:=dbms_soda.create_collection(collection_Name=>'SodaCollection2', metadata=>'{"schemaName":"SODA_USER","tableName":"SodaCollection2","keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"CLIENT"},"contentColumn":{"name":"DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},"lastModifiedColumn":{"name":"MODIFIEDON"},"versionColumn":{"name":"VERSION","method":"MD5"},"creationTimeColumn":{"name":"CREATEDON"},"readOnly":false}');
    dbms_output.put_line('  [INFO]  SODA Collection [SodaCollection2] created successfully');
  exception
    when others then
      dbms_output.put_line('  [ERROR] SODA Collection [SodaCollection2] creation failed with errors:');
      dbms_output.put_line(SQLERRM);
  end;
end;
/

In the example, the database user SODA_USER has two SODA collections 'SodaColleciton01' and 'SodaColleciton02'. 

Note: the value of "schemaName" in metadata section is "SODA_USER" which owns the SODA data, it is hard-coded in the script. Thereore, the generated script has to be run under same user in destination database.

Step 3. Import the mapped table (contains SODA documents) into destination database with Data Pump

The mapped tables can be imported into destination database with command

   impdp username/password schemas=<schema_name> dumpfile=<dump_file> logfile=<log_file>

Here, <dump_file> is dump file created in step 1.

Step 4. Load SODA collection metadata into destination database by running collection-creating script created in step 2

Since the script hard-coded the schema name in metadata section, it has to be run by same user in source database. In my example, the hard-coded schema name is SODA_USER, and the user SODA_USER has to be created in my source database and run the script as user SODA_USER.

If the mapped tables are imported to different schema in step 3 (with parameter REMAP_SCHEMA), you have to manually edit the script to replace the old value (SODA_USER in my example) of "schemaName" with the new schema name, then run it as new user.