I have the .prog file from a host program created in oracle apps. I am sending a parameter from oracle apps with host program and I can access it in the .prog file like this e.g.

echo "5 Concurrent Program Parameter 1 : " ${5}

I need to use this parameter ($5) into the control file (.ctl) where I will insert some columns and this parameter into a new table. e.g

LOAD DATA
INSERT INTO TABLE TABLE_NAME
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COL1,
COL2,
DATA_FROM_PROG (5) => ** here i need to insert that data from the .prog file**
)

I am thinking it would have to be included in this command somehow so it creates this control file or another dynamically but I can't figure out how to send that parameter and make this work. I am familiar with this line that I used in the past for simpler problems

e.g.sqlldr userid=user/pass data=$5 control=control.ctl

Thanks.

CodePudding user response：

I wouldn't know, as I don't know anything about Oracle apps. nor ".prog" files.

Workaround - from my perspective - would be to

• load only known data (from the source file)
• data_from_prog would be specified as a filler field (and populated with NULL values (if trailing nullcols is specified))
• after loading session is over, update that column from Oracle apps. - then you'd use a simple update statement; you're in (PL/)SQL world, it is easy to write such a query (at least, I hope so)

CodePudding user response：

Using Bash Script in the .prog file to create the control file (.ctl) dynamically from scratch seems to be working and I can use the parameters as well. So in the .prog file we would have:

echo "5 Concurrent Program Parameter 1      : " ${5} /*this is only to test it*/

/* *Printf* with *>* command will create and edit a file.
Alternative *Printf* with *>>* would append to the file*/

printf "LOAD DATA\n
INFILE 'path_to_csv_file.csv'\n /*this is data for col1, col2 etc*/
INSERT INTO TABLE TABLE_NAME\n
FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'\"\'\n
TRAILING NULLCOLS\n
(COL1,\n
COL2,\n
DATA_FROM_PROG CONSTANT ${5})" > [name and path to control file (e.g./folder/control.ctl)]

This way, when the .prog file is executed it will Dynamically create the .ctl file which will have the parameter that we want (${5}). And we can also add something like this to run the .ctl file

sqlldr userid=user/pass control=[path_to_control]control.ctl log=track.log

Also make sure to escape the quotes ' and double quotes " with \ because you will get some errors otherwise.