Unix command substitution when using sqlplud

Question

Trying to invoke a sql file from unix shells script.

ExecuteSql1.sh

#!/bin/sh
## steps to get the connection and form it.
result=`sqlplus -S ${connectURL} << EOF
SET PAGES 0 lines 800
@ /sql/testQuery.sql ${LOGFILE} 
EOF`

echo $result

output of the above script (works as expected without exception)

MODULUS BEFORE: 0 MODULUS AFTER: 16 No errors.

ExecuteSql2.sh {NOTE the starting space is PRESERVED below result variable}

#!/bin/sh
result=`sqlplus -S ${dbConnect} << EOF
      SET PAGES 0 lines 800
      @ /sql/testQuery.sql ${LOGFILE};
      EOF`

echo $result

When executing ExecuteSql2.sh, the output looks like below:

ExecuteSql2.sh: line 147: warning: here-document at line 144 delimited by end-of-file (wanted `EOF') MODULUS BEFORE: 0 MODULUS AFTER: 16 No errors. SP2-0042: unknown command "EOF" - rest of line ignored.

using, didn't know when using the `` built in command substitution with space causes this issue.

Any specific reason for this?

testQuery.sql:

SET SERVEROUTPUT ON SIZE UNLIMITED
set heading off verify off feedback off echo off

SPOOL &1

declare
 modulus NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('MODULUS BEFORE: '||modulus);

select mod(100,21) into modulus from dual;

DBMS_OUTPUT.PUT_LINE('MODULUS AFTER: '||modulus);

end;
/

SET ECHO ON FEEDBACK ON
SHOW ERRORS
SPOOL OFF

score 1 · Accepted Answer · answered Apr 30 '20 at 00:03

As per the documentation 3.6 Redirections - 3.6.6 Here Documents:

This type of redirection instructs the shell to read input from the current source until a line containing only word (with no trailing blanks) is seen. All of the lines read up to that point are then used as the standard input (or file descriptor n if n is specified) for a command.

The format of here-documents is:
[n]<<[-]word
        here-document delimiter
delimiter 

So your code should be

result=`sqlplus -S ${dbConnect} << EOF
      SET PAGES 0 lines 800
      @ /sql/testQuery.sql ${LOGFILE};
EOF
`

And note that the final "`" should be below the EOF, althoug I think is better to use the notation $() as in

result=$(sqlplus -S ${dbConnect} << EOF
      SET PAGES 0 lines 800
      @ /sql/testQuery.sql ${LOGFILE};
EOF
)

Unix command substitution when using sqlplud

Any specific reason for this?

1 Answers1