Explore Oracle Stuff

Thursday, July 9, 2026

How to install OCI CLI and instant client tool in Linux VM

Problem statement: In an Oracle Autonomous Data Warehouse environment, we do not have access to the underlying compute nodes. However, many organizations run custom shell-script jobs that must access OCI resources through the OCI CLI and make database calls. Even though cloud shell is pre-configured with instant client and OCI CLI tool but it is dedicated for oci user and we can not access from outside oci environment.To support these requirements, we need a Linux VM with Oracle Instant Client and the OCI CLI installed.

This article will guide you step by step how to install above mentioned tools.

Environment Setup: I have installed Oracle Linux Server release 8.10 using image Oracle-Linux-8.10-2026.06.15-0 available in OCI console. During installation I have chosen to use public IP so that I can connect from my laptop. In real world we may choose to use private IP only for security reasons. Please see the link below how to connect a Linux instance

Connecting to a Linux Instance

I just want to make you aware that old version of putty might throw below error which says there is a mismatch between server and client with respect to authentication methods.To resolve this error use putty version 0.84 and higher.

1. Check whether Oracle Instant Client packages are available in the currently enabled RPM repositories

dnf search instantclient

Last metadata expiration check: 23:51:11 ago on Sat 27 Jun 2026 03:28:18 PM GMT.

================================================================================ Name Matched: instantclient ================================================================================

oracle-instantclient-release-23ai-el8.src : Oracle Software yum repository configuration

oracle-instantclient-release-23ai-el8.x86_64 : Oracle Software yum repository configuration

oracle-instantclient-release-26ai-el8.x86_64 : Oracle Software yum repository configuration

oracle-instantclient-release-el8.src : Oracle Instant Client yum repository configuration

oracle-instantclient-release-el8.x86_64 : Oracle Instant Client yum repository configuration

2. Choose your choice of instant client and install

dnf info oracle-instantclient-release-26ai-el8.x86_64

Last metadata expiration check: 23:52:31 ago on Sat 27 Jun 2026 03:28:18 PM GMT.

Available Packages

Name : oracle-instantclient-release-26ai-el8

Version : 1.0

Release : 6.el8

Architecture : x86_64

Size : 14 k

Source : oracle-common-release-1.0-6.el8.src.rpm

Repository : ol8_baseos_latest

Summary : Oracle Software yum repository configuration

License : GPLv2

Description : This package contains the Oracle Software yum repository configuration.

dnf install oracle-instantclient-release-26ai-el8

Last metadata expiration check: 23:53:16 ago on Sat 27 Jun 2026 03:28:18 PM GMT.

Dependencies resolved.

=============================================================================================================================================================================================

Package Architecture Version Repository Size

=============================================================================================================================================================================================

Installing:

oracle-instantclient-release-26ai-el8 x86_64 1.0-6.el8 ol8_baseos_latest 14 k

Transaction Summary

=============================================================================================================================================================================================

Install 1 Package

Total download size: 14 k

Installed size: 18 k

Is this ok [y/N]: y

Downloading Packages:

oracle-instantclient-release-26ai-el8-1.0-6.el8.x86_64.rpm 124 kB/s | 14 kB 00:00

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total 121 kB/s | 14 kB 00:00

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : oracle-instantclient-release-26ai-el8-1.0-6.el8.x86_64 1/1

Verifying : oracle-instantclient-release-26ai-el8-1.0-6.el8.x86_64 1/1

Installed:

oracle-instantclient-release-26ai-el8-1.0-6.el8.x86_64

Complete!

3. Find SQL*Plus package and install

dnf search instantclient

Oracle Instant Client 26 for Oracle Linux 8 - License Included (x86_64) 384 kB/s | 24 kB 00:00

================================================================================ Name Matched: instantclient ================================================================================

oracle-instantclient-basic.x86_64 : Oracle Instant Client Basic package

oracle-instantclient-basiclite.x86_64 : Oracle Instant Client Light package

oracle-instantclient-devel.x86_64 : Development header files for Oracle Instant Client.

oracle-instantclient-jdbc.x86_64 : Supplemental JDBC features for the Oracle Instant Client

oracle-instantclient-odbc.x86_64 : Oracle Instant Client ODBC

oracle-instantclient-release-23ai-el8.src : Oracle Software yum repository configuration

oracle-instantclient-release-23ai-el8.x86_64 : Oracle Software yum repository configuration

