Enabling DBMS_OUTPUT by default in SQL Developer

thatjeffsmith SQL Developer 41 Comments

Tell Others About This Story:
LinkedIn0

DBMS_OUTPUT is one of the most well-known and least understood SYS packages. If you have questions about how it works in general, you might want to read my previous post on the subject.

What I want to show you today is a ‘trick’ that will help you bypass the step of enabling polling for DBMS_OUTPUT for all of your connections.

I don't want to have to click this button every single time I want to see my stuff!

I’m all about saving clicks, so let’s save you 3 clicks right now.

Instead of clicking the ‘Enable’ then selecting the connection then hitting ‘OK,’ we’re going to enable capture of the output be DEFAULT in SQL Developer.

We support SET SERVEROUTPUT

So try this. In your script use

SET SERVEROUTPUT ON
 
BEGIN
 Dbms_Output.Put_Line(Systimestamp);
END;
/

Now run the script using F5.

Let’s check the DBMS_OUTPUT panel.

Jeff, you lied to us!

Instead of checking the DBMS_OUTPUT panel, check the ‘Script Output’ page.

SET SERVEROUTPUT ON directs DBMS_OUTPUT to the Script Output Panel

But how do I get this to work by default?

Open a new worksheet.

Code this line

SET SERVEROUTPUT ON

Save to ‘startup.sql’

Open Tools – Preferences

Go to the Database page

On the ‘Filename for connection startup script’ – point to the ‘startup.sql’ file you just created.

Restart SQL Developer.

Open a connection and run your code.

You’ll see your DBMS_OUTPUT code with no more work!

Tell Others About This Story:
LinkedIn0

