Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
Anyone who needs a multi-line version of DBMS_OUTPUT.PUT_LINE?
This is one of my favorite swiss knife utilities, a handy procedure called PUT_LINES that allows you to forget the limitation of 255 characters per line of DBMS_OUTPUT.PUT_LINE.
Download put_lines.sql source file.
The idea is to split a multi-line source string (p_str) into chunks of the specified maximum length (p_chunk), possibly at any existing newline (p_newline) or at any breaking character (p_breaks) like spaces (default), commas, semicolons, etc., whichever comes last.
A hard break at the chunk length will occur only if no suitable breaking character has been found in the second half of each line, but you can make this threshold bigger or smaller as desired (p_threshold).
In other simple terms, i am trying to split the string in a user-friendly format.
For the sake of simplicity, I've embedded a private function called RIGHTMOST as comments in the declaration section of PUT_LINES.
You can either make this function independent if you deem that it could be useful in other situations (as i think) or simply uncomment it to keep it private and compile PUT_LINES without external references.
Showing posts with label DBMS_OUTPUT. Show all posts
Showing posts with label DBMS_OUTPUT. Show all posts
Wednesday, March 12, 2008
Friday, January 25, 2008
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:
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.
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.
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
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.
Friday, August 03, 2007
A practical example of using global temporary tables within Apex: displaying dbms_output messages
It's late so i am not going to make it longer than necessary.
Some days ago i read an interesting question in the OTN forum and i threw my 50 cent advice (well, honestly it may have been worth 60 or 70 cents as i did some homework before shooting...).
In short, a user was asking for a method for display the messages sent to the DBMS_OUTPUT buffer.
This question eventually aroused my fantasy, so i wrote a generic function for storing dbms_output messages into a generic table (a table in the same schema or in another one, provided the user has INSERT privilege).
Having used up all my fantasy in the development, i decided to call the function STORE_DBMS_OUTPUT.
In my test apex application the staging table is defined as follows:
The function takes 4 parameters and returns a number:
p_owner owner of the staging table, default to current user.
p_table name of the table, mandatory
p_txt_col name of the column for storing messages, typically VARCHAR2(255)
p_cnt_col optional name of the column for storing message sequence number
The last parameter may be omitted if you already have a before-insert trigger on the table where you stuff an oracle sequence.
The function retrieves all the content in the buffer and the value it returns is the number of messages it found.
Although i wrote the function for using within an Apex page, it's completely independent from it and it can be used in different situations.
The table where the messages are going to be stored doesn't necessarily have to be a Global Temporary Table (GTT), although in that case you must devise a method for keeping it clean without interfering with other sessions and/or users.
Users who are interested in looking at a working example of dynamic SQL in combination with FORALL and bulk binds may be interested in this function too.
Note also the n-tier usage of DUAL i talked about some time ago that i use here for initializing a pl/sql table containing the subscripts, i'll probably write something in another posting on this subject.
In case you are interested in using this function inside apex, here is how i did it (please note that this techniques are still under testing).
before header processes
step 10: truncate staging table (just in case) and call the desired procedure returning dbms_output messages (conditional on request=OUTPUT)
step 20: get the dbms_output and store it in a global temporary table (conditional on request=OUTPUT), as follows:
regions
sequence 10: some region with a button branching to the same page, setting request = OUTPUT
sequence 20: report region on staging table (conditional on request=OUTPUT)
sequence 30: PL/SQL type region truncating the table (conditional on request=OUTPUT), may be omitted altogether if GTT is defined as on commit delete rows.
As you see here i have up to two truncates statements. This is just to avoid having an idle session holding some temporary segment as i related in my previous posting.
If the GTT is defined as on commit delete rows, you can probably omit both truncates, i just prefer to leave a clean table after using it.
That's it.
Some days ago i read an interesting question in the OTN forum and i threw my 50 cent advice (well, honestly it may have been worth 60 or 70 cents as i did some homework before shooting...).
In short, a user was asking for a method for display the messages sent to the DBMS_OUTPUT buffer.
This question eventually aroused my fantasy, so i wrote a generic function for storing dbms_output messages into a generic table (a table in the same schema or in another one, provided the user has INSERT privilege).
Having used up all my fantasy in the development, i decided to call the function STORE_DBMS_OUTPUT.
In my test apex application the staging table is defined as follows:
create global temporary table staging_table( id number(5,0), text varchar2(255) ) on commit delete rows /The source code of the function can be downloaded from Yocoya.com.
The function takes 4 parameters and returns a number:
p_owner owner of the staging table, default to current user.
p_table name of the table, mandatory
p_txt_col name of the column for storing messages, typically VARCHAR2(255)
p_cnt_col optional name of the column for storing message sequence number
The last parameter may be omitted if you already have a before-insert trigger on the table where you stuff an oracle sequence.
The function retrieves all the content in the buffer and the value it returns is the number of messages it found.
Although i wrote the function for using within an Apex page, it's completely independent from it and it can be used in different situations.
The table where the messages are going to be stored doesn't necessarily have to be a Global Temporary Table (GTT), although in that case you must devise a method for keeping it clean without interfering with other sessions and/or users.
Users who are interested in looking at a working example of dynamic SQL in combination with FORALL and bulk binds may be interested in this function too.
Note also the n-tier usage of DUAL i talked about some time ago that i use here for initializing a pl/sql table containing the subscripts, i'll probably write something in another posting on this subject.
In case you are interested in using this function inside apex, here is how i did it (please note that this techniques are still under testing).
before header processes
step 10: truncate staging table (just in case) and call the desired procedure returning dbms_output messages (conditional on request=OUTPUT)
step 20: get the dbms_output and store it in a global temporary table (conditional on request=OUTPUT), as follows:
declare n integer; begin n := STORE_DBMS_OUTPUT( p_owner => :OWNER, p_table => 'STAGING_TABLE', p_txt_col => 'TEXT', p_cnt_col => 'ID'); end;
regions
sequence 10: some region with a button branching to the same page, setting request = OUTPUT
sequence 20: report region on staging table (conditional on request=OUTPUT)
sequence 30: PL/SQL type region truncating the table (conditional on request=OUTPUT), may be omitted altogether if GTT is defined as on commit delete rows.
As you see here i have up to two truncates statements. This is just to avoid having an idle session holding some temporary segment as i related in my previous posting.
If the GTT is defined as on commit delete rows, you can probably omit both truncates, i just prefer to leave a clean table after using it.
That's it.
Subscribe to:
Posts (Atom)