oracle-instantclient-release-26ai-el8.x86_64 : Oracle Software yum repository configuration

oracle-instantclient-release-el8.src : Oracle Instant Client yum repository configuration

oracle-instantclient-release-el8.x86_64 : Oracle Instant Client yum repository configuration

oracle-instantclient-sqlplus.x86_64 : Oracle Instant Client SQL*Plus package

oracle-instantclient-tools.x86_64 : Tools for Oracle Instant Client

dnf install -y oracle-instantclient-sqlplus.x86_64

Last metadata expiration check: 0:01:49 ago on Sun 28 Jun 2026 03:23:46 PM GMT.

Dependencies resolved.

=============================================================================================================================================================================================

Package Architecture Version Repository Size

=============================================================================================================================================================================================

Installing:

oracle-instantclient-sqlplus x86_64 23.26.2.0.0-2.el8 ol8_oracle_instantclient26 4.7 M

Installing dependencies:

oracle-instantclient-basic x86_64 23.26.2.0.0-2.el8 ol8_oracle_instantclient26 84 M

Transaction Summary

=============================================================================================================================================================================================

Install 2 Packages

Total download size: 89 M

Installed size: 362 M

Downloading Packages:

(1/2): oracle-instantclient-sqlplus-23.26.2.0.0-2.el8.x86_64.rpm 17 MB/s | 4.7 MB 00:00

(2/2): oracle-instantclient-basic-23.26.2.0.0-2.el8.x86_64.rpm 56 MB/s | 84 MB 00:01

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total 59 MB/s | 89 MB 00:01

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : oracle-instantclient-basic-23.26.2.0.0-2.el8.x86_64 1/2

Running scriptlet: oracle-instantclient-basic-23.26.2.0.0-2.el8.x86_64 1/2

Installing : oracle-instantclient-sqlplus-23.26.2.0.0-2.el8.x86_64 2/2

Running scriptlet: oracle-instantclient-sqlplus-23.26.2.0.0-2.el8.x86_64 2/2

Verifying : oracle-instantclient-basic-23.26.2.0.0-2.el8.x86_64 1/2

Verifying : oracle-instantclient-sqlplus-23.26.2.0.0-2.el8.x86_64 2/2

Installed:

oracle-instantclient-basic-23.26.2.0.0-2.el8.x86_64 oracle-instantclient-sqlplus-23.26.2.0.0-2.el8.x86_64

Complete!

4. Create oracle user and necessary groups(oinstall,dba)

groupadd oinstall

groupadd dba

useradd -m -g oinstall -G dba oracle

passwd oracle—to set password

5. Check whether sqlplus is accessible from oracle user

su – oracle

which sqlplus

/usr/bin/sqlplus

6. Install packages required for OCI cli

dnf install python36-oci-cli

Following packages were installed part of this installation

INFO =============================================================================================================================================================================================

Package Architecture Version Repository Size

=============================================================================================================================================================================================

Installing:

python36-oci-cli noarch 3.83.0-1.0.1.el8 ol8_oci_included 6.2 M

Installing dependencies:

python3-arrow noarch 1.1.1-1.el8 ol8_oci_included 119 k

python3-importlib-metadata noarch 1.7.0-1.el8 ol8_addons 47 k

python3-jmespath noarch 0.10.0-1.el8 ol8_oci_included 48 k

python3-prompt-toolkit noarch 3.0.29-1.0.2.el8 ol8_oci_included 669 k

python3-terminaltables noarch 3.1.10-1.0.1.el8 ol8_oci_included 29 k

python3-typing-extensions noarch 3.7.4.2-1.el8 ol8_oci_included 47 k

python3-wcwidth noarch 0.2.5-3.el8 ol8_oci_included 48 k

python3-zipp noarch 0.5.1-3.el8 ol8_addons 15 k

Transaction Summary

=============================================================================================================================================================================================

Install 9 Packages

Check the version

oci -version

3.83.0

7. Configure OCI CLI tool

Use below command and follow the instructions

oci setup config

This command provides a walkthrough of creating a valid CLI config file.

The following links explain where to find the information required by this

script:

User API Signing Key, OCID and Tenancy OCID:

https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other

Region:

https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm

General config documentation:

https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm

Enter a location for your config [/root/.oci/config]:

Config file: /root/.oci/config already exists. Do you want add a profile here? (If no, you will be prompted to overwrite the file) [Y/n]: n

