Oracle Cloud Infrastructure Documentation

Load Data from Directories in Autonomous Database

As an alternative to an object store location URI, you can specify a directory with DBMS_CLOUD procedures to load or unload data from files in a local directory, including directories created on attached network file systems.

The following procedures support specifying files in a directory with the file_uri_list parameter:

  • DBMS_CLOUD.COPY_COLLECTION
  • DBMS_CLOUD.COPY_DATA
  • DBMS_CLOUD.CREATE_EXTERNAL_TABLE

The following procedures support specifying files in a directory with the partitioning_clause parameter:

  • DBMS_CLOUD.CREATE_EXTERNAL_TABLE
  • DBMS_CLOUD.CREATE_HYBRID_PART_TABLE

You can specify one directory and one or more file names or use a comma separated list of directories and file names. The format to specify a directory is:'MY_DIR:filename.ext'. By default the directory name MY_DIR is a database object and is case-insensitive. The file name is case sensitive.

When you use the file_uri_list parameter to specify a directory you do not need to include the credential_name parameter, but you need READ object privileges on the directory.

For example, with a call to DBMS_CLOUD.COPY_DATA, use the file_uri_list parameter to specify files in a directory: 

BEGIN
  DBMS_CLOUD.COPY_DATA(
     table_name => 'HRDATA1',
     file_uri_list => 'HR_DIR:test.csv',
     format => JSON_OBJECT('type' value 'csv')  );
END;
/

This example copies the data from test.csv in the local directory HR_DIR to the table HRDATA1.

Regular expressions are not supported when specifying the file names in a directory. You can only use wildcards to specify file names in a directory. The character "*" can be used as the wildcard for multiple characters, and the character "?" can be used as the wildcard for a single character. For example:'MY_DIR:*" or 'MY_DIR:test?'

To specify multiple directories, use a comma separated list of directories: For example:'MY_DIR1:*, MY_DIR2:test?'

Use double quotes to specify a case-sensitive directory name. For example:'"my_dir1":*, "my_dir2":Test?'

To include a quote character, use two quotes. For example:'MY_DIR:''filename.ext'. This specifies the filename starts with a quote (').

See Attach Network File System to Autonomous Database for information on attaching network file systems.

Notes for Using Directories with DBMS_CLOUD Procedures

Note the following when you use DBMS_CLOUD procedures and specify a directory with the file_uri_list parameter:

  • Compression options for files such as GZIP are not supported for directory files. See the compression format option in DBMS_CLOUD Package Format Options for more information.

  • Special characters such as colon (:), single quote('), and comma(,) are not supported in the directory name.