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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

    v_rec_count number:=0;
            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) 
        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);

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

closed as off-topic by Jack Douglas Jun 8 '15 at 6:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the 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
up vote 8 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 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on
  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.


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

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