Shell Programming and Scripting

Hi,

i have one simple PL/SQL Block and i have saved it as .sql file, which i am trying to call from UNIX script.

PL/SQL block structure

Code:

CONNECT DB_NAME/PWD@Database
whenever SQLERROR EXIT 1;
Declare
..Variables...
BEGIN
--Code--
exception
END;
exit;

I have save this block as .SQL File and try to call it from UNIX script as normal way {sqlplus -s /nolog @$IFILE_PATH/CS_INSERT_QUERY.sql}. but the script is not executing as from the point it reached to call going in infinite loop and not generating the error also. Can one of you please tell me where am i doing wrong here.

the shell scripting is going in infinite loop while calling the sql file having plsql code in above mentioned structure.

or do i need to make any amendments in code while calling or in PL/SQL block.

PL/SQL Declare..BEGIN..END; block is working as expected.

Thanks in advance

To execute the block, you need to have a slash / on a line of its own, after the END; line.

Hi Ygor,

Thank for your help... that was the only thing missing.. if ound out finally after long struggle.

anyway many thanks

... which was unnecessary since it's mentioned right there in the Oracle docs:

SQL*Plus Basics

Code:

Running PL/SQL Blocks
...
...You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line.
...