Create Credentials and Load Data Pump Dump Files into an Existing Table

For data loading you can also use Oracle Data Pump dump files as source files.

The source files for this load type must be exported from the source system using the ORACLE_DATAPUMP access driver in External Tables. See Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver for details on exporting using the ORACLE_DATAPUMP access driver.

To load the data you first move the dump files that were exported using the ORACLE_DATAPUMP access driver to your Object Store and then use DBMS_CLOUD.COPY_DATA to load the dump files to an existing table in your database.

The source files in this example are the Oracle Data Pump dump files, exp01.dmp and exp02.dmp.

1. Store your object store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL. For example:

   SET DEFINE OFF
   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => '[e-mail]',
       password => 'password'
     );
   END;
   /

   This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials you can then use the same credential name for all data loads.

   For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.

   Note:

   Some tools like SQL*Plus and SQL Developer use the ampersand character (&) as a special character. If you have the ampersand character in your password use the SET DEFINE OFF command in those tools as shown in the example to disable the special character and get the credential created properly.
2. Load data into an existing table using the procedure DBMS_CLOUD.COPY_DATA. For example:

   CREATE TABLE CHANNELS
      (channel_id CHAR(1),
       channel_desc VARCHAR2(20),
       channel_class VARCHAR2(20)
      );
   /
   
   BEGIN
    DBMS_CLOUD.COPY_DATA(
       table_name =>'CHANNELS',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp01.dmp,
                        https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp02.dmp',
       format => json_object('type' value 'datapump')
    );
   END;
   /
   

   The parameters are:

   • table_name: is the target table’s name.

   • credential_name: is the name of the credential created in the previous step. The credential_name parameter must conform to Oracle object naming conventions, which do not allow spaces or hyphens.

   • file_uri_list: is a comma delimited list of the Data Pump dump files you want to load.

   • format: defines the options you can specify to describe the format of the source file. When you specify the type as 'datapump', the only other valid format parameter is 'rejectlimit'.

   In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

   For detailed information about the parameters, see COPY_DATA Procedure and COPY_DATA Procedure for Avro, ORC, or Parquet Files.