Oracle Database Commands and Practical Examples: OCI - Load CSV object data on OCI autonomous database

Loading a CSV file into a Table in the Autonomous Database using DBMS_CLOUD.COPY_DATA

Login on premises database and connect soe schema and collect 1000 rows objects in csv file.

D:\test\WINDOWS.X64_193000_db_home\bin>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 17:01:14 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SQL> alter session set container=PDB1;

Session altered.

SQL> connect soe/soe@pdb1

Connected.

SQL> select count(*),object_type,status from dba_objects where owner='SOE' group by status,object_type;

COUNT(*) OBJECT_TYPE STATUS

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

7 SEQUENCE VALID

1 FUNCTION VALID

1 PACKAGE BODY INVALID

1 PROCEDURE VALID

2 VIEW VALID

16 TABLE VALID

33 INDEX VALID

1 PACKAGE VALID

8 rows selected.

Spool output of sales.csv file which is used further for uploading data into OCI bucket

SQL> SET MARKUP CSV ON DELIMITER , QUOTE ON

SQL> SPOOL C:\Users\varun\OneDrive\Desktop\oci\sales.csv

SQL> SELECT ORDER_ID, TO_CHAR(ORDER_DATE,'DD-MM-YYYY HH24:MI:SS')

2 ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL FROM ORDERS ORDER BY ORDER_ID

3 FETCH FIRST 1000 ROWS ONLY;

"ORDER_ID","ORDER_DATE","ORDER_MODE","CUSTOMER_ID","ORDER_STATUS","ORDER_TOTAL"

10001,"01-01-2010 01:34:30","direct",12086,6,9962

10002,"01-01-2010 01:38:19",,12968,6,5796

10003,"01-01-2010 01:42:10","online",16404,7,4845

10004,"01-01-2010 01:46:05","online",10911,6,7274

10005,"01-01-2010 01:49:19","direct",15148,7,3987

10006,"01-01-2010 01:53:00",,7119,6,5675

10007,"01-01-2010 01:56:46","online",4694,6,4965

10008,"01-01-2010 02:00:19",,10384,5,6173

10009,"01-01-2010 02:03:53","online",7525,4,4090

10010,"01-01-2010 02:07:26","direct",10580,4,4839

10011,"01-01-2010 02:10:52","online",13234,5,6393

10012,"01-01-2010 02:14:32","direct",2464,5,6460

10013,"01-01-2010 02:17:52","online",2116,5,5822

10014,"01-01-2010 02:21:17",,19616,6,5098

10015,"01-01-2010 02:24:31","online",11045,7,5552

10016,"01-01-2010 02:28:06","direct",10282,6,7047

10017,"01-01-2010 02:31:36",,13133,5,8758

10018,"01-01-2010 02:35:27",,1886,3,7192

10019,"01-01-2010 02:39:32","direct",1881,6,4174

10020,"01-01-2010 02:43:07","online",15767,4,5374

10021,"01-01-2010 02:47:04","direct",5743,5,5176

10022,"01-01-2010 02:51:14","online",6437,4,4639

10023,"01-01-2010 02:55:08","direct",4923,7,8940

10024,"01-01-2010 02:58:35","online",16527,6,7216

10025,"01-01-2010 03:02:21",,9240,7,7397

10026,"01-01-2010 03:05:35",,7920,4,6609

10027,"01-01-2010 03:08:35","direct",17202,6,6295

10028,"01-01-2010 03:12:28",,16942,5,7891

10029,"01-01-2010 03:15:38","direct",10662,6,7446

10030,"01-01-2010 03:19:08","direct",15902,5,6823

10031,"01-01-2010 03:23:24","online",4729,5,6328

Upload the data into bucket

Connect to Automatic database and create table sales on soe schema

 C:\Users\varun>sqlplus soe/Abc#123456789@orcladb_mediumSQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 17:26:02 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 07 2021 16:18:11 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> sho user
USER is "SOE"
SQL> CREATE TABLE SOE.SALES (
  2  ORDER_ID NUMBER(12),
  3  ORDER_DATE DATE,
  4  ORDER_MODE VARCHAR2(8),
  5  CUSTOMER_ID NUMBER(12),
  6  ORDER_STATUS NUMBER(2),
  7  ORDER_TOTAL NUMBER(8,2)
  8  );
Table created.
SQL>