Oracle Cloud Infrastructure Documentation

Export Data to a Directory

Use DBMS_CLOUD.EXPORT_DATA to export files to a directory.
The directory to which you export files can be in the Autonomous Database file system or an attached external file system. See the following pages for more information:

Parent topic: Export Data From Autonomous Database

Export Data as CSV to a Directory

Shows the steps to export table data from your Autonomous Database to a directory as CSV data by specifying a query.

  1. Connect to your Autonomous Database instance.

    See Connect to Autonomous Database for more information.

  2. Create a directory.

    For example:

    CREATE DIRECTORY export_dir AS 'export_dir';
    See Create a Directory for more information.
  3. Run DBMS_CLOUD.EXPORT_DATA and specify the format parameter type with the value json to export the results as CSV files to a directory. Do not include the credential parameter when sending output to a directory.
    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => 'export_dir:sales.csv',
    format => JSON_OBJECT('type' value 'csv'),
    query => 'SELECT * FROM sales'
 );
END;
/

    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(
    file_uri_list => 'export_dir:sales.csv',
    query => 'SELECT * FROM sales',
    format => JSON_OBJECT('type' value 'json', 'recorddelimiter' value '"\r\n"' format json));
END;
/

    The directory name is case-sensitive when the directory name is enclosed in double quotes. For example:

    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => '"export_dir":sales.csv',
    format => JSON_OBJECT('type' value 'csv'),
    query => 'SELECT * FROM sales'
 );
END;
/

    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.

    The parameters are:

    • file_uri_list: is a comma delimited list of the export file(s). Use of wildcard and substitution characters is not supported in the file_uri_list.

    • format: specifies the required type parameter. The valid values are json, xml, and csv.

    • query: specifies a SELECT statement so that only the required data is exported. The query determines the contents of the dump file(s).

    Note

    The DBMS_CLOUD.EXPORT_DATA procedure creates the dump file(s) that you specify in the file_uri_list. The procedure does not overwrite files. If a dump file in the file_uri_list exists, DBMS_CLOUD.EXPORT_DATA generates another file with a unique name. DBMS_CLOUD.EXPORT_DATA does not create directories.

    For detailed information about the parameters, see EXPORT_DATA Procedure.

Notes for exporting with DBMS_CLOUD.EXPORT_DATA:

  • The query parameter that you supply can be an advanced query, if required, such as a query that includes joins or subqueries.

  • Specify the format parameter with the compression option to compress the output files.

Export Data as JSON a Directory

Shows the steps to export table data from your Autonomous Database to a directory as JSON data by specifying a query.

  1. Connect to your Autonomous Database instance.

    See Connect to Autonomous Database for more information.

  2. Create a directory.

    For example:

    CREATE DIRECTORY export_dir AS 'export_dir';
    See Create a Directory 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 to a directory. Do not include the credential parameter when sending output to a directory.
    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => 'export_dir:sales.dmp',
    format => json_object('type' value 'json'),
    query => 'SELECT * FROM sales'
 );
END;
/

    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(
    file_uri_list => 'export_dir:sales.dmp',
    query => 'SELECT * FROM sales',
    format => JSON_OBJECT('type' value 'json', 'recorddelimiter' value '"\r\n"' format json));
END;
/

    The directory name is case-sensitive when the directory name is enclosed in double quotes. For example:

    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => '"export_dir":sales.dmp',
    format => json_object('type' value 'json'),
    query => 'SELECT * FROM sales'
 );
END;
/

    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.

    The parameters are:

    • file_uri_list: is a comma delimited list of the export file(s). Use of wildcard and substitution characters is not supported in the file_uri_list.

    • format: specifies the required type parameter. The valid values are json, xml, and csv.

    • query: specifies a SELECT statement so that only the required data is exported. The query determines the contents of the dump file(s).

    Note

    The DBMS_CLOUD.EXPORT_DATA procedure creates the dump file(s) that you specify in the file_uri_list. The procedure does not overwrite files. If a dump file in the file_uri_list exists, DBMS_CLOUD.EXPORT_DATA generates another file with a unique name. DBMS_CLOUD.EXPORT_DATA does not create directories.

    For detailed information about the parameters, see EXPORT_DATA Procedure.

Notes for exporting with DBMS_CLOUD.EXPORT_DATA:

  • The query parameter that you supply can be an advanced query, if required, such as a query that includes joins or subqueries.

  • Specify the format parameter with the compression option to compress the output files.

Export Data as XML to a Directory

Shows the steps to export table data from your Autonomous Database to Directory as XML data by specifying a query.

  1. Connect to your Autonomous Database instance.

    See Connect to Autonomous Database for more information.

  2. Create a directory.

    For example:

    CREATE DIRECTORY export_dir AS 'export_dir';
    See Create a Directory for more information.
  3. Run DBMS_CLOUD.EXPORT_DATA and specify the format parameter type with the value json to export the results as XML files to a directory. Do not include the credential parameter when sending output to a directory.
    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => 'export_dir:sales.csv',
    format => JSON_OBJECT('type' value 'csv'),
    query => 'SELECT * FROM sales'
 );
END;
/

    The directory name is case-sensitive when the directory name is enclosed in double quotes. For example:

    BEGIN
 DBMS_CLOUD.EXPORT_DATA(
    file_uri_list => '"export_dir":sales.xml',
    format => JSON_OBJECT('type' value 'xml'),
    query => 'SELECT * FROM sales'
 );
END;
/

    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.

    The parameters are:

    • file_uri_list: is a comma delimited list of the export file(s). Use of wildcard and substitution characters is not supported in the file_uri_list.

    • format: specifies the required type parameter. The valid values are json, xml, and csv.

    • query: specifies a SELECT statement so that only the required data is exported. The query determines the contents of the dump file(s).

    Note

    The DBMS_CLOUD.EXPORT_DATA procedure creates the dump file(s) that you specify in the file_uri_list. The procedure does not overwrite files. If a dump file in the file_uri_list exists, DBMS_CLOUD.EXPORT_DATA generates another file with a unique name. DBMS_CLOUD.EXPORT_DATA does not create directories.

    For detailed information about the parameters, see EXPORT_DATA Procedure.

Notes for exporting with DBMS_CLOUD.EXPORT_DATA:

  • The query parameter that you supply can be an advanced query, if required, such as a query that includes joins or subqueries.

  • Specify the format parameter with the compression option to compress the output files.