RDBMS version: 19c
Shell : Bash sell in Oracle Linux 7.9
I had a related post on this today.
https://community.oracle.com/tech/developers/discussion/4502267/issue-with-spool-file-name-in-sql-plus#latest
I've encountered a new issue.
Issue description:
I have a bash shell script called test.sh which invokes sqlplus inside heredoc (shown below).
test.sh executes sample.sql file as shown below.
When sample.sql script gets executed, it spools out a file called script_for_SALES.sql.
In the same heredoc (EOF), I want to execute script_for_SALES.sql (for ease of demonstration, I just hard-coded script_for_SALES.sql file name)
I have noticed that, after executing Line 6 (sample.sql $VAR2), BASH's heredoc is skipping Line7 and Line 8 !!
As you can see in the output below, even the HELLO WORLD is not getting printed.
Any idea why ?
I want to get the dynamically generated script_for_SALES.sql script to be executed as well.
$ cat test.sh
#!/bin/bash
export VAR2=SALES
sqlplus / as sysdba <<-EOF
@/path/in/linux/post/sample.sql $VAR2 ---- Line 6
PROMPT HELLO WORLD -------------- Line 7
@/path/in/linux/post/script_for_SALES.sql ------ Line 8
exit
EOF
$ cat /path/in/linux/post/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/post/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
$ cat /path/in/linux/post/script_for_SALES.sql
alter session set container = SALES ;
select sysdate from dual;
exit