OraCmd - Oracle access without client tool »

Download OraCmd Free Trial »

Custom unload date foramt

OraCmd can custom date format.

A Short Example

This section contains a short example showing how OraCmd custom date format.

The Table

We used the following SQL statement to create the table:

	create table DIM_STATE_DATA
	(
	  NAME                     VARCHAR2(30),
	  STATE_DATA_WK            NUMBER(20) not null,
	  STATE_CD                 CHAR(2) not null,
	  EFFECTIVE_DT             DATE,
	  EXPIRE_DT                DATE,
	  ALLOWABLE_CALLTIME_START DATE,
	  ALLOWABLE_CALLTIME_STOP  DATE,
	  STATE_WK                 NUMBER(20) not null,
	  SORT_ORDER               INTEGER,
	  DW_LOAD_TIMESTAMP        TIMESTAMP(6) not null,
	  ROW_MAINTAINED_BY_CD     CHAR(1),
	  MOST_RECENT_ROW_IND      CHAR(1) not null,
	  CRE_USER                 CHAR(20) not null,
	  CRE_DATE                 TIMESTAMP(6) not null,
	  MOD_USER                 CHAR(20) not null,
	  MOD_DATE                 TIMESTAMP(6) not null
	)
		

We want custom the date data format and timestamp data format.

OraCmd commmand

Parameters: dateformat and timestampformat.

	unload table=dim_state_data datafile="E:\temp\dim_state_data.txt" 
	dateformat="YYYY-MM-DD HH24:MI:SS" timestampformat="YYYY-MM-DD_HH24:MI:SS.FF6" 
	delimiter="|" quote="chr(34)" header=y;
		

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 
	table=dim_state_data datafile="E:\temp\dim_state_data.txt" 
	dateformat="YYYY-MM-DD HH24:MI:SS" timestampformat="YYYY-MM-DD_HH24:MI:SS.FF6" 
	delimiter="|" quote="chr(34)" header=y
	log="E:\temp\unload_dateformat.log"
		

The Datafile

	dim_state_data.txt
	

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)