RDBMS version: 19c
Shell : Bash sell in Oracle Linux 7.9
I have a bash shell script called test.sh invokes sqlplus inside heredoc (shown below).
test.sh executes sample.sql file as shown below
I am passing a shell variable called $VAR2 to the sample.sql script
Everything seems to work except the spooled file name for the spool command inside sample.sql.
I am expecting a spool file named script_for_SALES.sql to be generated.
Instead, I get filenames like script_for_SALESsql.lst ! Any workaround for this ?
I tried few other variants also (mentioned at the bottom)
$ cat test.sh
#!/bin/bash
export VAR2=SALES
sqlplus / as sysdba <<-EOF
@/path/in/linux/sample.sql $VAR2
exit
EOF
$ cat /path/in/linux/sample.sql
set head off
set verify off
set echo off
set lines 140
set trim on
set pages 0
set feedback off
spool "/path/in/linux/script_for_&1.sql"
SELECT 'alter session set container = &1 ;' FROM DUAL;
select 'select sysdate from dual;' from dual;
prompt exit
spool off
exit
Executing test.sh
$ ./test.sh
<banner snipped for readability>
SQL> alter session set container = SALES ;
select sysdate from dual;
exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
$
$ ls -l
total 12
-rw-r--r--. 1 oracle oinstall 287 Aug 12 16:52 sample.sql
-rw-r--r--. 1 oracle oinstall 69 Aug 12 16:56 script_for_SALESsql.lst
-rwxr-xr-x. 1 oracle oinstall 129 Aug 12 16:56 test.sh
If I try
spool /path/in/linux/script_for_'&1'.sql
or
spool "/path/in/linux/script_for_'&1'.sql"
I get a spool file named script_for_'SALES'.sql. But, I don't want single quotes around SALES
If I try spool /path/in/linux/script_for_&1.sql
Again, I get the spool file generated with the name script_for_SALESsql.lst
Any way to get the spool file with the name script_for_SALES.sql ?