ThatJeffSmith

Enabling DBMS_OUTPUT by default in SQL Developer

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 ‘Enbable’ 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!