Sample Header Ad - 728x90

Unix command substitution when using sqlplud

0 votes
1 answer
634 views
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
Asked by Tim (113 rep)
Apr 29, 2020, 11:46 PM
Last activity: Apr 30, 2020, 12:03 AM