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 –
62 Comments
Awesome, thank you so much!
7 years old post, but this still requires manual activation. Why? Is there any side effect having SQL Dev automatically setting the output on for all connetions?
If it’s not recommended, maybe adding an on/off option for such in the preferences? Easier to find, easier to activate (or deactivate, for whatever reasons one may have).
Could we have it in the next release, mr. Jeff?
Not a bad question to be honest, why not always be polling for output. Consider it, ‘on the road map.’
Thanks a lot.
Thank you!
I am using sql dev 18.1 with Oracle 10g.
I am not getting DBMS_OUTPUT.put_line in output.
I have tried all suggestion.
View DBMS_OUTPUT set to connected schema.
SET SERVEROUTPUT ON;
nothing is working.
Please help
>>with Oracle 10g
That’s your problem.
Either upgrade your database (everyone wins), or downgrade your SQLDev (you win but mostly lose).
I am using oracle 10g with sql dev Version 17.2.0.188
still i am not getting any output.
please help
even i used your method and also set serveroutput on still no output
It’s because your database is 10g. You’ll need to either use an older version of SQLDev or upgrade your database to as least 11.2.0.4
Same problema here but with sqldeveloper versión is “Oracle IDE 17.2.0.188.1159”.
Seems that Oracle doesn’t care about Backward compatibility.
Sure we care. But when a database goes out of support, we no longer run test cases for it. If your version of Oracle is 10 or 15 years behind, you’ve got bigger problems then dbms output not working…
Install Toad for Oracle
it will solve)
Yes, you spend 1-5,000 dollars on a copy of Toad to help your old DB limp along…for a single db user.
Or, you could spend some time and money on upgrading your database to 18c. Then everyone who uses the DB benefits.
Hi, I set the path to startup.sql and i ran my plsql program
set serveroutput on;
declare
n number;
begin
select count(*) into n from dual;
dbms_output.put_line(n);
end;
/
Still i was unable to see the output. I am getting only this in my script output.
PL/SQL procedure successfully completed.
It’s login.sql,not startup.sql
Use this to see if it’s set
Show serveroutput,run with F5. Might want to try restarting the application too, some folks have seen ‘weird’ things happen.
I have a question: WHY THE (bad words) IS THIS DISABLED BY DEFAULT?!?!? What’s wrong with people?
Server output should be enabled by default, always and everywhere; to disable the command should be: “exec dbms_output.disable_server_output(I_AM_SURE=>1);” And Oracle’s response to this command could be one of two options:
ORA-xxxxx: User requested disable of dbms_output; ignoring request
or
“DBMS output is now disabled, dba has been notified that something weird is going on”
DBMS_OUTPUT is something used for diagnostics, it’s generally never used for applications.
Wow, quick response 🙂
That’s exactly why I think it should always be enabled – production applications shouldn’t be generating dbms output at all – if we’re generating, it’s because we want it…
Having it on by default would encourage folks to use it, and everything has a cost.
This decision was probably made 30 years ago. I can guess why it’s so today, but I’m thinking my guess is pretty close.
I guess you’re right…
Anyway, your post resolved my problem; thanks!
And thanks for the attention; your blog is awesome! 🙂
(and I’m getting used to SQL Developer – coming from SQL Navigator here; I miss the possibility to drag a column name into the script :P)
We support that too. Just expand the table in the tree to the column list, pick the column(s) you want, and drag and drop to where you want it place in your script.
If you get a popup, pick ‘Object Name.’
We even observe the click order.
Observing the click order on that is a real nice touch! 🙂
That’s not what I meant though; I work with PeopleSoft DB’s – they have over 30 thousand tables so I don’t usually open the table list in the IDE, too many to scroll through; I meant dragging the column names from the result sets (ex. select * from table, then drag the columns that I actually want).
In SQL Developer I right-click and copy the column headers to the same effect; the two caveats are that it takes longer than just “drag, drag, drag” and that I lose my clipboard contents…
I do really like the way you handled dragging from the definitions list! I will surely use this more often, thanks! One quick question; if I uncheck the “ask me every time” option, will I be stuck with my choice forever or until restart or…?
You’ll be stuck until you go into the preferences and change the behavior.
For your column drive, let’s say you currently have:
SELECT * FROM SALES:
* – no bueno
Put your cursor on SALES, hit SHIFT+F4
This gives you the desc popup. on the columns page, select the columns you want from the column_name column in the report, then drag and drop to the worksheet. That will copy and paste the column list in a comma separated values list.
Thank you!
Thanks, it works.
You’re of course, very welcome!
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.
Are you getting script output or grids?
I had to exit out of and then get back into sqldeveloper (I’m on a Windows 10 box).
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?
DBMS_OUTPUT_PUT_LINE is wrong syntax…correct syntax is DBMS_OUTPUT.PUT_LINE …
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:
fixed…
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
what version of SQLDev do you have?
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.
(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,
you can’t run PL/SQL like that
you need
begin
dbms_output.put_line(‘hello world’);
end;
/
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.
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
Thanks a lot! your comment was too helpful.
Hey thanks! That tip helped a lot.
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. 🙂
thanks, and fixed 🙂
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).
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
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 ?
Thank you so much 🙂
Great! thanks
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
Heeey thanks you!!!!
Saludos! 😉
De nada!
This only seems to work the first time you run a script which makes sense since it is only run once at startup
Hmmm, the session is shared, so that setting should persist throughout the connection. Let me go take a look and get back to you.