Passing filename dynamically in SPOOL of SQL*PLUS in shell script

Shell Programming and Scripting
1

Hi all,
I am executing shell script in which I am using SQLLDR
In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following

Spool folder/filename.txt
<PL/SQL block>

But now I dont want this filename.txt to be hardcoded, this should be dynamic. I mean it should be something like this

Spool folder/&1.txt
<PL/SQL block>

At runtime &1 should be substituted with the given string. i.e. through the input in shell script

Does anyone have idea how to implement this.

Regards
JC

2

Generate the script in a shell script, getting the file name from $1, and pipe it to sqlldr/sqlplus in the script.

3 
Script :

$cat myscript.ksh

file_name=$1

sqlplus -s  scott/tiger@oracle<<EOF
spool $file_name;
begin
do_something;
end;
/
spool off;
exit;
EOF

You may call the script with the filename as a parameter , this should do the trick

$sh myscript.ksh  "/home/user/data/file.txt"