I was going to refer someone on StackOverflow to my post on the LOAD command in SQLcl, but then I realized I hadn’t written one yet. Oops. So here’s that post.

One of the new (that is, a command in SQLcl that is NOT in SQL*Plus) commands is ‘LOAD.’

You can find all the new commands highlighted if you run ‘help’

Guess what this does…

No need to guess what LOAD does, just consult the help.

SQL> help load
LOAD
-----
 
Loads a comma separated value (csv) file into a table.
The first row of the file must be a header row.  The columns in the header row must match the columns defined on the table.
 
The columns must be delimited by a comma and may optionally be enclosed in double quotes.
Lines can be terminated with standard line terminators for windows, unix or mac.
File must be encoded UTF8.
 
The load is processed with 50 rows per batch.
If AUTOCOMMIT is set in SQLCL, a commit is done every 10 batches.
The load is terminated if more than 50 errors are found.

A quick demo

Let’s SPOOL some CSV to a file, then use the LOAD command to put that data into a new table.

SQL> SET sqlformat csv
SQL> cd /Users/thatjeffsmith
SQL> spool objects.csv
SQL> SELECT * FROM all_objects fetch FIRST 100 ROWS ONLY;
"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPLICATED","SHARDED","CREATED_APPID","CREATED_VSNID","MODIFIED_APPID","MODIFIED_VSNID"
"SYS","I_FILE#_BLOCK#","",9,9,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_OBJ3","",38,38,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_TS1","",45,45,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_CON1","",51,51,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","IND$","",19,2,"TABLE",07-FEB-18,21-NOV-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","CDEF$","",31,29,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","C_TS#","",6,6,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,,
"SYS","I_CCOL2","",58,58,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_PROXY_DATA$","",24,24,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_CDEF4","",56,56,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_TAB1","",33,33,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","CLU$","",5,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_PROXY_ROLE_DATA$_1","",26,26,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_OBJ1","",36,36,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","UNDO$","",15,15,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_UNDO2","",35,35,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_TS#","",7,7,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_FILE1","",43,43,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_COL2","",49,49,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_OBJ#","",3,3,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","C_OBJ#","",2,2,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,,
"SYS","I_CDEF3","",55,55,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","C_COBJ#","",29,29,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,,
"SYS","CCOL$","",32,29,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_OBJ5","",40,40,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","PROXY_ROLE_DATA$","",25,25,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_CDEF1","",53,53,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","C_USER#","",10,10,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,,
"SYS","C_FILE#_BLOCK#","",8,8,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,,
"SYS","FET$","",12,6,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_CON2","",52,52,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_OBJ4","",39,39,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","CON$","",28,28,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_CDEF2","",54,54,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","ICOL$","",20,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_COL3","",50,50,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_CCOL1","",57,57,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","COL$","",21,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_ICOL1","",42,42,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","UET$","",13,8,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","PROXY_DATA$","",23,23,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","USER$","",22,10,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_PROXY_ROLE_DATA$_2","",27,27,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_OBJ2","",37,37,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","TAB$","",4,2,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_COBJ#","",30,30,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_USER#","",11,11,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","FILE$","",17,17,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","OBJ$","",18,18,"TABLE",07-FEB-18,15-OCT-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","TS$","",16,6,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_UNDO1","",34,34,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","BOOTSTRAP$","",59,59,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_COL1","",48,48,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_FILE2","",44,44,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_IND1","",41,41,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_USER2","",47,47,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_USER1","",46,46,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","SEG$","",14,8,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:25","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","OBJERROR$","",60,60,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:26","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","OBJAUTH$","",61,61,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:26","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_OBJAUTH1","",62,62,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:26","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_OBJAUTH2","",63,63,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","C_OBJ#_INTCOL#","",64,64,"CLUSTER",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,,
"SYS","I_OBJ#_INTCOL#","",65,65,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","HISTGRM$","",66,64,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_H_OBJ#_COL#","",67,67,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","HIST_HEAD$","",68,68,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_HH_OBJ#_COL#","",69,69,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_HH_OBJ#_INTCOL#","",70,70,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","FIXED_OBJ$","",71,71,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_FIXED_OBJ$_OBJ#","",72,72,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:27","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","TAB_STATS$","",73,73,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_TAB_STATS$_OBJ#","",74,74,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","IND_STATS$","",75,75,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_IND_STATS$_OBJ#","",76,76,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","OBJECT_USAGE","",77,77,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_STATS_OBJ#","",78,78,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","PARTOBJ$","",79,79,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_PARTOBJ$","",80,80,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","DEFERRED_STG$","",81,81,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_DEFERRED_STG1","",82,82,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","DEPENDENCY$","",83,83,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:28","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","ACCESS$","",84,84,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_DEPENDENCY1","",85,85,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_DEPENDENCY2","",86,86,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_ACCESS1","",87,87,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","USERAUTH$","",88,88,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_USERAUTH1","",89,89,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","UGROUP$","",90,90,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_UGROUP1","",91,91,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_UGROUP2","",92,92,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","TSQ$","",93,10,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","SYN$","",94,94,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","VIEW$","",95,95,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","TYPED_VIEW$","",96,96,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","SUPEROBJ$","",97,97,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_SUPEROBJ1","",98,98,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_SUPEROBJ2","",99,99,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","SEQ$","",100,100,"TABLE",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","I_VIEW1","",101,101,"INDEX",07-FEB-18,07-FEB-18,"2018-02-07:19:20:29","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
 
