Withdata Software

OraCmd - Oracle access without client tool »

Download OraCmd Free Trial »

Custom unload record delimiter

The default OraCmd unload record delimiter is CRLF, this means one record one line.

Sometimes we need specify the char(s) to use for the record delimiter, for example, if you with all kinds of LF chars in you data and they're causing the records to break to new lines.

A Short Example

This section contains a short example showing how OraCmd custom record delimiter.

The Table

We used the following SQL statement to create the table:

	create table CUSTOMER
	(
	  LAST_NAME VARCHAR2(30) not null,
	  STATE_CD  VARCHAR2(2),
	  SALES     NUMBER
	)

OraCmd commmand

Parameter: recorddelimiter

	unload header=y recorddelimiter=/ table=customer datafile="E:\temp\customer.txt";
Oracle Command Tool - unload oracle custom record delimiter

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=unload 
	header=y recorddelimiter=/ table=customer datafile="E:\temp\customer.txt"
	log="E:\temp\unload_customer.log"

The Datafile

	customer.txt

The OraCmd parameter descriptions are as follows: 

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

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

recorddelimiter: record delimiter, default is CRLF

header: y|n, print field name at first line

exportclob: y|n, export clob data to file

table: table name ("all" for unload all table)

query: sql query string (can not use multi-line sql)

sqlfile: sql script file contains the query you want to unload

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

datafile: data file name

folder: data files folder (for unload all table)

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

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

buffer: Number of rows that will be transferred across the network at the same time. 
  This property can have a great impact on performance. 
  So it is preferable to choose the optimal value of buffer for each SQL statement and software - hardware configuration experimentally. 
  The default value is 25.

quit = y|n (quit OraCmd after run batch file)