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.
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.
·
