Finding your way out of SQLDeveloper's DBMS_OUTPUT pane

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

SQL Developer 1.2.1 build 32-13:
have you ever used the DBMS_OUTPUT pane inside Oracle SQL Developer?

Unlike my friend John Scott, i like to open several SQL Worksheet against the same database connection.
This must be the reason as to why i noticed one curious fact about the DBMS_OUTPUT pane that he wasn't apparently aware of, according to a recent message interchange.

In brief, if you open multiple worksheets against the same connection, you'll notice that each one comes with an associated series of tabs, among which there is the DBMS_OUTPUT pane (see the center of the picture in the screenshots below).
According to my personal experience, the pane associated with the first worksheet where you enabled the DBMS_OUTPUT will drive all the DBMS_OUTPUT of the session, as illustrated by the following series of pictures.

The first worksheet belongs to the first session. After enabling server output and after executing the procedure, it will display the content of the buffer:

The second connection has got its own DBMS_OUTPUT pane also displaying some output:

The third worksheet however is created against the first connection and it won't display anything.


However, if we switch back to the first worksheet, we find the output of the third worksheet...


This can be explained by looking at the active oracle sessions opened by SQL Developer, where it becomes clear that multiple SQLDeveloper worksheets against the same database connection will be mapped to the same oracle session:

select sid, serial#, username, module, action
from v$session
where program = 'SQL Developer';

SID     SERIAL#   USERNAME  MODULE                      ACTION
------- --------- --------- --------------------------- -------------
116     38309     SYS       SQL Developer            
117     8352      SYS       Oracle SQL Developer        Code Insight
121     13186     YOCOYA    Oracle SQL Developer        Code Insight
125     26877     YOCOYA    SQL Developer            

4 rows selected

SQL Developer uses two distinct sessions for each connection, one is marked as "Code Insight" and is for handling the object tree, the other one is for executing the SQL scripts. I opened two db connections and this accounts for the 4 rows that you see in the report above.

Although this makes perfectly sense from a technical point of view, it can result in puzzling results, as far as DBMS_OUTPUT is concerned.

Note also that if you set server output off and on again from in the third pane, then you will redirect the output to the active tab and the other one will stop being updated.

Not a big deal, but something to be aware of.