Load a Fixed-Width File into a New Table

This provides an example using DBMS_CLOUD.CREATE_EXTERNAL_TABLE to load data from a fixed-width source file to an external table.

For this example, the fixed-width source file has the following data:

 0INDEX01    INDEX                    2001272020012720200127VALID
 1INDEX02    INDEX                    2001272020012720200127VALID
 2INDEX03    INDEX                    2001272020012720200127VALID
 3INDEX04    INDEX                    2001272020012720200127VALID
 4TABLE01    TABLE                    2001272020012720200918VALID
 5TABLE02    TABLE                    2001272020012720200918VALID
 6CLUSTER01  CLUSTER                  2001272020012720200127VALID
 7INDEX05    INDEX                    2001272020012720200127VALID
 8INDEX06    INDEX                    2001272020012720200127VALID
 9INDEX07    INDEX                    2001272020012720200127VALID
10INDEX08    INDEX                    2001272020012720200127VALID
11TABLE03    TABLE                    2001272020012720200127VALID
12INDEX09    INDEX                    2001272020012720200127VALID
13INDEX10    INDEX                    2001272020012720200127VALID
14TABLE04    TABLE                    2001272020012720200127VALID
15INDEX11    INDEX                    2001272020012720200127VALID

1. From the console, select the compartment for your Autonomous AI Database, and then select the link to your Autonomous AI Database to open the console.

   Note: These steps are shown using Database Actions to execute the PL/SQL code, and query the data. These actions can be performed from any SQL connection, connecting to the Autonomous AI Database as a user with the proper privileges.

2. On the Autonomous AI Database Details page select Database Actions and in the list click SQL.

   As an alternative, select Database Actions and click View all database actions to access the Database Actions Launchpad. From the Development section of the Database Actions Launchpad, select SQL.

3. Within the SQL Worksheet, enter and execute the following code:

   BEGIN DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
                table_name      => '<YOUR_TABLE_NAME>'
   ,            credential_name => '<YOUR_CREDENTIAL_NAME>'
   ,            file_uri_list   => '<YOUR_ORACLE_OBJECT_STORE_URL>'
   ,            format          => json_object('trimspaces' value 'rtrim','skipheaders' value '1', 'dateformat' value 'YYYYMMDD')
   ,            field_list      => 'object_id      (1:3)   char
   
                                  , object_name    (4:14)  char
   
                                  , object_type    (15:39)  char
   
                                  , created_date1  (40:45)  date mask "YYMMDD"
   
                                  , created_date2  (46:53)  date
   
                                  , last_ddl_time  (54:61)  date
   
                                  , status         (62:71)'
   ,            column_list     => 'object_id      number
                                  , object_name    varchar2(30)
                                  , object_type    varchar2(25)
                                  , status         varchar2(10)
                                  , created_date1  date
                                  , created_date2  date
                                  , last_ddl_time  date');
   END;
   /