Load Oracle Data To Other Schema

OraCmd - Oracle access without client tool »

Load data to other schema

OraCmd can load data to other schema's table if you have the "write" (insert, update) privilege on the target schema's table.

A Short Example

This section contains a short example showing how OraCmd load data to specific schema.

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

OraCmd commmand

The default schema for user "hr" is "hr", and the table "HANDHELDS" is in schema "XDB", so we need use the parameter "schema".

OraCmd command:

	load schema=xdb 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):

	"C:\Program files\OraCmd\OraCmd" userid=hr/hr@localhost:1521:xe task=load 
	schema=xdb 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