Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
The DBMS_OUTPUT package is one that people frequently misunderstand. They misunderstand how it works, what it does, and it's limits. In this section I will address these misunderstandings. I will also address some alternative implementations that give you DBMS_OUTPUT-like functionality, but without some of the limits found in the native package.
DBMS_OUTPUT is a simple package designed to give the appearance that PL/SQL has the ability to perform simple screen I/O operations. It is designed so that it appears PL/SQL can print Hello World on your screen for example. You've seen me use it many hundreds of times in this book. An example is:
ops$tkyte@DEV816> exec dbms_output.put_line( 'Hello World' ); Hello World PL/SQL procedure successfully completed.
What you didn't see is that I had to issue a SQL*PLUS (or SVRMGRL) command in order to make this work. We can turn this screen I/O on and off like this:
ops$tkyte@DEV816> set serveroutput off ops$tkyte@DEV816> exec dbms_output.put_line( 'Hello World' ); PL/SQL procedure successfully completed. ops$tkyte@DEV816> set serveroutput on ops$tkyte@DEV816> exec dbms_output.put_line( 'Hello World' ); Hello World PL/SQL procedure successfully completed.
In reality, PL/SQL has no capability to perform screen I/O (that's why I said it was designed to give PL/SQL the appearance of being able to do this). In fact, it is SQL*PLUS that is doing the screen I/O - it is impossible for PL/SQL to write to our terminal. PL/SQL is being executed in a totally separate process, typically running on a different machine elsewhere in the network. SQL*PLUS, SVRMGRL, and other tools however, can write to our screens quite easily. You will notice that if you use DBMS_OUTPUT in your Java or Pro*C programs (or any program) the DBMS_OUTPUT data goes into the 'bit bucket', and never gets displayed. This is because your application would be responsible for displaying the output.
DBMS_OUTPUT is a package with a few entry points. The ones you will use the most are:
PUT - Puts a string, NUMBER, or DATE into the output buffer, without adding a newline.
PUT_LINE - Puts a STRING, NUMBER, or DATE into the output buffer and adds a newline.
NEW_LINE - Puts a newline into the output stream.
ENABLE/DISABLE - Enables or disables the buffering of data in the package. Effectively turns DBMS_OUTPUT on and off procedurally.
These procedures write to an internal buffer; a PL/SQL table stored in the package body of DBMS_OUTPUT. The limit the total length of a line (the sum of all bytes put into the buffer by you, without calling either PUT_LINE or NEW_LINE to terminate that line) is set to 255 bytes. All of the output your procedure generates is buffered in this table, and will not be visible to you in SQL*PLUS until after your procedure completes execution. PL/SQL is not writing to a terminal anywhere, it is simply stuffing data into a PL/SQL table.
As your procedure makes calls to DBMS_OUTPUT.PUT_LINE, the DBMS_OUTPUT package stores this data into an array (PL/SQL table), and returns control to your procedure. It is not until you are done that you will see any output. Even then, you will only see output if the client you are using is aware of DBMS_OUTPUT, and goes out of its way to print it out. SQL*PLUS for example, will issue calls to DBMS_OUTPUT.GET_LINES to get some of the DBMS_OUTPUT buffer, and print it on your screen. If you run a stored procedure from your Java/JDBC application, and expect to see the DBMS_OUTPUT output appear with the rest of your System.out.println data, you will be disappointed. Unless the client application makes a conscious effect to retrieve and print the data, it is just going into the bit bucket. We will demonstrate how to do this from Java/JDBC later in this chapter.
This fact that the output is buffered until the procedure completes, is the number one point of confusion with regards to DBMS_OUTPUT. People see DBMS_OUTPUT and read about it, and then they try to use it to monitor a long running process. That is, they'll stick DBMS_OUTPUT.PUT_LINE calls all over their code, and run the procedure in SQL*PLUS. They wait for the output to start coming to the screen, and are very disappointed when it does not (because it cannot). Without an understanding of how it is implemented, it is not clear why the data doesn't start appearing. Once you understand that PL/SQL (and Java and C external routines) running in the database cannot perform screen I/O, and that DBMS_OUTPUT is really just buffering the data in a big array, it becomes clear. This is when you should go back to the section on DBMS_APPLICATION_INFO, and read about the long operations interface! DBMS_APPLICATION_INFO is the tool you want to use to monitor long running processes, not DBMS_OUTPUT.
So, what is DBMS_OUTPUT useful for then? It is great for printing out simple reports and making utilities. See Chapter 23 on Invoker and Definer Rights for a PRINT_TABLE procedure that uses DBMS_OUTPUT to generate output like this:
SQL> exec print_table( 'select * from all_users where username = user' ); USERNAME : OPS$TKYTE USER_ID : 334 CREATED : 02-oct-2000 10:02:12 ----------------- PL/SQL procedure successfully completed.
It prints the data down the screen instead of wrapping it across. Great for printing that really wide row, which would consume lots of horizontal space, and wrap on your screen, making it pretty unreadable.
Now that we know that DBMS_OUTPUT works by putting data into a PL/SQL table, we can look further at the implementation. When we enable DBMS_OUTPUT, either by calling DBMS_OUTPUT.ENABLE, or by using SET SERVEROUTPUT ON, we are not only enabling the capture of the data, but also we are setting a maximum limit on how much data we will capture. By default, if I issue:
SQL> set serveroutput on I have enabled 20,000 bytes of DBMS_OUTPUT buffer. If I exceed this, I will receive:
begin * ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 106 ORA-06512: at "SYS.DBMS_OUTPUT", line 65 ORA-06512: at line 3
I can increase this limit via a call to SET SERVEROUTPUT (or DBMS_OUTPUT.ENABLE):
SQL> set serveroutput on size 1000000 SQL> set serveroutput on size 1000001 SP2-0547: size option 1000001 out of range (2000 through 1000000)
As you can see from the error message however, the limit is 20,000 bytes through 1,000,000 bytes. The limit of the number of bytes you can put into the buffer is somewhat less than the amount you set, perhaps much less. DBMS_OUTPUT has a simple packing algorithm it uses to place the data into the PL/SQL table. It does not put the i'th row of your output into the i'th array element, rather it densely packs the array. Array element #1 might have your first five lines of output encoded into it. In order to do this (to encode many lines into one line), they necessarily introduce some overhead. This overhead, the data they use to remember where your data is, and how big it is, is included in the byte count limit. So, even if you SET SERVEROUTPUT ON SIZE 1000000, you will get somewhat less than one million bytes of output.
Can you figure out how many bytes you will get? Sometimes yes and sometimes no. If you have a fixed size output line, every line is the same length, then the answer is yes. We can compute the number of bytes you will get exactly. If your data is of varying width, then no, we cannot calculate the number of bytes you will be able to output before you actually output it. Below, I explain the algorithm Oracle uses to pack this data.
We know that Oracle stores the data in an array. The maximum total number of lines in this array is set based upon your SET SERVEROUTPUT ON SIZE setting. The DBMS_OUTPUT array will never have more than IDXLIMIT lines where IDXLIMIT is computed as:
idxlimit := trunc((xxxxxx+499) / 500); So, if you SET SERVEROUTPUT ON SIZE 1000000, DBMS_OUTPUT will use 2,000 array elements at most. DBMS_OUTPUT will store at most 504 bytes of data in each array element, and typically less. DBMS_OUTPUT packs the data into a row in the array, in the following format:
their_buffer(1) = '<sp>NNNyour data here<sp>NNNyour data here...'; their_buffer(2) = '<sp>NNNyour data here<sp>NNNyour data here...';
So, for each line of your output, there is a 4-byte overhead for a space, and a 3-digit number. Each line in the DBMS_OUTPUT buffer will not exceed 504 bytes, and DBMS_OUTPUT will not wrap your data from line to line. So, for example, if you use the maximum line length and always write 255 bytes per line, DBMS_OUTPUT will be able to pack one line per array element above. This is because (255+4) * 2 = 518, 518 is bigger than 504, and DBMS_OUTPUT will not split your line between two of its array elements. Two lines will simply not fit in one of DBMS_OUTPUT's lines. Therefore, even though you asked for a buffer of 1,000,000 bytes, you will only get 510,000 - a little more then half of what you asked for. The 510,000 comes from the fact you are printing lines of 255 bytes, and they will allow for a maximum of 2,000 lines (remember IDXLIMIT from above); 255*2000 = 510,000. On the other hand, if you used a fixed line size of 248 bytes, they will get two lines for each of their lines, resulting in you being able to print out 248 * 2 * 2000 = 992,000 - a little more than 99 percent of what you asked for. In fact, this is the best you can hope for with DBMS_OUTPUT - 992,000 bytes of your data. It is impossible to get more printed out.
As I said previously, with a fixed size line, it is very easy to determine the number of lines you will be able to print. If you give me a number, say 79, 80, or 81 bytes per line, I can simply determine:
ops$tkyte@ORA8I.WORLD> select trunc(504/(79+4)) * 79 * 2000 from dual; TRUNC(504/(79+4))*79*2000 ------------------------- 948000 ops$tkyte@ORA8I.WORLD> select trunc(504/(80+4)) * 80 * 2000 from dual; TRUNC(504/(80+4))*80*2000 ------------------------- 960000 ops$tkyte@ORA8I.WORLD> select trunc(504/(81+4)) * 81 * 2000 from dual; TRUNC(504/(81+4))*81*2000 ------------------------- 810000
As you can see, the amount of data we can output varies widely, depending on the size of our output line!
The trouble with varying length output is that the amount of output we can produce is unpredictable. It depends on how you do the output, and the mix of line sizes DBMS_OUTPUT receives. If you output the same lines, just in a different order, you may be able to print more or less lines. This is a direct result of the packing algorithm.
This is one of the most confusing aspects of DBMS_OUTPUT. You might run your procedure once and have it produce a report of 700,000 bytes successfully, and run it then tomorrow and have it fail with ORA-20000: ORU-10027: buffer overflow at 650,000 bytes of output. This is simply due to the way DBMS_OUTPUT packs the data in the buffer. Further on in this section, we will look at some alternatives to DBMS_OUTPUT that remove this ambiguity.
A reasonable question to ask is, 'Why do they do this packing?' The reason is that when DBMS_OUTPUT was introduced in version 7.0, PL/SQL table memory allocation was very different. If you allocated a slot in a PL/SQL table, enough storage for the maximum array element size was allocated immediately. This means that since DBMS_OUTPUT uses a VARCHAR2(500), 500 bytes would be allocated for a DBMS_OUTPUT.PUT_LINE( 'hello world' ) - the same as for the output of a really big string. 2,000 lines of output would take 1,000,000 bytes of data, even if you printed out hello world 2,000 times, something that should actually take about 22 KB. So, this packing was implemented in order to prevent this over-allocation of memory in the PGA for the buffering array. In the latest releases of Oracle (8.0 and up) this is no longer the case. Array elements are dynamically sized and this packing isn't technically necessary any longer. So, you might say this is a legacy side effect from code written in prior releases.
The last thing about how DBMS_OUTPUT works I would like to mention has to do with the trimming of leading blanks on output lines. It is a mistaken belief that this is a DBMS_OUTPUT 'feature'. It is actually a SQL*PLUS 'feature' (although I know of many who disagree with the 'feature' tag on this one). To see what I mean, we can run a small test:
ops$tkyte@ORA8I.WORLD> exec dbms_output.put_line( ' hello world' ); hello world PL/SQL procedure successfully completed.
When I call DBMS_OUTPUT with ' hello world', the leading blanks are trimmed away. It is assumed that DBMS_OUTPUT is doing this but really it isn't. It is SQL*PLUS doing the trimming. The simple solution to this is to use the extended syntax on the SET SERVEROUTPUT command. The full syntax is of that command is:
set serveroutput {ON|OFF} [SIZE n] [FORMAT {WRAPPED|WORD_WRAPPED|TRUNCATED}]
The formats have the following meanings:
WRAPPED - SQL*PLUS wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.
WORD_WRAPPED - Each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*PLUS left-justifies each line, skipping all leading whitespace. This is the default.
TRUNCATED - When enabled, each line of server output is truncated to the line size specified by SET LINESIZE.
It is easiest just to see the effect of each format in action, to understand what each does:
SQL>set linesize 20 SQL>set serveroutput on format wrapped SQL>exec dbms_output.put_line( ' Hello World !!!!!' ); Hello World !!!!! PL/SQL procedure successfully completed. SQL>set serveroutput on format word_wrapped SQL>exec dbms_output.put_line( ' Hello World !!!!!' ); Hello World !!!!! PL/SQL procedure successfully completed. SQL>set serveroutput on format truncated SQL>exec dbms_output.put_line( ' Hello World !!!!!' ); Hello World PL/SQL procedure successfully completed.
By default, tools such as SQL*PLUS and SVRMGRL are DBMS_OUTPUT-aware. Most other environments are not. For example, your Java/JDBC program is definitely not DBMS_OUTPUT-aware. In this section, we'll see how to make Java/JDBC DBMS_OUTPUT-aware. The same principles used below apply equally to any programming environment. The methods I use with Java can be easily applied to Pro*C, OCI, VB, or any number of these environments.
We'll start with a small PL/SQL routine that generates some output data:
scott@TKYTE816> create or replace 2 procedure emp_report 3 as 4 begin 5 dbms_output.put_line 6 ( rpad( 'Empno', 7 ) || 7 rpad('Ename',12) || 8 rpad('Job',11) ); 9 10 dbms_output.put_line 11 ( rpad( '-', 5, '-' ) || 12 rpad(' -',12,'-') || 13 rpad(' -',11,'-') ); 14 15 for x in ( select * from emp ) 16 loop 17 dbms_output.put_line 18 ( to_char( x.empno, '9999' ) || ' ' || 19 rpad( x.ename, 12 ) || 20 rpad( x.job, 11 ) ); 21 end loop; 22 end; 23 / Procedure created. scott@TKYTE816> set serveroutput on format wrapped scott@TKYTE816> exec emp_report Empno Ename Job ----- ---------- --------- 7369 SMITH CLERK 7499 ALLEN SALESMAN ... 7934 MILLER CLERK PL/SQL procedure successfully completed.
Now, we'll set up a class to allow Java/JDBC to easily perform DBMS_OUTPUT for us:
import java.sql.*; class DbmsOutput { /* * Our instance variables. It is always best to * use callable or prepared statements, and prepare (parse) * them once per program execution, rather then once per * execution in the program. The cost of reparsing is * very high. Also, make sure to use BIND VARIABLES! * * We use three statements in this class. One to enable * DBMS_OUTPUT, equivalent to SET SERVEROUTPUT on in SQL*PLUS, * another to disable it, like SET SERVEROUTPUT OFF. * The last is to 'dump' or display the results from DBMS_OUTPUT * using system.out. * */ private CallableStatement enable_stmt; private CallableStatement disable_stmt; private CallableStatement show_stmt; /* * Our constructor simply prepares the three * statements we plan on executing. * * The statement we prepare for SHOW is a block of * code to return a string of DBMS_OUTPUT output. Normally, * you might bind to a PL/SQL table type, but the JDBC drivers * don't support PL/SQL table types. Hence, we get the output * and concatenate it into a string. We will retrieve at least * one line of output, so we may exceed your MAXBYTES parameter * below. If you set MAXBYTES to 10, and the first line is 100 * bytes long, you will get the 100 bytes. MAXBYTES will stop us * from getting yet another line, but it will not chunk up a line. * */ public DbmsOutput( Connection conn ) throws SQLException { enable_stmt = conn.prepareCall( "begin dbms_output.enable(:1); end;" ); disable_stmt = conn.prepareCall( "begin dbms_output.disable; end;" ); show_stmt = conn.prepareCall( "declare " + " l_line varchar2(255); " + " l_done number; " + " l_buffer long; " + "begin " + " loop " + " exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " + " dbms_output.get_line( l_line, l_done ); " + " l_buffer := l_buffer || l_line || chr(10); " + " end loop; " + " :done := l_done; " + " :buffer := l_buffer; " + "end;" ); } /* * ENABLE simply sets your size and executes * the DBMS_OUTPUT.ENABLE call * */ public void enable( int size ) throws SQLException { enable_stmt.setInt( 1, size ); enable_stmt.executeUpdate(); } /* * DISABLE only has to execute the DBMS_OUTPUT.DISABLE call */ public void disable() throws SQLException { disable_stmt.executeUpdate(); } /* * SHOW does most of the work. It loops over * all of the DBMS_OUTPUT data, fetching it, in this * case, 32,000 bytes at a time (give or take 255 bytes). * It will print this output on STDOUT by default (just * reset what System.out is to change or redirect this * output). */ public void show() throws SQLException { int done = 0; show_stmt.registerOutParameter( 2, java.sql.Types.INTEGER ); show_stmt.registerOutParameter( 3, java.sql.Types.VARCHAR ); for(;;) { show_stmt.setInt( 1, 32000 ); show_stmt.executeUpdate(); System.out.print( show_stmt.getString(3) ); if ( (done = show_stmt.getInt(2)) == 1 ) break; } } /* * CLOSE closes the callable statements associated with * the DbmsOutput class. Call this if you allocate a DbmsOutput * statement on the stack and it is going to go out of scope, * just as you would with any callable statement, resultset, * and so on. */ public void close() throws SQLException { enable_stmt.close(); disable_stmt.close(); show_stmt.close(); } }
In order to demonstrate its use, I've set up the following small Java/JDBC test program. Here dbserver is the name of the database server and ora8i is the service name of the instance:
import java.sql.*; class test { public static void main (String args []) throws SQLException { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbserver:1521:ora8i", "scott", "tiger"); conn.setAutoCommit (false); Statement stmt = conn.createStatement(); DbmsOutput dbmsOutput = new DbmsOutput( conn ); dbmsOutput.enable( 1000000 ); stmt.execute ( "begin emp_report; end;" ); stmt.close(); dbmsOutput.show(); dbmsOutput.close(); conn.close(); } }
Now we will test it, by first compiling it, and then running it:
$ javac test.java $ java test Empno Ename Job ----- ---------- --------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN ...
So, this shows how to teach Java to do DBMS_OUTPUT for us. Just as SQL*PLUS does, you'll have to call DbmsOutput.show() after executing any statement that might cause some output to be displayed. After we execute an INSERT, UPDATE, DELETE, or stored procedure call, SQL*PLUS is calling DBMS_OUTPUT.GET_LINES to get the output. Your Java (or C, or VB) application would call SHOW to display the results.
DBMS_OUTPUT has two major limitations that I've found:
The length of a 'line' is limited to 255 bytes. You must inject a new line at least every 255 bytes.
The total output you can produce is limited to between 200,000 bytes (if you output 1 byte per line) and 992,000 bytes (if you output 248 bytes per line). This is sufficient for many operations, but a show stopper for others, especially since the amount of output you can generate is a function of the lengths of the strings, and the order in which you print them.
So, what can we do? I'll suggest three alternatives to get around these various limits. The next two sections demonstrate these alternatives.
Sometimes the 255 byte line limit is just a nuisance. You want to print some debug statements, and the thing you are printing is 500 characters long. You just want to print it, and the format of it is not as relevant as just being able to see it. In this case, we can write a small wrapper routine. I have one permanently installed in all of my database instances, in part to get around the 255 bytes per line, and in part because DBMS_OUTPUT.PUT_LINE is 20 characters long, which is a lot of typing. I use a procedure P frequently. P is simply:
procedure p( p_string in varchar2 ) is l_string long default p_string; begin loop exit when l_string is null; dbms_output.put_line( substr( l_string, 1, 248) ); l_string := substr( l_string, 251 ); end loop; end;
It does not word-wrap output, it does nothing fancy. It simply takes a string up to 32 KB in size, and prints it out. It will break my large strings into many strings of 248 bytes each (248 being the 'best' number we calculated above, giving us the maximum output), and output them. It will change the data (so it is not suitable for increasing the line width of a routine that is creating a flat file), and will cause my one line of data to be printed on perhaps many lines.
All it does is solve a simple problem. It removes the error message:
ops$tkyte@ORA8I.WORLD> exec dbms_output.put_line( rpad('*',256,'*') ) BEGIN dbms_output.put_line( rpad('*',256,'*') ); END; * ERROR at line 1: ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line ORA-06512: at "SYS.DBMS_OUTPUT", line 99 ORA-06512: at "SYS.DBMS_OUTPUT", line 65 ORA-06512: at line 1
from occurring when I am just printing some debug, or a report.
A more robust method of getting around this limit, especially useful if you are creating a flat file data dump, is to not use DBMS_OUTPUT at all, but rather to use UTL_FILE and write directly to a file. UTL_FILE has a 32 KB limit per line of output, and does not have a byte limit on the size of a file. Using UTL_FILE, you can only create a file on the server so it is not appropriate if you were using SQL*PLUS on a network-connected client, and spooling to a local file on the client. If your goal was to create a flat file for data loading, and creating the file on the server is OK, UTL_FILE would be the correct approach.
So, this covers two of the three alternatives, now for the last one.
This is a general-purpose solution that works well in all environments. What we will do here is reinvent the wheel, only we'll invent a 'better' wheel. We will create a DBMS_OUTPUT-like package that:
Has a 4,000 byte per line limit (this is a SQL limit unfortunately, not a PL/SQL limit).
No limit on the number of output lines.
Can be spooled on the client, like DBMS_OUTPUT.
SQL*PLUS will not blank-trim the front of the string in any mode.
Can be fetched into a resultset on a client using a cursor (the output will be available via a query).
We'll begin by creating a SQL type. This type will be our DBMS_OUTPUT buffer. Since it is a SQL type, we can SELECT * from it easily. Since virtually everything can do a SELECT *, any tool should be able to display our output easily.
ops$tkyte@ORA8I.WORLD> create or replace type my_dbms_output_type 2 as table of varchar2(4000) 3 / Type created.
Now we move on to the specification for our DBMS_OUTPUT-like package. This package is set up much like the real DBMS_OUTPUT. It does not have the routines GET_LINE and GET_LINES. These will not be needed, given our implementation. The routines PUT, PUT_LINE, and NEW_LINE work just like their counterparts in DBMS_OUTPUT. The functions GET, FLUSH, and GET_AND_FLUSH are new - they have no counterpart in DBMS_OUTPUT. These routines will be used to retrieve the output once the stored procedure has executed. The function GET will simply return the buffered data, but it will not 'erase' it. You can call GET over and over to retrieve the same buffer (DBMS_OUTPUT always flushes the buffer). The function FLUSH allows you to reset the buffer, in other words empty it out. The function GET_AND_FLUSH, as you might guess, returns the buffer, and clears it out - the next calls to this package will function against an empty buffer:
tkyte@TKYTE816> create or replace package my_dbms_output 2 as 3 procedure enable; 4 procedure disable; 5 6 procedure put( s in varchar2 ); 7 procedure put_line( s in varchar2 ); 8 procedure new_line; 9 10 function get return my_dbms_output_type; 11 procedure flush; 12 function get_and_flush return my_dbms_output_type; 13 end; 14 / Package created.
We will be using some of the methods we discussed Chapter 20 on Using Object Relational Features, specifically the capability of being able to SELECT * from PLSQL_FUNCTION, which is how our DBMS_OUTPUT package will work. The functions you are most interested in are the ENABLE, DISABLE, PUT, PUT_LINE, and NEW_LINE routines. These work more or less like their DBMS_OUTPUT counterparts, the major difference being that ENABLE takes no parameters, and that MY_DBMS_OUTPUT is enabled by default (whereas DBMS_OUTPUT is disabled by default). You are limited by the amount of RAM you can allocate on your system (so beware!). Next, we implement the package body. The implementation of this package is very straightforward. We have a package global variable that is our output buffer. We add lines of text to it and extend this variable when necessary. To flush it, we assign an empty table to it. As it is so straightforward, it is presented here without further comment:
tkyte@TKYTE816> create or replace package body my_dbms_output 2 as 3 4 g_data my_dbms_output_type := my_dbms_output_type(); 5 g_enabled boolean default TRUE; 6 7 procedure enable 8 is 9 begin 10 g_enabled := TRUE; 11 end; 12 13 procedure disable 14 is 15 begin 16 g_enabled := FALSE; 17 end; 18 19 procedure put( s in varchar2 ) 20 is 21 begin 22 if ( NOT g_enabled ) then return; end if; 23 if ( g_data.count <> 0 ) then 24 g_data(g_data.last) := g_data(g_data.last) || s; 25 else 26 g_data.extend; 27 g_data(1) := s; 28 end if; 29 end; 30 31 procedure put_line( s in varchar2 ) 32 is 33 begin 34 if ( NOT g_enabled ) then return; end if; 35 put( s ); 36 g_data.extend; 37 end; 38 39 procedure new_line 40 is 41 begin 42 if ( NOT g_enabled ) then return; end if; 43 put( null ); 44 g_data.extend; 45 end; 46 47 48 procedure flush 49 is 50 l_empty my_dbms_output_type := my_dbms_output_type(); 51 begin 52 g_data := l_empty; 53 end; 54 55 function get return my_dbms_output_type 56 is 57 begin 58 return g_data; 59 end; 60 61 function get_and_flush return my_dbms_output_type 62 is 63 l_data my_dbms_output_type := g_data; 64 l_empty my_dbms_output_type := my_dbms_output_type(); 65 begin 66 g_data := l_empty; 67 return l_data; 68 end; 69 end; 70 / Package body created.
Now, in order to make this package useful, we need some method of getting at the buffer easily. You can call MY_DBMS_OUTPUT.GET or GET_AND_FLUSH, and retrieve the object type yourself, or you can use one of the two views below. The first view, MY_DBMS_OUTPUT_PEEK, provides a SQL interface to the GET routine. It allows you to query the output buffer over and over again, in effect, allowing you to 'peek' into the buffer without resetting it. The second view, MY_DBMS_OUTPUT_VIEW, allows you to query the buffer once - any subsequent calls to PUT, PUT_LINE, NEW_LINE, GET, or GET_AND_FLUSH will work on an empty output buffer. A SELECT * FROM MY_DBMS_OUTPUT_VIEW is similar to calling DBMS_OUTPUT.GET_LINES. It resets everything:
tkyte@TKYTE816> create or replace 2 view my_dbms_output_peek ( text ) 3 as 4 select * 5 from TABLE ( cast( my_dbms_output.get() 6 as my_dbms_output_type ) ) 7 / View created. tkyte@TKYTE816> create or replace 2 view my_dbms_output_view ( text ) 3 as 4 select * 5 from TABLE ( cast( my_dbms_output.get_and_flush() 6 as my_dbms_output_type ) ) 7 / View created.
Now we are ready to demonstrate how this works. We will run a procedure to generate some data into the buffer, and then see how to display and interact with it:
tkyte@TKYTE816> begin 2 my_dbms_output.put_line( 'hello' ); 3 my_dbms_output.put( 'Hey ' ); 4 my_dbms_output.put( 'there ' ); 5 my_dbms_output.new_line; 6 7 for i in 1 .. 20 8 loop 9 my_dbms_output.put_line( rpad( ' ', i, ' ' ) || i ); 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed. tkyte@TKYTE816> select * 2 from my_dbms_output_peek 3 / TEXT ------------------------------------------------------------------ hello Hey there 1 2 ... 19 20 23 rows selected.
The interesting thing to note here is that SQL*PLUS, not being aware of MY_DBMS_OUTPUT, will not display the results automatically. You need to help it along, and execute a query to dump the results.
Since we are just using SQL to access the output, it should be easy for you to rewrite your own DbmsOutput Java/JDBC class. It will be a simple ResultSet object, nothing more. As a last comment on this snippet of code, the output buffer is still there waiting for us:
tkyte@TKYTE816> select * 2 from my_dbms_output_peek 3 / TEXT ------------------------------------ hello Hey there 1 2 ... 19 20 23 rows selected.
and not only is it waiting for us, we also can WHERE on it, sort it, join it, and so on (like any table could be):
tkyte@TKYTE816> select * 2 from my_dbms_output_peek 3 where text like '%1%' 4 / TEXT -------------------------------------- 1 10 11 ... 18 19 11 rows selected.
Now, if this is not the desired behavior (to be able to query and re-query this data) we would SELECT from MY_DBMS_OUTPUT_VIEW instead:
tkyte@TKYTE816> select * 2 from my_dbms_output_view 3 / TEXT --------------------------------- hello Hey there 1 ... 19 20 23 rows selected. tkyte@TKYTE816> select * 2 from my_dbms_output_view 3 / no rows selected
In this fashion, we get to see the data only once.
This new implementation of DBMS_OUTPUT raises the 255 bytes per line limit to 4,000 bytes per line, and effectively removes the size limitation on the number of total output bytes (you are still limited by available RAM on your server though). It introduces some new functionality (you can query your output, sort it, and so on) as well. It removes the SQL*PLUS default feature of blank-trimming. Lastly, unlike UTL_FILE, the results of MY_DBMS_OUTPUT can be spooled to a client file in the same way DBMS_OUTPUT output could, making it a viable replacement for client-side functions.
You might ask why I used an object type instead of a temporary table in this implementation. The answer is one of code, and overhead. The amount of code to manage the temporary table, to have at least an additional column to remember the proper order of the data, as compared to this simple implementation, is large. Also, a temporary table incurs some amount of I/O activities and overhead. Lastly, it would be hard to implement the 'flushing view' effect I have above, whereby we empty the output buffer automatically simply by selecting from it. In short, using the object type lead to a lighter weight implementation. If I planned on using this to generate tens of MB of output, I might very well reconsider my choice of buffering mechanisms, and use a temporary table. For moderate amounts of data, this implementation works well.
In this section, we have covered how the DBMS_OUTPUT package is actually implemented. Now that you know how it works, you will not become a victim of the side effects of this. You are now able to anticipate that you won't get the buffer size you asked for, and that the size of this output buffer will seem arbitrary at times. You'll be aware that it is not possible to produce an output line that exceeds 255 bytes without a newline. You know that you cannot see DBMS_OUTPUT output until after the procedure or statement completes execution, and even then, only if the environment you are using to query the database supports DBMS_OUTPUT.
In addition to gaining an understanding of how DBMS_OUTPUT works, we have also seen how to solve many of the major limitations, typically by using other features to accomplish our goals. Solutions such as UTL_FILE to produce flat files and simple functions like P to not only save on typing, but also to print larger lines. In the extreme case, we looked implementing your own equivalent functionality that does not suffer from some of the limits.
DBMS_OUTPUT is a good example of how something seemingly trivial can, in fact, be a very complex piece of software with unintended side effects. When you read through the DBMS_OUTPUT documentation in Oracle's Supplied PL/SQL Packages Reference guide, it sounds so simple and straightforward. Then issues like the total number of output bytes you can generate, and so on, crop up. Knowledge of how the package works helps us avoid these issues, either by just being aware that they are there, or by using alternative methods to implement our applications.