C Transferring Table Data

This chapter outlines the steps to move table data from one Oracle Autonomous Data Warehouse (ADW) database to another using Oracle Cloud Object Storage and a pre-authenticated request (PAR) URI with read/write permissions.

Assumptions

• You have a list of table names to export.

• You have a read/write prefix-level PAR URI for an Oracle Object Storage bucket.

• You want to export each table as JSON – CSV has issues with null in the last column.

• Each table’s data will be stored under a URI path like <PAR_URI>/table_name.

• You will use DBMS_CLOUD.EXPORT_DATA for export and DBMS_CLOUD.COPY_DATA for import.

• Multi-part and timestamped files will be generated during export.

• You are importing into existing tables with matching structures in the destination ADW.

Step 1: Export Tables from Source ADW

Use DBMS_CLOUD.EXPORT_DATA to export each table to the Object Store.

Example PL/SQL Block

DECLARE
  c_base_uri VARCHAR2(4000) := '<PAR URI>/<prefix>'; -- without trailing slash
  c_table_list SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('ITEM_LOC_SOH_EOD', 'ITEM_LOC_SOH', 'ITEM_LOC');
  l_columns CLOB;
  l_query CLOB;
BEGIN
  FOR i IN 1 .. c_table_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('outputing ' || c_table_list(i) || ' to uri: ' || c_base_uri || '/' || LOWER(c_table_list(i)));
 
    -- To guarantee a specific ordering of columns, you can use the ORDER BY 
    -- clause within the JSON_ARRAYAGG function.
    SELECT LISTAGG(column_name, ', ') 
           WITHIN GROUP (ORDER BY column_id)
      into l_columns
    FROM user_tab_columns
    WHERE table_name = c_table_list(i);
 
     -- build query
    l_query := 'SELECT ' || l_columns || ' FROM ' || c_table_list(i);
 
    DBMS_OUTPUT.PUT_LINE('columns: ' || l_columns);
    DBMS_OUTPUT.PUT_LINE('query: ' || l_query);
    DBMS_CLOUD.EXPORT_DATA(
      credential_name => NULL,  -- PAR requires no credential
      file_uri_list   => c_base_uri || '/' || LOWER(c_table_list(i)),
      format          => JSON_OBJECT('type' VALUE 'json'),
      query           => l_query
    );
  END LOOP;
END;

This generates files in object store like:

<PAR_URI>/orders_1_<timestamp1>.json
<PAR_URI>/orders_1_<timestamp1>.json
... and so on

Step 2: Import Tables to Destination ADW

Use DBMS_CLOUD.COPY_DATA to import each exported table using wildcards to match multi-part, timestamped files. The destination tables already exist. Data is inserted into the destination table.

Example PL/SQL Block

DECLARE
  c_base_uri VARCHAR2(4000) := '<PAR URI>/<prefix>'; -- same PAR URI used for export
  c_table_list SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('ITEM_LOC_SOH_EOD', 'ITEM_LOC_SOH', 'ITEM_LOC');
  l_columnpath CLOB;
BEGIN
  FOR i IN 1 .. c_table_list.COUNT LOOP
    -- To guarantee a specific ordering of columns, use the ORDER BY 
    -- clause within the JSON_ARRAYAGG function. Column order in
    -- columnpath must match table column ordering.  First column in
    -- columnpath matches first column in table.
    SELECT JSON_ARRAYAGG('$.' || column_name) 
      WITHIN GROUP (ORDER BY column_id)
      INTO l_columnpath
    FROM user_tab_columns
    WHERE table_name = c_table_list(i);
     
    DBMS_OUTPUT.PUT_LINE('Importing ' || c_table_list(i) || ' to uri: ' || c_base_uri || '/' || LOWER(c_table_list(i)));
    DBMS_CLOUD.COPY_DATA(
      table_name      => c_table_list(i),
      credential_name => NULL,
      file_uri_list   => c_base_uri || '/' || LOWER(c_table_list(i)) || '*',
      format          => JSON_OBJECT('type' VALUE 'json', 'columnpath' VALUE l_columnpath)
    );
  END LOOP;
END;

Notes

• The file_uri_list uses a wildcard * to match all exported file parts.
• Ensure the target tables already exist in the destination ADW with compatible structure.
• This process requires that the PAR remains valid and unexpired during export and import.
• Use LOWER(r.table_name) to maintain consistency in file naming.

Optional Enhancements

• Add error handling and logging.
• Add support for column.
• Add a control table to track exports and imports.