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:

prefix="DATA_LOAD_SHELL_SCRIPT_";

for i in /directory/prefix_files*; do

fileName=$(basename $i)
tableName=$prefix""$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
prompt
spool LOAD_DATA_CONTROL.ctl
select 'load data'||chr(10)||
'infile' '$fileName'||chr(10)||
'insert into table''$tableName'||chr(10)||
'fields terminated by '','''||chr(10)||
'('||chr(10)||
'CAMPAING_CODE','||chr(10)||
'PRODUCT_INSTANCE,'||chr(25)||
'EMAIL'||chr(10)||
'NAME'||chr(25)||
'SURNAME'||chr(25)||
')'
from dual;
spool off;
exit;
EOF

sqlldr user/pass@SID control=LOAD_DATA_CONTROL.ctl

done

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?

Thanks!!!