Home » RDBMS Server » Server Utilities » sqlrd control file
sqlrd control file [message #442109] Thu, 04 February 2010 13:40 Go to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Hi

I am not able to understand how and which dat is loaded for a certain sqllrd control file logic used in our application, can anyone please explain it.

data file is of format xxxx.dat and data is like :

43355334;1978-01-29;
44334433;1970-01-15;
43434343;1974-03-26;
43334443;1977-09-20;

Control File used is :
OPTIONS (LOAD=500000)
LOAD DATA
CHARACTERSET WE8ISO8859P1
INTO TABLE ABCD
TRUNCATE
WHEN (9)=';'
AND (8)<>';' AND (7)<>';' AND (6)<>';' AND (5)<>';'
AND (4)<>';' AND (3)<>';' AND (2)<>';' AND (1)<>';'
Fields terminated by ';'
TRAILING NULLCOLS
(
EMPLOYEE_NUMBER char,
BIRTH_DATE date 'YYYY-MM-DD',
DTTM_STAMP sysdate
)

Can anyone please explain me the logic used here by control file ?

Regards
-navin
Re: sqlrd control file [message #442122 is a reply to message #442109] Thu, 04 February 2010 15:12 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
If there is not a semicolon in all positions between 1-8 and there is a semicolon in position 9, then load the data into the corresponding columns.
Re: sqlrd control file [message #442127 is a reply to message #442122] Thu, 04 February 2010 21:36 Go to previous messageGo to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Hi

Many Thanks for your reply.
Can you also tell me the purpose of below syntax :

1. OPTIONS (LOAD=500000)
2. TRAILING NULLCOLS

Regards
Naveen
Re: sqlrd control file [message #442135 is a reply to message #442127] Thu, 04 February 2010 22:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm

Regards
Michel
Re: sqlrd control file [message #442136 is a reply to message #442127] Thu, 04 February 2010 22:43 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Answered here ....[[SQL%2ALoader_FAQ]]

Quote:
The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.



Quote:
Values specified on the command line override values specified in the OPTIONS clause in the control file.


For more reference see this...

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1004643

sriram Smile

[Updated on: Thu, 04 February 2010 22:44]

Report message to a moderator

Re: sqlrd control file [message #443146 is a reply to message #442109] Sat, 13 February 2010 00:40 Go to previous message
Blaylock
Messages: 6
Registered: February 2010
Location: USA
Junior Member
TOAD has an interface to do SQL*Loads, it can generate the control files too...
Previous Topic: Hiding password in exp command
Next Topic: SQL Loader : How can we make use of RECNUM in SQL expression
Goto Forum:
  


Current Time: Wed Dec 07 06:56:24 CST 2016

Total time taken to generate the page: 0.04802 seconds