Bulk load into ATP DB using OIC

This blog gives you the technical steps to upload the bulk load into ATP using the OIC ATP adapter. we have many more blogs on this topic, this bog may cover similar information, but try to cover all minor pointers to be considered for implementation..

Security policy to be used for bulk upload "JDBC with OIC signature" and this needs information that can be obtained from the oracle console and ATP DB. 

below can be obtained from the oracle console.

  1. Wallet and password
  2. Database Service Username and its password
  3. Object Storage Region
  4. Object Storage Tenancy OCID
  5. Object Storage Compartment OCID
  6. Object Storage User OCID
  7. Private Key
  8. Finger Print

Wallet and password: you can get it from ATP DB provisioning from the console

Note : this blog, i am not covering the process of spinning of ATP DB in the console.


you can assign a password for it while downloading

Database Service Username and its password: you would get this from DB admin / in case, if you have spinned off the instance, you would have captured when enabling the DB.

Object Storage Region: 

 Click manage regions in the console, as below.

You would get all regions and copy the region Identifier

 To find Tenancy OCID, go to tenancy and click the hamburger and click Governance and administration, and click tenancies



And copy the below tenancy ID

Go to compartments, in this demo.. I have not created a specific compartment, used the parent compartment only. Search for compartments, as below.

by default, you would see 2 compartments, and I used the root compartment




Click on the above highlighted compartment, and take the compartment OCID, you would see compartment name starts with tenancy.. as this is the root compartment.. you would see tenancy and root OCIC are same.

In the connection, you should use same for both in tenancy and compartment OCID.. else you may not be able to list the buckets in the integration end point.

You can find user OCID below. You will usually find 2 users after creating the new tenancy..

I have considered normal user , instead of federated.. you can use any user having right authorizations.

  Now time to generate the keypair.. you can use the OCI capability of generating key pair, by clicking the API keys, most of the above information is available in the below config file.. this will be available right after you generate the public and private key

Incase, if you are creating a new DB user, make sure that user is REST and graph enabled.

After providing all the above information in the connection, it will be successful.

      Configure your end point, by selecting the below option

    Create a bucket in the compartment, in which you have configured in the connection.

While you configure, you may get the below error. You may get the below error when listing buckets in the ATP end point, this could be because of the policies / not mentioning the right compartment in the connection page.

Please make sure your user should have the below policies mentioned.

Create a policy, as below in the compartment where the bucket is created, and maintain policy statements

Once the above error is corrected, you would be able to select the bucket that you want to use for the bulk load.

 And need to mention the format of input file / attachment format

You can now build integrations to pull a file and send as a reference to end point as below or you can send a rest attachment 

Build the integration as below

ATP adapter only takes the file reference as an input., you can download as staging and map the reference to upload ATP.

Attachment file content should not have double quotes as below.., otherwise it goes for error.


Common errors:

1)      Integration might time out – it could be format issue in the file that you upload or ATP configuration issue

2)      Incase if you get the below issue, its format issue and make sure you don’t use quotes in the field values in the file.

serviceInvocationError : Stored procedure invocation error.

Error while trying to prepare and execute the DBMS_CLOUD.COPY_DATA API.

An error occurred while preparing and executing the DBMS_CLOUD.COPY_DATA API. Cause: java.sql.SQLException: ORA-20003: Reject limit reached, query table "ADMIN"."COPY$123_LOG" for error details

 <a  target="_blank" style="cursor:pointer !important" title="Link to Customer Connect Forum for this error code. It will open in a new browser tab." href="https://cloudcustomerconnect.oracle.com/resources/704fc67d61/search?find=ORA-06512">ORA-06512</a> : at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1263

 <a  target="_blank" style="cursor:pointer !important" title="Link to Customer Connect Forum for this error code. It will open in a new browser tab." href="https://cloudcustomerconnect.oracle.com/resources/704fc67d61/search?find=ORA-06512">ORA-06512</a> : at "C##CLOUD$SERVICE.DBMS_CLOUD", line 4825

 <a  target="_blank" style="cursor:pointer !important" title="Link to Customer Connect Forum for this error code. It will open in a new browser tab." href="https://cloudcustomerconnect.oracle.com/resources/704fc67d61/search?find=ORA-06512">ORA-06512</a> : at line 1

 

Check to ensure that the database objects exists(stored procedures,table,view..etc) in the database and the parameters match the signature of the API-like not exceeding the column range or is not NULL or is already existing value.Please examine the error message carefully to determine a resolution.  This exception is considered not retriable, likely due to a modelling mistake.  To classify it as retriable instead add property nonRetriableErrorCodes with value "-20003" to your deployment descriptor (i.e. weblogic-ra.xml).

ORA-20003: Reject limit reached, query table "ADMIN"."COPY$123_LOG" for error details

 <a  target="_blank" style="cursor:pointer !important" title="Link to Customer Connect Forum for this error code. It will open in a new browser tab." href="https://cloudcustomerconnect.oracle.com/resources/704fc67d61/search?find=ORA-06512">ORA-06512</a> : at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1263

 <a  target="_blank" style="cursor:pointer !important" title="Link to Customer Connect Forum for this error code. It will open in a new browser tab." href="https://cloudcustomerconnect.oracle.com/resources/704fc67d61/search?find=ORA-06512">ORA-06512</a> : at "C##CLOUD$SERVICE.DBMS_CLOUD", line 4825

 <a  target="_blank" style="cursor:pointer !important" title="Link to Customer Connect Forum for this error code. It will open in a new browser tab." href="https://cloudcustomerconnect.oracle.com/resources/704fc67d61/search?find=ORA-06512">ORA-06512</a> : at line 1

Fault Details :

<err:serviceInvocationError xmlns:err="http://xmlns.oracle.com/cloud/generic/service/fault">

   <err:type>Stored procedure invocation error</err:type>

   <err:title>Error while trying to prepare and execute the DBMS_CLOUD.COPY_DATA API.</err:title>

   <err:detail>An error occurred while preparing and exec:Application Error

3)      You may get the below error when listing buckets in the ATP end point, this could be because of the policies / not mentioning the right compartment in the connection page.

















 























Comments