Comments 41

  1. Hi, I set the path to startup.sql and i ran my plsql program

    set serveroutput on;
    declare
    n number;
    begin
    select count(*) into n from dual;
    dbms_output.put_line(n);
    end;
    /

    Still i was unable to see the output. I am getting only this in my script output.

    PL/SQL procedure successfully completed.

    Reply
    1. thatjeffsmith Post
      Author

      It’s login.sql,not startup.sql

      Use this to see if it’s set

      Show serveroutput,run with F5. Might want to try restarting the application too, some folks have seen ‘weird’ things happen.

      Reply

  2. I have a question: WHY THE (bad words) IS THIS DISABLED BY DEFAULT?!?!? What’s wrong with people?
    Server output should be enabled by default, always and everywhere; to disable the command should be: “exec dbms_output.disable_server_output(I_AM_SURE=>1);” And Oracle’s response to this command could be one of two options:
    ORA-xxxxx: User requested disable of dbms_output; ignoring request
    or
    “DBMS output is now disabled, dba has been notified that something weird is going on”

    Reply
    1. thatjeffsmith Post
      Author

      1. Wow, quick response 🙂
        That’s exactly why I think it should always be enabled – production applications shouldn’t be generating dbms output at all – if we’re generating, it’s because we want it…

      2. thatjeffsmith Post
        Author

        Having it on by default would encourage folks to use it, and everything has a cost.

        This decision was probably made 30 years ago. I can guess why it’s so today, but I’m thinking my guess is pretty close.

      3. I guess you’re right…
        Anyway, your post resolved my problem; thanks!
        And thanks for the attention; your blog is awesome! 🙂
        (and I’m getting used to SQL Developer – coming from SQL Navigator here; I miss the possibility to drag a column name into the script :P)

      4. thatjeffsmith Post
        Author

        We support that too. Just expand the table in the tree to the column list, pick the column(s) you want, and drag and drop to where you want it place in your script.

        If you get a popup, pick ‘Object Name.’

        We even observe the click order.

      5. Observing the click order on that is a real nice touch! 🙂
        That’s not what I meant though; I work with PeopleSoft DB’s – they have over 30 thousand tables so I don’t usually open the table list in the IDE, too many to scroll through; I meant dragging the column names from the result sets (ex. select * from table, then drag the columns that I actually want).
        In SQL Developer I right-click and copy the column headers to the same effect; the two caveats are that it takes longer than just “drag, drag, drag” and that I lose my clipboard contents…
        I do really like the way you handled dragging from the definitions list! I will surely use this more often, thanks! One quick question; if I uncheck the “ask me every time” option, will I be stuck with my choice forever or until restart or…?

      6. thatjeffsmith Post
        Author

        You’ll be stuck until you go into the preferences and change the behavior.

        For your column drive, let’s say you currently have:

        SELECT * FROM SALES:

        * – no bueno

        Put your cursor on SALES, hit SHIFT+F4

        This gives you the desc popup. on the columns page, select the columns you want from the column_name column in the report, then drag and drop to the worksheet. That will copy and paste the column list in a comma separated values list.

    1. thatjeffsmith Post
      Author

  5. Hi

    I am new to oracle.
    As I type select statement in worksheet and execute my query I am seeing table but nothing is align in that.
    Can you please tell me where I am wrong?
    Thanks.

    Reply
    1. thatjeffsmith Post
      Author

  7. Hi Jeff,
    i am getting the below error while trying to execute PL/SQL script in sql developer version 4.0.3.16.

    Error starting at line : 1 in command –
    DECLARE
    a number :=5;
    BEGIN
    DBMS_OUTPUT_PUT_LINE(a);
    END;
    Error report –
    ORA-06550: line 4, column 1:
    PLS-00201: identifier ‘DBMS_OUTPUT_PUT_LINE’ must be declared
    ORA-06550: line 4, column 1:
    PL/SQL: Statement ignored
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    i am executing in the below env.
    hr [email protected]//localhost:1521/orcl.

    How to fix this?

    Reply

  8. PLS-00201: identifier ‘DBMS_OUTPUT_PUT_LINE’ must be declared
    ORA-06550: line 4, column 1:
    PL/SQL: Statement ignored
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    Reply

  9. I added the line SQL> SET SERVEROUTPUT ON to my script at the top and tried running the script with it included. I got the following error:
    Error starting at line : 1 in command –
    SQL> SET SERVEROUTPUT ON
    Error report –
    Unknown Command

    The above error is followed by: PL/SQL procedure successfully completed.
    So, I believe the script is running successfully.
    Any help would be appreciated.
    –Thank you

    Reply
    1. thatjeffsmith Post
      Author

      1. I found out where I went wrong. It is kind of a silly mistake.
        I have been giving the command:
        SQL> SET SERVEROUTPUT ON with ‘SQL>’ included, which led to the error.

        Thank you for your swift response.

    2. (I’m a new DBA)
      I set up STARTUP.SQL and saved it and restarted SQL Developer. (ver 1.5.5. Build MAIN-5969)

      3 lines are in my code window:
      CLEAR Screen
      SELECT table_name from dba_tables;
      DBMS_OUTPUT.PUT_LINE(‘Test1’);

      In the Script Output tab, the data from the select comes down fine, but for my DBMS_OUTPUT line I get “Error Report–unknown command”

      What am I still doing wrong to warrant the error?

      Thanks,

      Reply
      1. thatjeffsmith Post
        Author

  10. My SERVEROUTPUT setting in the startup script was not working. I had it in lower case and with trailing line comment:

    `set serveroutput on — Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks`

    Changing to `SET SERVEROUTPUT ON` fixed the issue.

    Reply
    1. thatjeffsmith Post
      Author

      what version?

      I just ran
      set serveroutput on

      and it worked A-OK…maybe the comment is what was the issue?

      what version are you running?

      you can use SHOW ALL to see what the settings are after you’re connected

      Reply

  13. Thanks for the tip, Jeff.

    In paragraph 4, I am pretty sure you mean “Enable”, with 1 B, not “Enbable” with 2 Bs. Thanks for the chuckle, though. 🙂

    Reply
    1. thatjeffsmith Post
      Author

  14. Nothing happens. More specifically, Script Output says “Task completed in 0.016 seconds,” but there is nothing in either the Script Output window or the Dbms Output window (Dbms Output window has a tab with the conneciton name in it, but nothing in the content pane for that tab).

    Reply

    1. Click on ‘+’ Sign in Database output window and again select the database connection name that you are running the script in. It worked for me. try it

      Reply

  15. I just tried this in SQL Developer 4.1.0.17 Build MAIN-17.29 and after restarting the Query Results / Script Results windows stopped to show after running any SQL code.
    I even deleted all settings that were in my AppData/Roaming folder, repeated it on a “clean” app and still the situation persists. Is this a bug of some sort ?

    Reply

  18. Hi,
    When try to use sys.Dbms_Output.Put_Line(Systimestamp);
    I got an error message
    Error starting at line 1 in command:
    sys.Dbms_Output.Put_Line(Systimestamp)
    Error report:
    Unknown Command

    Reply
    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *