Upload Data from a Flat File into a Database Table

The Oracle Autonomous Transaction Processing Adapter can read rows of data in chunks from a comma-separated value (CSV) or XML file and insert them into a table in the Oracle Autonomous Transaction Processing database. With support for cloud libraries in the Oracle Autonomous Transaction Processing database, this operation can be completed much faster. This section provides details about how to use the Oracle Autonomous Transaction Processing Adapter to insert data from a large, flat CSV file into an Oracle Autonomous Transaction Processing database.

This use case is first demonstrated with a standard integration that:

• Retrieves a CSV file from the embedded File Server included in Oracle Integration.
• Uses the Oracle Autonomous Transaction Processing Adapter to process the file in chunks and perform a standard insert operation into an Oracle Autonomous Transaction Processing database table.

This standard integration is shown.

To demonstrate the enhanced integration use case:

• The same file is read from the same SFTP server location and uploaded to an object storage location within Oracle Cloud Infrastructure through a REST Adapter invoke connection.
• The Oracle Autonomous Transaction Processing Adapter invokes the COPY_DATA PL/SQL procedure from the DBMS_CLOUD standard package library, which reads the file from the object storage location and inserts the data into the target table in the Oracle Autonomous Transaction Processing database. PL/SQL package library details are available in the Oracle Autonomous Transaction Processing documentation.

This enhanced integration is shown.

The configuration process is described in two sections:

• The standard integration that is built uses basic components.
• The enhanced integration that is built uses PL/SQL libraries in the Oracle Autonomous Transaction Processing database.

Standard Integration

An overview of the steps to build the standard integration is provided below.

1. Configure the File Server to enable SFTP server functionality. See Administer File Server in Using File Server in Oracle Integration 3.
2. Create an FTP Adapter connection to retrieve the file from the File Server.
3. Create an Oracle Autonomous Transaction Processing Adapter connection to point to the target Oracle Autonomous Transaction Processing database.
4. Build the integration shown in the first image above.
5. In the Adapter Endpoint Configuration Wizard, select the insert operation with the target table in the Oracle Autonomous Transaction Processing database.

Enhanced Integration

An overview of the steps to build the enhanced integration is provided below.

1. Use the same SFTP connection built in Step 2 of the previous section.
2. Use the same Oracle Autonomous Transaction Processing Adapter connection from Step 3 of the previous section.
3. Create a REST Adapter connection to upload a CSV file to an Oracle Cloud Infrastructure object storage location.
4. Create PL/SQL credentials in the database to access the object store using the DBMS_CLOUD.CREATE_CREDENTIAL procedure.
5. Create a PL/SQL wrapper package and procedure to call DBMS_CLOUD.COPY_DATA using the credentials from Step 4 and the target table in the Oracle Autonomous Transaction Processing database to insert the data.
6. Build the enhanced integration shown in the second image above.
7. In the Adapter Endpoint Configuration Wizard, instead of selecting the insert operation, select the PL/SQL procedure option.
8. Select the wrapper procedure created in Step 5.

XML files can also be processed by DBMS_CLOUD. See this blog.