Enabling DBMS_OUTPUT by default in SQL Developer

thatjeffsmith SQL Developer 30 Comments

Tell Others About This Story:

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!

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 30

    1. thatjeffsmith Post
      Author
  1. 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.

    1. thatjeffsmith Post
      Author
  2. 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 hr@//localhost:1521/orcl.

    How to fix this?

  3. 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:

  4. 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

    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,

      1. thatjeffsmith Post
        Author
  5. 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.

    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

  6. 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. 🙂

    1. thatjeffsmith Post
      Author
  7. 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).

    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

  8. 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 ?

  9. 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

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

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