OraCmd - Oracle access without client tool »

Download OraCmd Free Trial »

Load CLOB data

OraCmd can load CLOB data.

A Short Example

This section contains a short example showing how OraCmd load CLOB data.

The Data

The particular file (clob.txt) used for this example contains CLOB data. It's a delimited text file.

	1,Hello CLOB
	2,Not everybody use CLOB
	3,But some time we should use it
		

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

	create table T_CLOB
	(
	  ID     NUMBER,
	  F_CLOB CLOB,
	  CONSTRAINT "PK_T_CLOB" PRIMARY KEY ("ID")
	)
		

OraCmd commmand

	load table=t_clob datafile="E:\test\clob.txt" delimiter="," loadtype=append
		

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=t_clob datafile="E:\test\clob.txt" delimiter="," loadtype=append 
	quit=y log="E:\test\load_clob.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