Articles in this section

Import data into Oracle Autonomous Database (ADB)

You can create an import into Oracle ADB in either of these ways:

  • as part of a flow while designing or building your flow (Flow builder > Add destination app)

– or –

  • as a standalone resource that you can later attach to a flow (Resources > Imports)

Tip

The navigation paths differ, but the importing task is the same.

Creating an import into Oracle ADB consists of two logical parts: providing general details and configuring your import.

Provide general details

To import data into Oracle ADB, you can start by creating a destination or creating an import resource and provide details:

Option A: Add/create a destination in a flow

  1. From the main menu, select Tools> Flow builder.

  2. In the Flow builder, click the plus sign (Define_options.svg) next to Destinations & Lookups.

    17875007568155-adb-create-destination.png
  3. In Create destination/lookup, for Application, select Oracle Autonomous Database

  4. For What would you like to do?, select Import records into destination application.

  5. For Connection, select a previously created Oracle ADB connection from the drop-down menu. It saves time if you've already created your connection and only have to select it. However, you can create a connection if you haven't done so already.

  6. Click Next.

– or –

Option B: Create an import resource

  1. From the main menu, select Resources >Imports.

  2. Click + New import.

    adb-create-import-resource.png
  3. In Create import, for Application, select Oracle Autonomous Database.

  4. For Connection, select a previously created Oracle Autonomous Database connection from the drop-down menu. It saves time if you're already created your connection and only have to select it. However, you can create a connection if you haven't done so already.

  5. For Name, enter a meaningful name based on the purpose, function, or any other aspect.

  6. For Description, optionally provide details that will help in understanding your import.

  7. Click Next.

You can select Use existing import check box later when you've created an import and want to reuse it.

Note

You've completed providing the general import details. Next, you must configure the import.

Configure your import

Oracle ADB uses standard SQL queries to import and modify data. If your flow requires multiple SQL queries, you must create one import for each query. You can't use multiple SQL queries on a single import.

For Oracle ADB, you must configure the required settings in General and How would you like the records imported? sections for data to be imported successfully. (You can configure the optional settings as required for any customization.)

Configure required import settings

  1. In Name your import, enter a meaningful name based on the purpose, function, or any other aspect.

    Note

    • If you've used the Resources > Imports path to create an import, then this value is automatically filled in.

    • When importing records, the default for One to many is No. If you've a scenario where you want to use One to many, that is, you have a single record that internally needs to create multiple records, see Create a one to many import including nested arrays

  2. Under How would you like the fields to be imported? for Choose type , select any of the following options based on your requirements.

    import-options-jdbc.png

    Setting

    Description

    Use bulk insert SQL query

    The bulk insert data option is ideal for large data volumes. integrator.io builds the insert query for you automatically for each batch of records. You can select the destination table to receive bulk insert data by validated table name or by referencing a dynamic lookup with a handlebars expression that identifies the destination table. The default number of records bound in each batch is 100, but you can use the Batch size setting in the Advanced section to tune your imports as needed.

    For bulk insert, provide a table name in Destination table.

    (Optional) Use mapping for bulk insert option

    When using the bulk insert option, you can map the data.

    1. Click the mappings (mapping.svg) button on the Oracle ADB import step.

      adb-mapping-icon.png
    2. Edit the mapping between your source and the Oracle ADB import, then click the Settings (settings_gear.svg) button to the right of the mapped settings/fields.

      adb-bulk-mapping.png
    3. Set the Data type based on your requirement.

    4. Click Preview to view the output.

    5. Click Save.

    Use SQL query once per record

    Execute a SQL query once for each record. You can write your SQL command in the SQL query text box. Click Edit (handlebars_editor.svg) to the right of the text box to open the SQL Query builder AFE. In the query, if you're using dates, you must provide the data type explicitly. For example,

    INSERT INTO REST_API_TEST_1 (ID, NAME, AGE, ADDRESS, SALARY, EXPORTED, JOINING) values ({{record.ID}}, {{record.NAME}}, {{record.AGE}}, {{record.ADDRESS}}, {{record.SALARY}}, {{record.EXPORTED}}, TO_DATE({{record.JOINING}},'YYYY-MM-DD"T"HH24:MI:SS"Z"'))

    Use SQL query once per page of records

    Execute a SQL query once per page of records. You can write your SQL command in the SQL query text box. Click Edit (handlebars_editor.svg) to the right of the text field to open the SQL Query builder AFE.

  3. Click Preview to check the import. If the SQL query is not executed successfully, revise the query.

  4. Click Save.

When an import is saved,

  • if you've created it in a flow, the import is added to your flow in Flow builder.

  • if you've created it as a resource, then it is added under Resources > Imports.

Optional settings/sections

Optional sections/settings

Instructions

Mock response

Mock response

See Provide mock response data

Advanced

Batch

The Batch setting is shown only for a bulk insert. BatchSize indicates the number of records that will be imported in one request. The default value is 100.

Concurrency ID lock template

See Build concurrency ID lock template

Data URI template

See Data URI template

Invoke

Copy the URL if you want to invoke this resource via an HTTP request.

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.