OraFAQ Forum: Server Utilities » Passing Varaibles to the control file in SQL Loader

Home » RDBMS Server » Server Utilities » Passing Varaibles to the control file in SQL Loader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passing Varaibles to the control file in SQL Loader [message #158147]

Wed, 08 February 2006 12:25

Ganeshbabu Venkat
Messages: 17
Registered: March 2001

Junior Member

Can we dynamically pass some variables in to the control file. This is one of my fields in the SQL Loader

LAST_NOTIFICATION_DT "systimestamp",

is there a way that i could get the value for systemtimestamp in my shell file and pass it on as a variable value. please let me know.

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158149 is a reply to message #158147]

Wed, 08 February 2006 12:30

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Usually yes.
http://www.orafaq.com/forum/m/73220/42800/?srch=sqlldr+variable#msg_73220
Have'nt tested for systimestamp.

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158151 is a reply to message #158147]

Wed, 08 February 2006 12:37

Ganeshbabu Venkat
Messages: 17
Registered: March 2001

Junior Member

hmm. you are dynamically creating a control file. my control file will remain the same. its just that field i need to populate.

So basically i'm looking to see if i can get the systemtimestamp in oracleSQL put it an variable and pass that on.

any thoughts?

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158152 is a reply to message #158151]

Wed, 08 February 2006 12:44

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> i'm looking to see if i can get the systemtimestamp in oracleSQL put it an variable and pass that on.
I dont understand.
Pass it to where?
the inputdatafile? Or to controlfile?

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158153 is a reply to message #158152]

Wed, 08 February 2006 12:48

Ganeshbabu Venkat
Messages: 17
Registered: March 2001

Junior Member

to pass it on to the control file

OPTIONS (SKIP=1, ERRORS=999999)
LOAD DATA
INFILE '$data'
BADFILE '$bad'
DISCARDFILE '$discard'
APPEND INTO TABLE REFRESH_SUPPRESSION_STAGING
WHEN (1) !='3'
(
ORIGINAL_LOAD_DT "sysdate",
LAST_NOTIFICATION_DT "systimestamp",
NPA_CD POSITION(2:4),
NXX_CD POSITION(5:7),
LINE_NBR POSITION(8:11),
NOTIFICATION_SOURCE_CD POSITION(12:14)
)

LAST_NOTIFICATION_DT variable name that holds the time stamp

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158155 is a reply to message #158153]

Wed, 08 February 2006 13:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

so systemtimestamp remains a constant during this load?
If so, use CONSTANT.
If you want to script it, recreate the controlfile dynamically.
You have one baseline / template controlfile.
For every load create a new controlfile from template and use it.

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158157 is a reply to message #158147]

Wed, 08 February 2006 13:20

Ganeshbabu Venkat
Messages: 17
Registered: March 2001

Junior Member

Any examples how to create a dynamic control file and call it.

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158158 is a reply to message #158157]

Wed, 08 February 2006 13:22

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

The above said URL talks all about it

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158160 is a reply to message #158147]

Wed, 08 February 2006 13:33

Ganeshbabu Venkat
Messages: 17
Registered: March 2001

Junior Member

Yeah but as u said... its not straight forward with the systimestamp i guess.. if its not too much trouble can you try a few scripts .. i'm going to try something here...

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158162 is a reply to message #158160]

Wed, 08 February 2006 13:46

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

The process is almost the same.
>>i can get the systemtimestamp in oracleSQL put it an variable and pass that on
Why do want to get something out of oracle and throw in back in there?

Report message to a moderator

Re: Passing Varaibles to the control file in SQL Loader [message #158163 is a reply to message #158147]

Wed, 08 February 2006 13:49

Ganeshbabu Venkat
Messages: 17
Registered: March 2001

Junior Member

I agree. But I heard that this will be more efficient? is that right..

I mean in the SQL loader when u actually specify timestamp does it go out everytime to the oracle function to fetch the time stamp as opposed to this way where we just get the time stamp once and do it..

I'm not sure...

Report message to a moderator

Previous Topic:	Problem In login
Next Topic:	getting error when loading the data. Any idea

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 03:57:40 CST 2025