Pass table value to SQL Loader Control File
Posted on 2014-03-18
There is a process that loads and verifies files in Oracle on Unix (AIX).
For example, a file called ORANGES.txt gets validated and gets assigned a file_key = 459 in the DATA_STG table.
Now its time to load this file, ORANGES.txt using SQL Loader.
Here's the control file:
APPEND INTO TABLE DATA_PROD
FIELDS TERMINATED by '|' TRAILING NULLCOLS
FILE_FILE_KEY CONSTANT $FILE_KEY,
USER_ID CONSTANT "AA",
FILE_FILE_KEY needs to come from the table DATA_STG, which means it needs to be 459.
I know that I need a shell script to get the FILE_KEY from the table DATA_STG. I need help writing that piece. I've done some research and know that I can dynamically create the control file with this value, I'm not sure how. Any help would be appreciated.