Load Data or Query Data from Files in a Directory

You can use DBMS_CLOUD procedures to load data from files in a directory, including directories created on attached network file systems. You can also use these procedures to create external tables that you can use to query data.

The following DBMS_CLOUD procedures support loading data into the database from a directory:

  • DBMS_CLOUD.COPY_COLLECTION
  • DBMS_CLOUD.COPY_DATA

In addition, the following DBMS_CLOUD procedures support creating external tables from data in files in a directory.

  • DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
  • DBMS_CLOUD.CREATE_EXTERNAL_TABLE
  • DBMS_CLOUD.CREATE_HYBRID_PART_TABLE

Depending on the procedure, use either the file_uri_list parameter or the partitioning_clause parameter to specify files in one or more directories.

To load data from a directory, you do not need to include the credential_name parameter, but you need READ object privileges on the directory.

For example, you can load data into an existing table using DBMS_CLOUD.COPY_DATA:

  1. Use an existing directory, create a directory, or attach a network file system for the source files.

    See Create Directory in Autonomous Database for information on creating a local directory on your Autonomous Database instance.

    See Attach Network File System to Autonomous Database for information on attaching a network file system that contains the data you want to load.

  2. Load data with a DBMS_CLOUD procedure.

    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',
        file_uri_list => 'MY_DIR:channels.txt',
        format => json_object('delimiter' value ',')
     );
    END;
    /
    

    The parameters are:

    • table_name: is the target table’s name.

    • file_uri_list: is a directory and file names specification for the source files you want to load.

      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.

      See Load Data from Directories in Autonomous Database for more details and example of specifying files in a directory. This also shows information on quoting the directory name to make the directory name case sensitive, and for information on using wildcards.

    • format: defines the options you can specify to describe the format of the source file, including whether the file is of type text, ORC, Parquet, or Avro.

See COPY_DATA Procedure for more information.

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