100 ROWS selected. 
 
SQL> spool off
SQL> CREATE TABLE demo_load AS SELECT * FROM all_objects WHERE 1=2;
 
TABLE DEMO_LOAD created.
 
SQL> LOAD demo_load objects.csv
--Insert failed in batch rows  101  through  103 
--ORA-01400: cannot insert NULL into ("HR"."DEMO_LOAD"."OWNER")
--Row 101 data follows:
INSERT INTO DEMO_LOAD(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID)
VALUES ('','','',NULL,NULL,'',to_date(''),to_date(''),'','','','','',NULL,'','','','','','','','',NULL,NULL,NULL,NULL);
--Row 102 data follows:
INSERT INTO DEMO_LOAD(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID)
VALUES ('100 rows selected. ','','',NULL,NULL,'',to_date(''),to_date(''),'','','','','',NULL,'','','','','','','','',NULL,NULL,NULL,NULL);
--Row 103 data follows:
INSERT INTO DEMO_LOAD(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID)
VALUES ('','','',NULL,NULL,'',to_date(''),to_date(''),'','','','','',NULL,'','','','','','','','',NULL,NULL,NULL,NULL);
--Number of rows processed: 103
--Number of rows in error: 3
1 - WARNING: LOAD processed WITH errors
SQL> commit;

Wait, what’s with the 3 failed rows at the end?

If I tail the csv file I created, there’s a few extra lines due to feedback…hence the 3 rows failed to run – which is good 🙂

Browsing the TABLE in SQL Developer it looks like it ran just as it should.

DATEs and TIMESTAMPs came in just A-OK as well 🙂

If I go in and remove those 3 lines, truncate the table, and run the LOAD again…

Cleaner 🙂

Is this the BEST way to load CSV?

Probably not – I would still advise folks it’s much faster to use sqlldr or External TABLEs, but it would be hard to argue this isn’t simpler, especially when you’re dealing with reasonable amounts of data.

Reasonable being a number of rows that are adapt at being INSERTed one at a time.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

