Sqlplus Serveroutput not showing

Question

Here is my sqlplus script:

sqlplus <<EOF>> $LOGFILE $USER_NAME_PASSWORD
WHENEVER SQLERROR EXIT SQL.SQLCODE
spool $LOGFILE
set serveroutput on size 20000
show serveroutput
set echo on
set sqlblanklines off
set define off
@$1
spool off
exit
EOF
RC=$?

And here is my sql script:

DECLARE

L_COUNT NUMBER := 0;

BEGIN

   FOR i in 1..4 LOOP

      l_count := l_count + 1;

   END LOOP;

   dbms_output.put_line('Num: ' || l_count);

END;

I would expect to see the results of l_count in my $LOGFILE, but I do not. I'm sure I'm missing some set feature when I call sqlplus.

Any ideas?

Answer 1

You never actually execute your pl/sql anonymous block. You need to put a '/' after your END; statement:

DECLARE
L_COUNT NUMBER := 0;
BEGIN
   FOR i in 1..4 LOOP
      l_count := l_count + 1;
   END LOOP;
   dbms_output.put_line('Num: ' || l_count);
END;
/

Also, it's not a good idea to use both shell stdout redirection to write to the same file as specified by your SPOOL command. Pick one or the other, not both.