File: /root/.oci/config already exists. Do you want to overwrite (Removes existing profiles!!!)? [y/N]: y

Enter a user OCID: ocid1.user.oc1.XXXXXXXXXXXXXXXXXX

Enter a tenancy OCID: ocid1.tenancy.oc1..XXXXXXXXXXXX

Enter a region by index or name(e.g.

1: af-casablanca-1, 2: af-johannesburg-1, 3: ap-batam-1, 4: ap-chiyoda-1, 5: ap-chuncheon-1,

6: ap-chuncheon-2, 7: ap-dcc-canberra-1, 8: ap-dcc-gazipur-1, 9: ap-delhi-1, 10: ap-hyderabad-1,

11: ap-ibaraki-1, 12: ap-kulai-2, 13: ap-melbourne-1, 14: ap-mumbai-1, 15: ap-osaka-1,

16: ap-seoul-1, 17: ap-seoul-2, 18: ap-singapore-1, 19: ap-singapore-2, 20: ap-suwon-1,

21: ap-sydney-1, 22: ap-tokyo-1, 23: ca-montreal-1, 24: ca-toronto-1, 25: eu-amsterdam-1,

26: eu-budapest-1, 27: eu-crissier-1, 28: eu-dcc-dublin-1, 29: eu-dcc-dublin-2, 30: eu-dcc-milan-1,

31: eu-dcc-milan-2, 32: eu-dcc-rating-1, 33: eu-dcc-rating-2, 34: eu-dcc-zurich-1, 35: eu-frankfurt-1,

36: eu-frankfurt-2, 37: eu-jovanovac-1, 38: eu-madrid-1, 39: eu-madrid-2, 40: eu-madrid-3,

41: eu-marseille-1, 42: eu-milan-1, 43: eu-paris-1, 44: eu-stockholm-1, 45: eu-turin-1,

46: eu-zurich-1, 47: il-jerusalem-1, 48: me-abudhabi-1, 49: me-abudhabi-2, 50: me-abudhabi-3,

51: me-abudhabi-4, 52: me-alain-1, 53: me-dcc-doha-1, 54: me-dcc-muscat-1, 55: me-dubai-1,

56: me-ibri-1, 57: me-jeddah-1, 58: me-riyadh-1, 59: mx-monterrey-1, 60: mx-queretaro-1,

61: sa-bogota-1, 62: sa-riodejaneiro-1, 63: sa-santiago-1, 64: sa-saopaulo-1, 65: sa-valparaiso-1,

66: sa-vinhedo-1, 67: uk-cardiff-1, 68: uk-gov-cardiff-1, 69: uk-gov-london-1, 70: uk-london-1,

71: us-ashburn-1, 72: us-ashburn-2, 73: us-chicago-1, 74: us-gov-ashburn-1, 75: us-gov-chicago-1,

76: us-gov-phoenix-1, 77: us-langley-1, 78: us-luke-1, 79: us-newark-1, 80: us-phoenix-1,

81: us-saltlake-2, 82: us-sanjose-1, 83: us-somerset-1, 84: us-thames-1): 80

Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: y

Enter a directory for your keys to be created [/root/.oci]:

Enter a name for your key [oci_api_key]:

File /root/.oci/oci_api_key_public.pem already exists, do you want to overwrite? [y/N]: y

Public key written to: /root/.oci/oci_api_key_public.pem

Enter a passphrase for your private key ("N/A" for no passphrase):

Repeat for confirmation:

File /root/.oci/oci_api_key.pem already exists, do you want to overwrite? [y/N]: y

Private key written to: /root/.oci/oci_api_key.pem

Fingerprint: XXXXXXXXXXXXXXXXXXXXXXX

Config written to /root/.oci/config

If you haven't already uploaded your API Signing public key through the

console, follow the instructions on the page linked below in the section

'How to upload the public key':

https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2

Validate all the enties of config file /root/.oci/config

8. Upload API signing key in the console

From OCI console click on profile icon visible on top right corner and from there select Tokens and Keys tab

Click on the button Add API Key and paste contents of /root/.oci/oci_api_key_public.pem

In the next window it will generate configuration file preview.Make sure it matches with your config file stored in the server

9. Testing the OCI CLI configuration from root user

Use below command to test the configuration

oci iam user list

