| passing value into sqlldr [message #460930] |
Tue, 15 June 2010 11:25  |
spgmes
Messages: 3
Registered: June 2010
|
Junior Member |
|
|
I am executing sqlldr from a UNIX shell script (HP box). The data I am loading is coming from a fixed length flat file. I also want to be able to pass a variable from the shell to the loader job to be loaded with the rest of the data into the oracle table. The value being passed will change with each execution of the shell script which is run on a daily basis.
Hoping someone has a suggestion.
|
|
|
|
| Re: passing value into sqlldr [message #460932 is a reply to message #460930] |
Tue, 15 June 2010 11:35  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>I also want to be able to pass a variable from the shell to the loader job to be loaded with the rest of the data into the oracle table.
What do you mean by "rest of the data"?
Adding variable to each column?
Here is a generic idea. You can dynamically rebuild the controlfile to your need ( you can use any tool you want. Sed/awk..)
http://www.orafaq.com/forum/t/26637/0/
If the above wont work for you,
please post some sample data, relevant table DDL with relevant columns.
[Updated on: Tue, 15 June 2010 11:38] Report message to a moderator |
|
|
|
|
|
|
|
| Re: passing value into sqlldr [message #460952 is a reply to message #460932] |
Tue, 15 June 2010 13:14 |
spgmes
Messages: 3
Registered: June 2010
|
Junior Member |
|
|
I guess I should have explained I'm new at this. Here's what my control.file looks like:
LOAD DATA
--
INTO TABLE COA_NIXIE
APPEND
WHEN (10:15) = '999999'
--
(SIX_DIGIT_MAILER_ID POSITION(10:15) INTEGER EXTERNAL,
MAILPIECE_ID POSITION(17:25) CHAR,
SEQ_NUM POSITION(2:9) INTEGER EXTERNAL,
USPS_FILE_NAME CONSTANT 'test file',
SYS_CREATION_DATE SYSDATE,
SYS_UPDATE_DATE FILLER,
OPERATOR_ID FILLER,
APPLICATION_ID CONSTANT 'BLONEC',
DL_SERVICE_CODE CONSTANT '4439',
DL_UPDATE_STAMP FILLER,
MOVE_EFF_DATE POSITION(33:38) INTEGER EXTERNAL,
MOVE_TYPE POSITION(39:39) CHAR,
DELIVERABILITY_CODE POSITION(40:40) CHAR,
and on and on...
Here is the command I am executing from my shell script:
sqlldr userid=$CARES_USERNAME/$CARES_PASSWORD@$CARES_INSTANCE,control=$CONTROL_FILE,SKIP=1,LOG=$COA_FILE.log,BAD=$COA_FILE.bad,DISCARD=$COA_ FILE.dis, DATA=$COA_LOC/$COA_FILE
What I need to do is populate the USPS_FILE_NAME column in the COA_NIXIE table with the value of the $COA_FILE shell variable.
I think I need to create a dynamic control file but I couldn't follow what was been shown in the examples.
Can you help or where can I go for more explanation on creating dynamic control files.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
