Shell Programming and Scripting

Hi ,

I have a pl/sql procedure which takes its input from a shell script, and deletes rows from db table based on the input.
I've dbms_output.put_line statments in procedure which i want to capture and display in the script

for example, if no rows have been deleted, i have to stop executing the rest of the script.

the issue is that the dbms_output statements are not displayed on the screen even when the serveroutput is on

Can anyone give me ideas on how to implement this.

When calling put_line from other than an anonymous block, the output is buffered. To retrieve the output, use get_line.

Code:

create or replace procedure TestProc as
  2  begin
  3  dbms_output.put_line( 'This is TestProc' );
  4  end;
  5  /

Code:

cat Test
sqlplus -s scott/tiger << !
  set serveroutput on
  set feedback off
declare
  RES varchar2(256);
  S number(10);
begin
  TestProc;
  dbms_output.get_line( RES,S );
  dbms_output.put_line( RES );
end;
/
!

./Test 
This is TestProc