Export JSON Data to Cloud Object Storage

Shows the steps to export table data from your Autonomous AI Database to Cloud Object Storage as JSON data by specifying a query.

1. Connect to your Autonomous AI Database instance.

   See Connect to Autonomous AI Database for more information.

2. Store your Cloud Object Storage credential using DBMS_CLOUD.CREATE_CREDENTIAL.

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       username => 'user1@example.com',
       password => 'password'
     );
   END;
   /

   The values you provide for username and password depend on the Cloud Object Storage service you are using.

   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.

3. Run DBMS_CLOUD.EXPORT_DATA and specify the format parameter type with the value json to export the results as JSON files on Cloud Object Storage.
   To generate the JSON output files there are two options for the file_uri_list parameter:

   • Set the file_uri_list value to the URL for an existing bucket on your Cloud Object Storage.

   • Set the file_uri_list value to the URL for an existing bucket on your Cloud Object Storage and include a file name prefix to use when generating the file names for the exported JSON.

   If you do not include the file name prefix in the file_uri_list, DBMS_CLOUD.EXPORT_DATA supplies a file name prefix. See File Naming for Text Output (CSV, JSON, Parquet, or XML) for details.

   For example, the following shows DBMS_CLOUD.EXPORT_DATA with a file name prefix specified in file_uri_list:

   BEGIN
     DBMS_CLOUD.EXPORT_DATA(
       credential_name => 'DEF_CRED_NAME',
       file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/dept_export',
       query           => 'SELECT * FROM DEPT',
       format          => JSON_OBJECT('type' value 'json'));
   END;
   /

   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.

   When record delimiters include escape characters, such as \r\n or \t, enclose the record delimiters in double quotes. For example, to use the record delimiter \r\n, enclose the value in double quotes:"\r\n".

   BEGIN
     DBMS_CLOUD.EXPORT_DATA(
       credential_name => 'DEF_CRED_NAME',
       file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/dept_export',
       query => 'SELECT * FROM DEPT',
       format => JSON_OBJECT('type' value 'json', 'recorddelimiter' value '"\r\n"' format json));
   END;
   /

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

   For detailed information about the available format parameters you can use with DBMS_CLOUD.EXPORT_DATA, see DBMS_CLOUD Package Format Options for EXPORT_DATA.