{

"data": [

{

"capabilities": {

"can-use-api-keys": true,

"can-use-auth-tokens": true,

"can-use-console-password": true,

"can-use-customer-secret-keys": true,

"can-use-db-credentials": true,

"can-use-o-auth2-client-credentials": true,

"can-use-smtp-credentials": true

"compartment-id": "ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXXXX",

"db-user-name": null,

"defined-tags": {},

"description": null,

"email": "debdhritiman@*****.com",

"email-verified": true,

"external-identifier": "7f322816bfc24b2094ac507bef60e069",

"freeform-tags": {},

"id": "ocid1.user.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXX",

"identity-provider-id": null,

"inactive-status": null,

"is-mfa-activated": true,

"last-successful-login-time": "2026-07-09T15:27:01.421000+00:00",

"lifecycle-state": "ACTIVE",

"name": "debdhritiman@yahoo.com",

"previous-successful-login-time": null,

"time-created": "2026-06-09T16:15:23.478000+00:00"

}

]

}

10. Download credential using oci command

Set below variable after login into linux vm

ADB_OCID=ocid1.autonomousdatabase.oc1.phx.XXXXXXXXXXXXXXX

FILE_NAME=wallet.zip

WALLET_PW=XXXXXX

oci db autonomous-database generate-wallet --autonomous-database-id $ADB_OCID --file $FILE_NAME --password $WALLET_PW

unzip this wallet.zip file which will have wallet keys and tnsnames.ora file.

Update sqlnet.ora to point wallet location appropriate to your wallet location.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle")))

SSL_SERVER_DN_MATCH=yes

and connect like below

export TNS_ADMIN=/home/oracle

sqlplus admin/XXXX@cdborcl_high

Hope you have learned something useful

Sunday, June 21, 2026

End-to-End CSV Loading into Oracle Autonomous Database with OCI Object Storage

Problem statement: In many real-world ETL workflows, flat files are generated outside the database and need to be loaded into Autonomous Data Warehouse. This article demonstrates how to upload a CSV file to OCI Object Storage and load it into Oracle Autonomous Database using DBMS_CLOUD.COPY_DATA.Entire process was done using CLI so that we can automate the process.

Environment:

Source: Oracle Linux 8 VM running in OCI

File: Sample csv file ecommerce_orders_dataset.csv downloaded using this link from Kaggle

E-commerce Orders Dataset 2026 | SCRA

Target database: Oracle autonomous database

Pre-requisites:

OCI CLI installed and configured

Required IAM permissions for Object Storage and auth token creation

Autonomous Database available

SQL access to Autonomous Database

CSV file available on the source VM

DBMS_CLOUD package available in ADB

Solution: Following are the high level steps for the problem statement we have defined

· Use OCI CLI tool to create a bucket under storage service

· Upload csv into the bucket we have created in previous step

· Create an authorization token and save the password

· Create a credential inside the autonomous database so that link can be established between object storage and database

· Create table structure matching data type in CSV

· Use DBMS_CLOUD.COPY_DATA procedure to load the data from csv to the table created in above steps

Now we will focus every steps in detail

1. Use OCI CLI tool to create a bucket under storage service

Now here I will show you a very easy way to use oci cli tool which will help you a lot later if you want to explore various options without relying on google search everytime.Use below command to run oci cli in interactive mode.Here you can see I have typed oci os(Object storage) and it is giving us next set of command options.Lets select bucket using down arrow key

(Note:if the image is not clear click on the image)

oci -i

Select create

Select compartment-id

I don’t know my compartment-id so I have used same approach to find my compartment-id using below command.We know compartment falls under IAM

So finally my command becomes

oci os bucket create --compartment-id ocid1.compartment.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXX --name mybucket

{

"data": {

"approximate-count": null,

"approximate-size": null,

"auto-tiering": null,

"compartment-id": "ocid1.compartment.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",

"created-by": "ocid1.user.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",

"defined-tags": {

"Oracle-Tags": {

"CreatedBy": "default/debdhritiman@yahoo.com",

"CreatedOn": "2026-06-20T14:22:40.473Z"

}

"etag": "33dbf38d-a582-453a-8162-253aee4f2a0f",

"freeform-tags": {},

"id": "ocid1.bucket.oc1.phx.aaaaaaaayetp5fv4l7smfyaq4w6zglglau52aqp52kdy4ig7lcvtsqxhjjwq",

"is-read-only": false,

"kms-key-id": null,

"metadata": {},

"name": "mybucket",

"namespace": "axbybsdhxsix",

"object-events-enabled": false,

"object-lifecycle-policy-etag": null,

"public-access-type": "NoPublicAccess",

"replication-enabled": false,

"storage-tier": "Standard",

"time-created": "2026-06-20T14:22:40.483000+00:00",

"versioning": "Disabled"

"etag": "33dbf38d-a582-453a-8162-253aee4f2a0f"

}

1. Upload csv file into the bucket

Using same approach I have created the command

oci os object put -bn mybucket --file /home/debdhritim/ecommerce_orders_dataset.csv

Uploading object [####################################] 100%

{

"etag": "a99c103c-50d6-4ab3-ac2b-c224aa4b86fd",

"last-modified": "Sat, 20 Jun 2026 14:31:58 GMT",

"opc-content-md5": "x7DNfgxWSEqWWNd9c814AA=="

}

We can confirm using below command

oci os object list -bn mybucket -ns axbybsdhxsix

{

"data": [

{

"archival-state": null,

"etag": "a99c103c-50d6-4ab3-ac2b-c224aa4b86fd",

"md5": "x7DNfgxWSEqWWNd9c814AA==",

"name": "ecommerce_orders_dataset.csv",

"size": 7730710,

"storage-tier": "Standard",

"time-created": "2026-06-20T14:31:58.105000+00:00",

"time-modified": "2026-06-20T14:31:58.105000+00:00"

}

"prefixes": []

}

2. Create an authorization token and save the password

We need ocid of the user using which we can create auth token. Hence use below query to get ocid of the user

oci iam user list | jq '.data[] | select(."name" =="debdhritiman@yahoo.com")'

{

"capabilities": {

"can-use-api-keys": true,

"can-use-auth-tokens": true,

"can-use-console-password": true,

"can-use-customer-secret-keys": true,

"can-use-db-credentials": true,

"can-use-o-auth2-client-credentials": true,

"can-use-smtp-credentials": true

"compartment-id": "ocid1.tenancy.XXXXXXXXXXXXXXXX ",

"db-user-name": null,

"defined-tags": {},

"description": null,

"email": "debdhritiman@yahoo.com",

"email-verified": true,

"external-identifier": "7f322816bfc24b2094ac507bef60e069",

"freeform-tags": {},

"id": "ocid1.user.XXXXXXXXXXXXXXXXXXX ",

"identity-provider-id": null,

"inactive-status": null,

"is-mfa-activated": true,

"last-successful-login-time": "2026-06-20T13:53:05.450000+00:00",

"lifecycle-state": "ACTIVE",

"name": "debdhritiman@yahoo.com",

"previous-successful-login-time": null,

"time-created": "2026-06-09T16:15:23.478000+00:00"

}

oci iam auth-token create --description "Auth token for database to connect object storage" --user-id ocid1.user.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

{

"data": {

"description": "Auth token for database to connect object storage",

"id": "ocid1.credential.oc1..aaaaaaaaokz6lhkcqrrtwwg374k2tkq54s7ktiog7532zmhpqctoebfdt7da",

"inactive-status": null,

"lifecycle-state": "ACTIVE",

"time-created": "2026-06-20T15:02:55.703000+00:00",

"time-expires": null,

"token": "Gzk}EGED-x]x3TKCHkvD", --This is the token we should save as this is not stored anywhere

"user-id": "ocid1.user.oc1.. XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX "

"etag": "68d2ed611c7c43bd86bf4d93ee360874"

}

3. Create a credential inside the autonomous database so that link can be established between object storage and database

Connect to your ADB and use below PL/SQL to create the credential

--In case you want to drop and recreate

BEGIN

DBMS_CLOUD.DROP_CREDENTIAL( credential_name => 'OBJECT_STORE_CRED' );

END;

BEGIN

DBMS_CLOUD.CREATE_CREDENTIAL(

credential_name => 'OBJECT_STORE_CRED',

username => 'debdhritiman@yahoo.com',

password => 'Gzk}EGED-x]x3TKCHkvD'

);

END;

Validate in data dictionary

SQL> SELECT credential_name, username, enabled, comments FROM user_credentials WHERE credential_name = 'OBJECT_STORE_CRED';

CREDENTIAL_NAME USERNAME ENABLED COMMENTS

____________________ _________________________ __________ __________________________________________________________

OBJECT_STORE_CRED debdhritiman@yahoo.com TRUE {"comments":"Created via DBMS_CLOUD.create_credential"}

Validate connectivity using below query

SELECT object_name, bytes FROM DBMS_CLOUD.LIST_OBJECTS( credential_name => 'OBJECT_STORE_CRED', location_uri => 'https://objectstorage.us-phoenix-oraclecloud.com/n/axbybsdhxsix/b/mybucket/');

4. Create table structure matching data type in CSV

CREATE TABLE ecommerce_orders (

order_id number PRIMARY KEY,

customer_id VARCHAR(20),

order_date DATE,

year number,

month number,

day number,

weekday VARCHAR(20),

quarter number,

age number,

gender VARCHAR(10),

country VARCHAR(50),

city VARCHAR(50),

customer_type VARCHAR(20),

product_id VARCHAR(20),

category VARCHAR(50),

subcategory VARCHAR(50),

brand VARCHAR(50),

price number(10,2),

quantity number,

discount number(5,2),

discount_amount number(10,2),

coupon_used BOOLEAN,

shipping_cost number(10,2),

tax number(10,2),

total_amount number(12,2),

payment_method VARCHAR(30),

device_type VARCHAR(20),

channel VARCHAR(30),

member_status VARCHAR(10),

delivery_type VARCHAR(20),

warehouse_region VARCHAR(20),

delivery_days number,

order_status VARCHAR(20),

is_returned BOOLEAN,

rating number(3,1),

Customer_Value number(8,2),

Profit_Margin number(5,2),

Profit_Amount number(8,2),

season VARCHAR(20),

holiday_season BOOLEAN,

hv_order BOOLEAN

);

5. Use DBMS_CLOUD.COPY_DATA procedure to load the data from csv to the table created in above steps

Executed below command

begin

DBMS_CLOUD.COPY_DATA(

table_name =>'ECOMMERCE_ORDERS',

credential_name =>'OBJECT_STORE_CRED',

file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/p/FC204TdtecHLSypF4cbrxOWa_dhQIqI86skHvIwVAHbLHnhv3alBMEMK4rr1cWU5/n/axbybsdhxsix/b/mybucket/o/ecommerce_orders_dataset.csv',

format => json_object('type' value 'csv', 'skipheaders' value '1','dateformat' value 'yyyy-mm-dd')

);

end;

It failed with some date format error

Use below query to find the log details

SQL> select start_time,update_time,status,table_name,logfile_table,badfile_table from user_Load_operations;

START_TIME UPDATE_TIME STATUS TABLE_NAME LOGFILE_TABLE BADFILE_TABLE

______________________________________ ______________________________________ _________ ___________________ ________________ ________________

20-JUN-26 04.17.45.610773000 PM GMT 20-JUN-26 04.17.45.965481000 PM GMT FAILED ECOMMERCE_ORDERS COPY$15_LOG COPY$15_BAD

SQL> select * from COPY$15_LOG;

RECORD

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LOG file opened at 06/21/26 15:02:42

Total Number of Files=1

Data File: https://objectstorage.us-phoenix-1.oraclecloud.com/p/FC204TdtecHLSypF4cbrxOWa_dhQIqI86skHvIwVAHbLHnhv3alBMEMK4rr1cWU5/n/axbybsdhxsix/b/mybucket/o/ecommerce_orders_dataset.csv

Log File: COPY$15_3030025.log

LOG file opened at 06/21/26 15:02:42

Bad File: COPY$15_1483975.bad

Field Definitions for table COPY$183PPH0U8LQXAKX45WOG

Record format DELIMITED BY NEWLINE

Data in file has same endianness as the platform

Rows with all null fields are accepted

Fields in Data Source:

ORDER_ID CHAR (255)

Terminated by ","

Enclosed by """ and """

CUSTOMER_ID CHAR (255)

Terminated by ","

Enclosed by """ and """

ORDER_DATE CHAR (255)

Date datatype DATE, date mask yyyy-mm-dd

Terminated by ","

Enclosed by """ and """

YEAR CHAR (255)

Terminated by ","

Enclosed by """ and """

MONTH CHAR (255)

Terminated by ","

Enclosed by """ and """

DAY CHAR (255)

Terminated by ","

Enclosed by """ and """

WEEKDAY CHAR (255)

Terminated by ","

Enclosed by """ and """

QUARTER CHAR (255)

Terminated by ","

Enclosed by """ and """

AGE CHAR (255)

Terminated by ","

Enclosed by """ and """

GENDER CHAR (255)

Terminated by ","

Enclosed by """ and """

COUNTRY CHAR (255)

Terminated by ","

Enclosed by """ and """

CITY CHAR (255)

Terminated by ","

Enclosed by """ and """

CUSTOMER_TYPE CHAR (255)

Terminated by ","

Enclosed by """ and """

PRODUCT_ID CHAR (255)

Terminated by ","

Enclosed by """ and """

CATEGORY CHAR (255)

Terminated by ","

Enclosed by """ and """

SUBCATEGORY CHAR (255)

Terminated by ","

Enclosed by """ and """

BRAND CHAR (255)

Terminated by ","

Enclosed by """ and """

PRICE CHAR (255)

Terminated by ","

Enclosed by """ and """

QUANTITY CHAR (255)

Terminated by ","

Enclosed by """ and """

DISCOUNT CHAR (255)

Terminated by ","

Enclosed by """ and """

DISCOUNT_AMOUNT CHAR (255)

Terminated by ","

Enclosed by """ and """

COUPON_USED CHAR (255)

Terminated by ","

Enclosed by """ and """

SHIPPING_COST CHAR (255)

Terminated by ","

Enclosed by """ and """

TAX CHAR (255)

Terminated by ","

Enclosed by """ and """

TOTAL_AMOUNT CHAR (255)

Terminated by ","

Enclosed by """ and """

PAYMENT_METHOD CHAR (255)

Terminated by ","

Enclosed by """ and """

DEVICE_TYPE CHAR (255)

Terminated by ","

Enclosed by """ and """

CHANNEL CHAR (255)

Terminated by ","

Enclosed by """ and """

MEMBER_STATUS CHAR (255)

Terminated by ","

Enclosed by """ and """

DELIVERY_TYPE CHAR (255)

Terminated by ","

Enclosed by """ and """

WAREHOUSE_REGION CHAR (255)

Terminated by ","

Enclosed by """ and """

DELIVERY_DAYS CHAR (255)

Terminated by ","

Enclosed by """ and """

ORDER_STATUS CHAR (255)

Terminated by ","

Enclosed by """ and """

IS_RETURNED CHAR (255)

Terminated by ","

Enclosed by """ and """

RATING CHAR (255)

Terminated by ","

Enclosed by """ and """

CUSTOMER_VALUE CHAR (255)

Terminated by ","

Enclosed by """ and """

PROFIT_MARGIN CHAR (255)

Terminated by ","

Enclosed by """ and """

PROFIT_AMOUNT CHAR (255)

Terminated by ","

Enclosed by """ and """

SEASON CHAR (255)

Terminated by ","

Enclosed by """ and """

HOLIDAY_SEASON CHAR (255)

Terminated by ","

Enclosed by """ and """

HV_ORDER CHAR (255)

Terminated by ","

Enclosed by """ and """

error processing column ORDER_DATE in row 2 for datafile https://objectstorage.us-phoenix-1.oraclecloud.com/p/FC204TdtecHLSypF4cbrxOWa_dhQIqI86skHvIwVAHbLHnhv3alBMEMK4rr1cWU5/n/axbybsdhxsix/b/mybucket

/o/ecommerce_orders_dataset.csv

ORA-01830: Date format picture ends before converting entire input string.

149 rows selected.

The reason for this failure was I used dateformat as 'yyyy-mm-dd' in DBMS_CLOUD.COPY_DATA procedure and my csv has mm/dd/yyyy format.This issue was resolved after I changed the data format as mm/dd/yyyy

format => json_object('type' value 'csv', 'skipheaders' value '1','dateformat' value 'yyyy-mm-dd')

Now I can see 30,000 rows have been inserted successfully.

Now the manual process is validated, the same steps can be automated using shell scripting, OCI CLI, and scheduled database jobs or external orchestration tools.

Hope you have learned something useful.

Reference:- So you have your CSV, TSV and JSON data lying in your Oracle Cloud Object Store. How do you get it over into your Autonomous Database? | autonomous-ai-database

Explore Oracle Stuff

Thursday, July 9, 2026

How to install OCI CLI and instant client tool in Linux VM

Sunday, June 21, 2026

End-to-End CSV Loading into Oracle Autonomous Database with OCI Object Storage

How to install OCI CLI and instant client tool in Linux VM

Report Abuse

Labels