OraCmd - Oracle access without client tool »

Download OraCmd Free Trial »

Load Oracle data from flat file


delimeter: delimiter char ("|",",","#","TAB","WHITESPACE")

quote: quote char (quote="'" => single quotes, quote="chr(34)" => double quotes)

header: y|n, data file contains field name at first line

schema: schema name, use this parameter to load specific schema's data, if not set, use logon user's default schema

table: table name

datafile: data file name

loadtype: append|replace|update
	append: appends the new rows to the table.
	replace: all rows in the table are deleted and the new data is loaded.
	update: update the rows by the new data.

updatekey: key feild to update with, usually use primary key, only for loadtype=update.

dateformat: date format (e.g. "YYYY-MM-DD HH24:MI:SS")

timestampformat: timestamp format (e.g. 'YYYY-MM-DD-HH24.MI.SS.FF6')

nullif: specifies that the column should be loaded as NULL 
("BLANKS" for zero length strings being loaded into numeric columns)

badfile: write the content that can not load to this badfile

errors:  maxinum error count

OraCmd> load table=dim_state_data
     2  datafile=e:\temp\tables\dim_state_data_hdr_plus_1_row.csv
     3  delimiter="|"
     4  header=y
     5  loadtype=replace
     6  dateformat="HH24.MI.SS"
     7  timestampformat="YYYY-MM-DD-HH24.MI.SS.FF6";

Oracle import csv command

You can also read this: Convert SQL*Loader control file to OraCmd command