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.
- Wallet and password
- Database Service Username and its password
- Object Storage Region
- Object Storage Tenancy OCID
- Object Storage Compartment OCID
- Object Storage User OCID
- Private Key
- 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.
I have considered normal user , instead of federated.. you can use any user having right authorizations.
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.
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
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
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
Post a Comment