OraCmd - Oracle access without client tool »

Convert SQL*Loader control file to OraCmd command

To use SQL*Loader you have to install Oracle client software. To avoid installing the large client, you can use OraCmd.

OraCmd is not as flexible as SQL*Loader, but it is easy and powerful enough.

A Short Example

This section contains a short example showing how SQL*Loader control file be converted to OraCmd command.

The Data

The particular file used for this example contains handhelds infomation. It's a delimited text file. The following example shows three records from that file. The lines wrap in this page, but in the file each name is on its own line:

	"ACQ","2007-09-04 15:47:47","XV6700","917-817-6829","054-07696647",
	"HT638E602306","166.240.214.11","",""

	"ACQ","2007-09-04 15:50:17","XV6700","917-817-6904","054-07696459",
	"HT638E602118","166.240.214.12","",""

	"ACQ","2007-09-04 15:50:26","XV6700","917-817-8778","054-07696510",
	"HT638E602169","166.240.214.13","",""

As you can see, the data in the file is comma-delimited, and each field is enclosed within double quotes.

We used the following SQL statement to create the table into which all this data will be loaded:

	create table HANDHELDS
	(
	  REGION_NAME    VARCHAR2(20),
	  ASSIGN_DATE    DATE,
	  MODEL          VARCHAR2(20),
	  PHONE_NUMBER   VARCHAR2(20),
	  ESN            VARCHAR2(20),
	  SERIAL         VARCHAR2(20),
	  IP_ADDRESS     VARCHAR2(20),
	  ASSIGN_TECH_ID VARCHAR2(20),
	  MOBILE_VERSION VARCHAR2(20)
	)

SQL*Loader control file

The following control file will be used to load the data.

	load data
	infile 'E:\testdata\handhelds.csv'
	replace
	into table HANDHELDS
	fields terminated by ","
	optionally enclosed by '"'
	(
	  REGION_NAME,
	  ASSIGN_DATE date 'yyyy-mm-dd hh24:mi:ss',
	  MODEL,
	  PHONE_NUMBER,
	  ESN,
	  SERIAL,
	  IP_ADDRESS,
	  ASSIGN_TECH_ID,
	  MOBILE_VERSION
	)

OraCmd commmand

Convert the SQL*Loader control file to following OraCmd command:

	load table=handhelds datafile="E:\testdata\handhelds.csv" delimiter="," 
	quote="chr(34)" dateformat="YYYY-MM-DD HH24:MI:SS" loadtype=replace

Or a batch file(replace the userid with your own database user info. The lines wrap in this page, but in the file is 1 line.):

	"C:\Program files\OraCmd\OraCmd" userid=hr/hr@localhost:1521:xe task=load 
	table=handhelds datafile="E:\testdata\handhelds.csv" delimiter="," quote="chr(34)" 
	dateformat="YYYY-MM-DD HH24:MI:SS" loadtype=replace quit=y 
	log="E:\test\load_handhelds.log"

The OraCmd parameter descriptions are as follows:

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