Hello guys, i need help with something i have to do:
Here's the thing:
I need to create a shell script wich:
- List all files in a directory. (This files contain the data to load).
- For each file, create an oracle table with the name of the file.
- Load the data of the file in the corresponding table (i mean the data of each file should load in the table with the same name of the file).

I understand i need to use sqlldr and create a control file on the fly to pass it the $fileName and the $tableName (wich i created).

I did this (unix shell script with sqlldr commands), but i'm not sure how to create the control file and pass it unix shell script variables:


for i in /directory/prefix_files*; do

fileName=$(basename $i)
sqlplus user/pass@SID @createTableScript.sql '$i'
sqlplus -s user/pass@SID << EOF
set feed off
set head off
set termout off
prompt Generating the controlfile LOAD_DATA_CONTROL.ctl as
select 'load data'||chr(10)||
'infile' '$fileName'||chr(10)||
'insert into table''$tableName'||chr(10)||
'fields terminated by '','''||chr(10)||
from dual;
spool off;

sqlldr user/pass@SID control=LOAD_DATA_CONTROL.ctl


The unix part it's ok, the code of the sqlldr i copied from internet, but not sure if its ok. Can anyone guide me?