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