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:

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

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 AI Database for information on creating a local directory on your Autonomous AI Database instance.

    See Attach Network File System to Autonomous AI 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 AI 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 AI Database for information on attaching network file systems.