OraCmd - Oracle access without client tool »

Download OraCmd Free Trial »

Unload Oracle data form sql file

OraCmd can unload data from sql file.

A Short Example

This section contains a short example showing how OraCmd unload data from a sql file.

The Table

We used the following SQL statement to create the table:

	create table JOBS
	(
	  JOB_ID     VARCHAR2(10) not null,
	  JOB_TITLE  VARCHAR2(35),
	  MIN_SALARY NUMBER(6),
	  MAX_SALARY NUMBER(6),
	  CONSTRAINT JOB_ID_PK PRIMARY KEY (JOB_ID)
	)
		

Just need unload the data in "job_id" and "job_title" fields to file.

sqlfile content: unload_jobs.sql

	select job_id,job_title from jobs
		

OraCmd commmand

	unload sqlfile=e:\temp\tables\unload_jobs.sql
	datafile=e:\temp\tables\jobs_part2.txt
	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 
	sqlfile=e:\temp\tables\unload_jobs.sql datafile=e:\temp\tables\jobs_part2.txt
	delimiter="," quote="chr(34)" header=y
		

The datafile content: jobs_part2.txt

"JOB_ID","JOB_TITLE"
"AD_PRES","President"
"AD_VP","Administration Vice President"
"AD_ASST","Administration Assistant"
"FI_MGR","Finance Manager"
"FI_ACCOUNT","Accountant"
"AC_MGR","Accounting Manager"
"AC_ACCOUNT","Public Accountant"
"SA_MAN","Sales Manager"
"SA_REP","Sales Representative"
"PU_MAN","Purchasing Manager"
"PU_CLERK","Purchasing Clerk"
"ST_MAN","Stock Manager"
"ST_CLERK","Stock Clerk"
"SH_CLERK","Shipping Clerk"
"IT_PROG","Programmer"
"MK_MAN","Marketing Manager"
"MK_REP","Marketing Representative"
"HR_REP","Human Resources Representative"
"PR_REP","Public Relations Representative"
	

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 tables)

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)