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

59 Comments

  1. I got a special kind of headache trying to debug a PIPELINED function… From SQL worksheet I execute SELECT * FROM pipelined_func(); ..and there is zero output, neither in dbms_output window nor in script output window.

    It almost seems that SQLDeveloper is not polling for output in this case? Output does get saved into buffer, and I see it next time I execute a normal, non-pipelined function. Perhaps there are other side-effects in work here. But dBeaver just works. SQLDev 23.1; Oracle 19c.

    • Do you have a RETURN? Then just call it from SQL like demonstrated here.

      SQL Developer has no problem showing the results

      MY_STRING
      ----------------------------------------------------------------------------------------------------
      abc

  2. 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….?

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