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!

An ad hoc example –

If the serveroutput is defined in your login script, this will be on for every Oracle session in SQL Developer.
thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

57 Comments

  1. Avatar
    Juan Hernandez Reply

    Any chance to get DBMS_OUTPUT enabled by default, each time i run an anonymous block without the need of doing this setting….?

  2. Avatar
    Andres Castillo Reply

    I do have to note that I was not able to get anything in the ‘script output’ page until I followed the procedure Jeff mentioned for ‘startup.sql’
    Once I did that, it worked.

Write A Comment