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.

thatjeffsmith
Author

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

2 Comments

  1. 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!

    • thatjeffsmith

      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