Passing the result of an anonymous pl/sql block to a shell script

Shell Programming and Scripting
1

Hello,

Here is the code i have written to get the count of a plsql query back to the unix.

function checkforCOIDs
{
countcheck=`sqlplus -s $1/$2@$3
whenever oserror exit sql.oscode rollback
whenever sqlerror exit sql.sqlcode rollback
set serverout on size 2000;
set head off feedback off pages 0;

DECLARE
count_coid INTEGER;

BEGIN
SELECT COUNT(*) INTO count_coid
from XXX.COID_CONTROL 
where Load_Flag = 'Y' and 
Load_Date = (SELECT TO_NUMBER(EXTRACT(DAY FROM Sysdate - 1)) FROM DUAL);

dbms_output.put_line(count_coid);
END;
/
commit;
exit
eof`
echo ${countcheck}
}

I have tried everything, but still while running my script, it throws an error "checkforCOIDs[22]: 0403-057 Syntax error at line 11 : `(' is not expected."

Can anyone help me please..!!

2

Your here document syntax is incorrect.

Correction:

countcheck=`sqlplus -s $1/$2@$3 << eof
1 Like
3

Dint see the small thing..
Thank you so much Yoda..