Create Credentials and Copy JSON Data into an Existing Table

Use DBMS_CLOUD.COPY_DATA to load JSON data in the cloud into a table.

The source file in this example is a JSON data file.

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.

   Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

2. Load JSON data into an existing table using the procedure DBMS_CLOUD.COPY_DATA.

   For example:

   CREATE TABLE WEATHER2
       (WEATHER_STATION_ID VARCHAR2(20),
        WEATHER_STATION_NAME VARCHAR2(50));
   / 
   
   BEGIN 
     DBMS_CLOUD.COPY_DATA(
         table_name      => 'WEATHER2',
         credential_name => 'DEF_CRED_NAME',
         file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/jsonfiles*',
         format          =>  JSON_OBJECT('type' value 'json', 'columnpath' value '["$.WEATHER_STATION_ID",
             "$.WEATHER_STATION_NAME"]')
       );
   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.

   • file_uri_list: is a comma delimited list of the source files you want to load. You can use wildcards in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

   • format: for DBMS_CLOUD.COPY_DATA with JSON data, the type is json. Specify other format values to define the options to describe the format of the JSON source file. See DBMS_CLOUD Package Format Options for more information.

   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.