19 Comments

  1. SQL> set loadformat enclosures off
    SQL> set loadformat delimiter |
    SQL> set load batch_rows 100
    SQL> set load timestamp_format dd-mon-yy hh.mi.ss.ff AM
    SQL> set timer on
    14:40:57 SQL> set load truncate on
    14:40:57 SQL>
    14:40:57 SQL> LOAD MIGDR.D1TABLE@MIGRATION C:\MIG\08162023_225339\2_MIGRATIONTEST_08162023_225339.csv;
    Table MIGDR.D1TABLE@MIGRATION does not exists and cannot be loaded
    14:41:25 SQL>
    14:41:25 SQL> insert into D1TABLE@MIGRATION(createddate) values(’02-SEP-21 12.00.00.000000000 AM’);

    1 row inserted.

    14:41:34 SQL> rollback;

    Rollback complete.

    14:41:38 SQL> SELECT count(1) from “MIGDR”.”D1TABLE”@MIGRATION;

    COUNT(1)
    ___________
    261

    14:41:48 SQL>

    Here MIGRATION is the database link.

    • Harsha VY

      Hi Jeff,

      I’m trying to load data using database link. The real question was, will SQLCL LOAD allows data load through database link?

      Thanks
      Harsha

    • You connect to a db and reference a file or object store item…to put into a table in that db.

      No db_links

  2. Is the LOAD command able to load a CSV file based on position?

    I have some files where the headers don’t match the column names however, the positions do match. Using the GUI at Step 4 of 5 I’m able to select “Match By Position” and it works. Would like to use SQLDeveloper LOAD command instead of the GUI.

    Thanks,
    Rob

    • Yes just ignore the header line and it defaults to position matching at that point, I wrote a post on this subject very recently.

  3. Hi Jeff,
    In SQLCL I’m having trouble loading delimited files starting with a timestamp when there is more than 2 rows, here’s my test case.

    cat x.txt
    “COL1″~~~”COL2″~~~”COL3″
    21-DEC-21 00.00.00.000000~~~123~~~”ABC”
    21-DEC-21 00.00.00.000000~~~456~~~”DEF”
    21-DEC-21 00.00.00.000000~~~789~~~”GHI”

    create table x (
    col1 timestamp,
    col2 number,
    col3 varchar2(10)
    )
    ;

    ALTER SESSION SET nls_timestamp_format=’DD-MON-YYYY HH24.MI.SS.FF6′;
    set loadformat delimiter ~~~
    load table x /tmp/x.txt

    Load data into table X

    csv
    column_names on
    delimiter ~~~
    enclosures “”
    encoding UTF8
    row_limit off
    row_terminator default
    skip_rows 0
    skip_after_names

    #ERROR Insert failed for row 1
    #ERROR COL1: GDK-05058: non-numeric character found
    #ERROR Row 1 data follows:
    21-DEC-21 00.00.00.000000,123,ABC
    #ERROR Insert failed for row 2
    #ERROR COL1: GDK-05058: non-numeric character found
    #ERROR Row 2 data follows:
    21-DEC-21 00.00.00.000000,456,DEF
    #ERROR Insert failed for row 3
    #ERROR COL1: GDK-05058: non-numeric character found
    #ERROR Row 3 data follows:
    21-DEC-21 00.00.00.000000,789,GHI
    #INFO Number of rows processed: 3
    #INFO Number of rows in error: 3
    #INFO No rows committed
    WARNING: Processed with errors

    When I have I remove the 3rd row x.txt it loads fine

    “COL1″~~~”COL2″~~~”COL3″
    21-DEC-21 00.00.00.000000~~~123~~~”ABC”
    21-DEC-21 00.00.00.000000~~~456~~~”DEF”

    Load data into table X

    csv
    column_names on
    delimiter ~~~
    enclosures “”
    encoding UTF8
    row_limit off
    row_terminator default
    skip_rows 0
    skip_after_names

    #INFO Number of rows processed: 2
    #INFO Number of rows in error: 0
    #INFO Last row processed in final committed batch: 2
    SUCCESS: Processed without errors

    Thanks
    Nick

    • ALTER SESSION SET nls_timestamp_format=’DD-MON-YYYY HH24.MI.SS.FF6′; ... NO, instead, try -

      SET LOAD TIMESTAMP DD-MON-YYYY HH24.MI.SS.FF6

    • Nick Hall

      Brilliant, thanks for your help Jeff.

      Merry Christmas

  4. SQLcl does not seem to handle embedded line break in the CSV data, ie:

    1234,”this is data”,”this is more data
    with an embedded newline”,”and finally more data”,””

    That is valid CSV as per the spec (rfc4180), but SQLcl throws an error on it.

    • Away from my PC for a few days but this is a fun aspect of csv…it’s not super happy with multi line strings. Standard or no, not all apps dig it.

      Easiest answer for you now is to open it in Excel and save it back out as xlsx.

  5. Hi Jeff,
    yes, the workaround solves this issue.
    Thanks again for your immediate response and help!
    Andre

  6. Hi,
    just to let you know:
    It works like that (from a .bat file):
    DELETE FROM upl_pk_vzk_import;
    COMMIT;
    set loadformat delimiter ; column_names on
    LOAD upl_pk_vzk_import aufbereitung_pk_vzk_semicolon.csv;
    QUIT;

    However, it errors when I do it this way (please note the line breake):
    DELETE FROM upl_pk_vzk_import;
    COMMIT;
    set loadformat delimiter ;
    column_names on
    LOAD upl_pk_vzk_import aufbereitung_pk_vzk_semicolon.csv;
    QUIT;
    — cmd output:

    Fehler beim Start in Zeile: 5 Datei @ C:\Working\APP_SWLE_ALLIN\Laden_von_Aufbereitung_PK_VZK_csv_Daten_mit_SQLcl_Sample_20210813_1055\SQLcl_load.sql
    In Befehl –
    column_names on
    Fehlerbericht –
    Unbekannter Befehl

    format csv

    column_names on
    delimiter ,
    enclosure_left ”
    enclosure_right ”
    encoding UTF8
    row_limit off
    row_terminator default
    skip_rows 0
    skip_after_names

    –Spalte in Headerzeile DIM_KST;DIM_JAHRE;DIM_MONAT;DIM_KENNZAHL;DIM_VERSION;DIM_BWA;DIM_GB;DIM_CENTER;DIM_SEG;WERT ist nicht f├╝r Tabelle definiert.
    –Anzahl verarbeitete Zeilen: 0
    –Anzahl fehlerhafte Zeilen: 0
    –Keine Zeilen festgeschrieben
    SEVERE: Load terminated

    • Right – I logged that bug, but you know how to get it working in the meantime now, right?

  7. Hi Jeff,
    We need badly a parameter to set CSV delimiter for LOAD. There is a problem…
    SQLcl implicitely uses the comma, however when storing csv from Excel (in german) the delimiter is a semi-colon. Very ugly… Can you help. Or do I miss something.

    Would be nice if you could drop a mail to me.
    Thank you in advance!
    Andre

    • You can do it, but there’s a bug, which requires a workaround. You have to set something AFTER the delimiter, so the command doesn’t ‘eat’ the ; as a statement delimiter. I will file a bug for you.

      This will work –


      SQL> set loadformat delimiter ; column_names on
      SQL> load temp_locations semicolon.csv

      format csv

      column_names on
      delimiter ;
      enclosure_left "
      enclosure_right "
      encoding UTF8
      row_limit off
      row_terminator default
      skip_rows 0
      skip_after_names

      --Number of rows processed: 23
      --Number of rows in error: 0
      --Last row processed in final committed batch: 23
      0 - SUCCESS: Load processed without errors
      SQL> select * from temp_locations fetch first 5 rows only;

      LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID
      ______________ __________________________ ______________ ____________ ___________________ _____________
      1000 1297 Via Cola di Rie 00989 Roma IT
      1100 93091 Calle della Testa 10934 Venice IT
      1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
      1300 9450 Kamiya-cho 6823 Hiroshima JP
      1400 2014 Jabberwocky Rd 26192 Southlake Texas US

      SQL>

  8. Have you tried loading a file in a path that contains spaces (or a file name with spaces)? I have not been able to get around this. I have tried quotes, double quotes, substitution variables and escapes to no avail. Do you have any suggestions?

  9. This is great. Can you add a little more detail about how this works depending on csv file location? For example, how to refer to the file if it is on a linux server the database is hosted on, or an sftp site, or on a windows desktop running SQL, or a web site using PHP…
    Thanks!

    • It’ll work as long as the process (SQLcl) has access to the file. If it’s on a SFTP site, I’d download it locally.

      PHP, not sure how that comes into play with a CLI.

Write A Comment