Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was wondering why the output is not being shown even if I set the SERVEROUPUT variable to ON.

I have the following annonymous block code:

DECLARE
    v_rec_count number:=0;
BEGIN
    for rec in (SELECT T_A.ROWID, T_A.OPEN_COUNT, T_B.OPEN_COUNT as NEW_OPEN_COUNT
            FROM T_A
            INNER JOIN T_B ON
                T_A.KEYA = T_B.KEYA
            AND T_A.KEYB = T_B.KEYB
            AND T_A.OPEN_COUNT <> T_B.OPEN_COUNT) 
    loop
        v_rec_count := v_rec_count + 1;

        update T_A t1
        set t1.OPEN_COUNT = rec.NEW_OPEN_COUNT,
            t1.UPD_USER_ID = 'batch',
            t1.UPD_DATE = SYSDATE
        where t1.rowid = rec.rowid;
    end loop;

    dbms_output.put_line ('T_A UPDATE_TOTAL: ' || v_rec_count);
END;
/

When I execute the following code, I would like to see the output of dbms_output.put_line to the sql prompt and eventually to the log file when I run the sql file via sqlplus. The output that the block does is just the "PL/SQL Procedure has successfully run".

I already set SET SERVEROUPUT ON before the block but it does not work. Any ideas?

share|improve this question
    
"I would like to see the output of dbms_output.put_line to the sql prompt and eventually to the log file when I run the sql file via sqlplus" - What are you executing it in to test? –  Phil Oct 11 '12 at 11:11
add comment

1 Answer 1

up vote 2 down vote accepted

As you can see from the output below, a test anonymous PL/SQL block works in SQL Plus as expected:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 1 21:55:35 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on
SQL> DECLARE
  2      v_rec_count number;
  3  BEGIN
  4      select count(*) into v_rec_count from dual;
  5      dbms_output.put_line ('count: ' || v_rec_count);
  6  END;
  7  /
count: 1

PL/SQL procedure successfully completed.

SQL>

I'd guess that you're not using SQL Plus to test & are testing in another tool (such as SQL Developer) and it's not set DBMS_OUTPUT.enable() or you haven't configured the tool to correctly display the output from the RDBMS.

Or, it could be the fact that SET SERVEROUPUT ON is a typo :)

share|improve this answer
    
I am executing in an sqlplus prompt in linux. yes the SET SERVEROUPUT ON was a typo error. I really did use SERVEROUTPUT cause it is noticeable if I get it wrong cause it displays a SP2-0735 error. So just perform the same thing i was writing yesterday and it worked. must be loosing it. –  Nap Oct 12 '12 at 0:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.