I am trying to call pl/sql block in unix script using sqlplus. I just tried to print a statement but nothing is getting printed and I am not getting any error as well.
Result=`sqlplus -s $TgtUsrID/$TgtPswd@$TgtServer <<eof
whenever sqlerror exit sql.sqlcode;
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
eof
`
current_time=`date`
echo " Script execution finished at $current_time"
Just need to prepend the variable(Result) with the dollar operator after the last EOF
along with an echo command such as
Result=`sqlplus -S /nolog << EOF
conn $TgtUsrID/$TgtPswd@$TgtServer
whenever sqlerror exit sql.sqlcode
set feedback off
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/
EOF`
echo $Result
echo Script execution finished at $(date)
would yield such a result
Hello World!
Script execution finished at Tue Feb 14 00:15:51 +03 2021
where quotes for the description after the last echo is redundant, and prefer using connection with /nolog as being securer, since the password of the schema would be unveiled whenever ps -ef command issued in the current case.
date" and "echo Script execution finished at $current_time", you could / should just simply "echo Script execution finished at $(date)"current_time was going to be used again somewhere.date " is the same as "echo $(date) ".