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