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’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.
Instead of checking the DBMS_OUTPUT panel, check the ‘Script Output’ page.
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 –
55 Comments
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.