Skip to Main Content
  • Questions
  • how to get around Dbms_output limitations

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, vijay .

Asked: May 02, 2000 - 1:21 pm UTC

Answered by: Tom Kyte - Last updated: April 29, 2009 - 8:31 am UTC

Category: - Version:

Viewed 10K+ times! This question is

You Asked

dbms_output.put_line has limitation of 255 characters
per line. How can I get out of this limitation, so that I can
use dbms_output.put_line to have 480 character per line



and we said...



Depending on whether the output must be on a single line (in which case the answer is "dbms_output simply cannot do that") I have 3 ideas that might be of use.

1) This is what I do. I have my own procedure "p" that I call (dbms_output.put_line is so long anyway). P always does:


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, 250 ) );
l_string := substr( l_string, 251 );
end loop;
end;

it avoids the 255 byte issue but does not put the output on a single line.

2) use utl_file and write directly to a file. utl_file (added in 7.3.3) has a 1022 byte limit/line (raised to 32k/line in 8.0.5)

3) use a plsql table type and a select -- write your own dbms_output (that has no 1,000,000 byte limit and limits the linesize to 2000 bytes/4000 bytes in Oracle7/8, not 255). I tested this in 7.3.4 and it works well. it would look like this:

tkyte@ORA734.WORLD> create or replace package my_dbms_output
2 as
3 procedure put( s in varchar2 );
4 procedure put_line( s in varchar2 );
5 procedure new_line;
6
6 function get_line( n in number ) return varchar2;
7 pragma restrict_references( get_line, wnds, rnds );
8
8 function get_line_count return number;
9 pragma restrict_references( get_line_count, wnds, rnds, wnps );
10
10 pragma restrict_references( my_dbms_output, wnds, rnds, wnps, rnps );
11 end;
12 /

Package created.


thats our interface, much like the existing dbms_output (but no disable/enable -- this is always enabled). We implement the package body as such:

tkyte@ORA734.WORLD> create or replace package body my_dbms_output
2 as
3
3 type Array is table of varchar2(4000) index by binary_integer;
4 g_data array;
5 g_cnt number default 1;
6
6 procedure put( s in varchar2 )
7 is
8 begin
9 if ( g_data.last is not null ) then
10 g_data(g_data.last) := g_data(g_data.last) || s;
11 else
12 g_data(1) := s;
13 end if;
14 end;
15
15 procedure put_line( s in varchar2 )
16 is
17 begin
18 put( s );
19 g_data(g_data.last+1) := null;
20 end;
21
21 procedure new_line
22 is
23 begin
24 put( null );
25 g_data(g_data.last+1) := null;
26 end;
27
27 function get_line( n in number ) return varchar2
28 is
29 l_str varchar2(4000) default g_data(n);
30 begin
31 g_data.delete(n);
32 return l_str;
33 end;
34
34 function get_line_count return number
35 is
36 begin
37 return g_data.count+1;
38 end;
39
39 end;
40 /

Package body created.


The reason we use the pragmas on the get_line and get_line_count is so we can "select * from PLSQL_TABLE" to print the data (we don't want to use dbms_output to print for obvious reasons ;)

We can create a view on a table with lots of rows (all_objects is typically good with 1,000 or more rows in most cases) as such:

tkyte@ORA734.WORLD>
tkyte@ORA734.WORLD> create or replace view my_dbms_output_view
2 as
3 select rownum lineno, my_dbms_output.get_line( rownum ) text
4 from all_objects
5 where rownum < ( select my_dbms_output.get_line_count from dual );

View created.


That view will

- find the number of lines to print from my_dbms_output.get_line_count
- invoke the get_line procedure with rownum for each line of output we generated


To test it and see how it works, we:


tkyte@ORA734.WORLD> begin
2 my_dbms_output.put_line( 'Hello World' );
3 my_dbms_output.put_line( ' hi there' );
4 my_dbms_output.put( 'This is one line on many ' );
5 my_dbms_output.put( ' calls to put,' );
6 my_dbms_output.put( ' just to see it working' );
7 my_dbms_output.new_line;
8 my_dbms_output.put( 'end of data...' );
9 end;
10 /

PL/SQL procedure successfully completed.

tkyte@ORA734.WORLD> select * from my_dbms_output_view;

LINENO TEXT
---------- --------------------------------------------------------------
1 Hello World
2 hi there
3 This is one line on many calls to put, just to see it working
4 end of data...

tkyte@ORA734.WORLD> select * from my_dbms_output_view;
no rows selected



So, after we run the block, we have to select * from the view (or just select TEXT from the view to get the data we really want to dump -- you would "set heading off, set pagesize 10000, set feedback off, spool somefile, select text from view" to capture the output). The very next time we try to select * from the view -- its empty (it empties itself as it fetches)

Another test -- to show that after getting empty it works again:


tkyte@ORA734.WORLD> begin
2 for x in ( select rpad( ' ', rownum, ' ' ) || username data from all_users where rownum < 11 )
3 loop
4 my_dbms_output.put_line( x.data );
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

tkyte@ORA734.WORLD>
tkyte@ORA734.WORLD> select * from my_dbms_output_view;

LINENO TEXT
---------- --------------------------------------------------
1 SYS
2 SYSTEM
3 DBSNMP
4 SCOTT
5 TKYTE
6 USERA
7 USERB
8

8 rows selected.

tkyte@ORA734.WORLD>

(this little trick has other uses -- you can now select * from plsql_table in other places knowing this. Have you ever wanted a variable length in list? eg: select * from T where x in ( "some string of variables, how many you don't know until run time" ) )



and you rated our response

  (34 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

how to get around Dbms_output limitations

March 15, 2001 - 3:40 pm UTC

Reviewer: Cauvery Kodira from Columbia, MD USA

I have been trying in vain to get some good output on my client machine - the results I get from spooling are too ugly. This pre-created package by Tom really helps.
Thanks

Great workaround

April 03, 2001 - 7:30 am UTC

Reviewer: Tony van der Prijt from Netherlands

Hi there,

I've been fiddling with the same issue and this answer really saved me from the hassle of finding it out myself (if i even would think of it...)

Thnx

formatting

October 05, 2001 - 12:44 pm UTC

Reviewer: A reader

SQL>  select lineno from my_dbms_output_view;

    LINENO
----------
##########
##########
##########
##########
##########
##########
##########
##########
##########
##########
##########


SQL> desc my_dbms_output_view;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LINENO                                             NUMBER
 TEXT                                               VARCHAR2(4000)


format settings:

column lineno format10

a)How should I format the output from this view( when I format the above is the output is what I get for lineno(as there is no column length declared.

b)how should I get unwrapped output from this view

 

Tom Kyte

Followup  

October 05, 2001 - 3:03 pm UTC

column lineno format 999999999

lineno is a number, don't use a character string format on it.

set linesize 5000
set trimspool on
select * from my_dbms_utput_view order by lineno;


will do it.

whats going wrong

October 05, 2001 - 1:09 pm UTC

Reviewer: A reader

SQL> begin
  2          my_dbms_output.put_line( 'Hello World' );
  3          my_dbms_output.put_line( '    hi there' );
  4          my_dbms_output.put( 'This is one line on many ' );
  5          my_dbms_output.put( ' calls to put,' );
  6          my_dbms_output.put( ' just to see it working' );
  7          my_dbms_output.new_line;
  8          my_dbms_output.put( 'end of data...' );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from my_dbms_output_view;

    LINENO
----------
TEXT
--------------------------------------------------------------------------------
         1
Hello World

         2
    hi there

         3
This is one line on many  calls to put, just to see it working


    LINENO
----------
TEXT
--------------------------------------------------------------------------------
         4
end of data...


SQL> /

no rows selected

SQL> /

no rows selected

What is the reason that we are able to see the data in the view only once, and when wanting to see the next time the view is empty.
 

Tom Kyte

Followup  

October 05, 2001 - 3:05 pm UTC

see line 31 of the packge body -- i delete it after getting it on the assumption that once you've seen it, you've seen it. If you don't like that, get rid of line 31.

Correct the Error in the above package spec

October 06, 2001 - 10:15 am UTC

Reviewer: Nag

Tom
I find pragma restrict_references( my_dbms_output, wnds, rnds, wnps, rnps );
in package specification.

But I dont find the relevant procedure or function in the package body.

Try compiling your package exactly the way you have it avertised.

thank you


Tom Kyte

Followup  

October 06, 2001 - 12:15 pm UTC

No, you try compiling as I have it adverstised -- I already compiled it, that is a cut and paste from sqlplus. It compiles in all versions of Oracle I have.

my_dbms_output is the NAME OF THE PACKAGE. I am specifying the purity level of the package itself. It is valid, complete and correct.

Why do we have to define the purity level for the package

October 06, 2001 - 1:50 pm UTC

Reviewer: Nag

Tom

We are used to specifying the purity levels for functions, which we call in sql's in our package. But we have never declared purity level for the packages itself.

What are the advantages of doing so, actually why should we do so, what is the downside of not doing so.

I have heard that purity levels have been relaxed in 8i , but I see that you still are specifying the purity levels, something is missing here.

Thank you

Tom Kyte

Followup  

October 06, 2001 - 2:31 pm UTC

Look at the example, it was from 7.3.4 (see my prompt, I always have the version in it).

I did it in 7.3.4 and 8.0 in order to help narrow down where pragma violations where happening.  Consider:

ops$ora734@ORA734.WORLD> create or replace package my_pkg
  2  as
  3      function foo return number;
  4      pragma restrict_references( foo, wnds, rnds, wnps, rnps );
  5  end;
  6  /

Package created.

ops$ora734@ORA734.WORLD> 
ops$ora734@ORA734.WORLD> create or replace package body my_pkg
  2  as
  3  
  3  function foo return number
  4  is
  5  begin
  6      return 0;
  7  end;
  8  
  8  
  8  begin
  9      update emp set ename = ename;
 10  end;
 11  /

Warning: Package Body created with compilation errors.

ops$ora734@ORA734.WORLD> show err
Errors for PACKAGE BODY MY_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/1      PLS-00452: Subprogram 'FOO' violates its associated pragma

<b>obviously FOO itself doesn't violate its pragma -- it doesn't do anything.  So whats the problem?  Add the other pragma</b>


ops$ora734@ORA734.WORLD> create or replace package my_pkg
  2  as
  3      pragma restrict_references( my_pkg, wnds, rnds, wnps, rnps );
  4  
  4      function foo return number;
  5      pragma restrict_references( foo, wnds, rnds, wnps, rnps );
  6  
  6  end;
  7  /

Package created.

ops$ora734@ORA734.WORLD> 
ops$ora734@ORA734.WORLD> create or replace package body my_pkg
  2  as
  3  
  3  function foo return number
  4  is
  5  begin
  6      return 0;
  7  end;
  8  
  8  
  8  begin
  9      update emp set ename = ename;
 10  end;
 11  /

Warning: Package Body created with compilation errors.

ops$ora734@ORA734.WORLD> show err
Errors for PACKAGE BODY MY_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/5      PLS-00452: Subprogram 'MY_PKG' violates its associated pragma

Ahh, now its a tad more clear where the error is.  I just did it as a matter of habit to help me track down where the pragma was really being violated. 

Drilling down into some basics

October 06, 2001 - 5:20 pm UTC

Reviewer: Nag

1.What is the actual meaning of Pragma ( in the given context)

2.What does pragma restrict references mean( we know that they are help ful in avoiding functions from making changes in the database), but , can you put it in your words.

3.A wee bit silly , but what is problem with functions changing the database.

4.O.k. in 7.3.4 we had pragma restrict references, where we avoided making changes to the database using a functional call in sql, because that is not desirable.

But now in 8i we have relaxed the purity levels, what does this mean, can we make changes to the database using functional calls in SQL.

5.By purity levels do we mean the wnds, rnds, wnps, rnps , or something else.

6. 3/5 PLS-00452: Subprogram 'MY_PKG' violates its associated pragma

In the given example how is my_pkg violating its associated pragma. What is its associated pragma.

My questions deal with a lot of basics, but believe me TOM, a deluge of developers are living and are stuck with these doubts in there mind, but they feel lazy or afraid to ask someone who has full knowledge like you.



Some clarification needed here.

Tom Kyte

Followup  

October 07, 2001 - 4:31 pm UTC

1) a pragma is a compiler directive.  It alters the default behavior of the compiler.

Normally the compiler really, honestly just doesn't care if we update the database in our procedures.  It doesn't care.  However, if I ASSERT via the pragma restrict_references that my code DOESN'T update the database (via WNDS) and the compiler catches me lying to it (it sees that I do in fact have the ability to update the database in my procedure), it'll fail the compilation.  

Hence, when you assert the purity level, you're asking the compiler to verify that for you.

Without the pragma in versions 7.x and 8.0, you would not be able to invoked packaged procedures from SQL -- thats the "helpful" part of them.  They allow packaged procedures to be called from sql.

2) see 1

3) In 8i we made is so you did not have to SPECIFY the purity level (we compute it a compile time).  

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function foo return number
  2  as
  3  begin
  4     update emp set ename = ename;
  5      return sql%rowcount;
  6  end;
  7  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select foo from dual;
select foo from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "OPS$TKYTE.FOO", line 4
ORA-06512: at line 1

you still cannot do that UNLESS the function is an autonomous transaction (and even then -- you had better be really really smart about what you are doing).

The reason this is BAD is easily demonstrated:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_users where rownum <= 10;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table cnt ( x int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into cnt values (0);
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function foo return number
  2  as
  3          pragma autonomous_transaction;
  4          l_x number;
  5  begin
  6          update cnt set x = x+1 returning x into l_x;
  7          commit;
  8  
  9          return l_x;
 10  end;
 11  /
Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where user_id = foo;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'



ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from cnt;

         X
----------
        10

<b>no surprises there -- 10 rows, cnt = 10, seems OK right.  Seems predicable right.  No, not really:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(user_id);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where user_id = foo;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)



ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from cnt;

         X
----------
        12

<b>Now we did it 2 times for that query.  Why two?  I don't know, I don't really care (truly not our business, they just called it two times to do the index processing).  No, the result is not deterministic.  What if the optimizer decides to change the plan again later?</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where user_id = foo;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
DBSNMP                                 16 28-AUG-01


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=16)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=1 Bytes=16)



ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from cnt;

         X
----------
        22

<b>Which is is free to do.  This is why calling functions from sql that have side effects is typically a really truly BAD IDEA. </b>

5) yes. we mean that.

6) 

Look at the spec:

ops$ora734@ORA734.WORLD> create or replace package my_pkg
  2  as<b>
  3      pragma restrict_references( my_pkg, wnds, rnds, wnps, rnps );
  4  </b>

I asserted that this package WNDS -- it writes no database state.  Now, look at the body:

ops$ora734@ORA734.WORLD> create or replace package body my_pkg
  2  as
  3  
  3  function foo return number
  4  is
  5  begin
  6      return 0;
  7  end;
  8  
  8  <b>
  8  begin
  9      update emp set ename = ename;
 10  end;</b>

 11  /

the elaboration code for this package DOES write the database state obviously.  I lied, the compiler caught me.  A more subtle way for the package itself to write the database state would be something like:

create or replace package my_pkg
as
     x number default foo;
      ......
end;

here foo is a function, foo updates the database, hence the package my_pkg updates the data, hence EVERY function in my_pkg updates the database, hence no function in my_pkg can be called from SQL. 

how com purity levels have been relaxed in 8i

October 07, 2001 - 4:30 pm UTC

Reviewer: A reader

It is interesting to note that , purity levels which were of such prime importance in 7.3.4 , are not as important now.

What is the trade off, what are we trading the purity levels for.

How come something so important in previous versions , is not important now.

Tom Kyte

Followup  

October 07, 2001 - 4:37 pm UTC

The old rules:

? The function cannot modify database tables; therefore, it cannot
execute an INSERT, UPDATE, or DELETE statement.

? Functions that read or write the values of packaged variables
cannot be executed remotely or in parallel.

? Only functions called from a SELECT, VALUES, or SET clause
can write the values of packaged variables.

? The function cannot call another subprogram that breaks one of
the foregoing rules. Also, the function cannot reference a view
that breaks one of the foregoing rules. (Oracle replaces references
to a view with a stored SELECT operation, which can include
function calls.)

The new rules:

o A function called from a query or DML statement may not end the current
transaction, create or rollback to a savepoint, or ALTER the system or session.

o A function called from a query (SELECT) statement or from a parallelized DML
statement may not execute a DML statement or otherwise modify the database.

o A function called from a DML statement may not read or modify the particular
table being modified by that DML statement.

As software evolves, features are added. see the comment above this one for why it can be a really truly bad idea most of the time.

The rules were mostly relaxed for functions called from DML (insert/update/delete) since they behave much in the same way a trigger would. It really didn't affect functions called from queries which is where 99% of the times this functionality is used.

Excellent

May 14, 2002 - 10:55 am UTC

Reviewer: Srimal from USA

Hello Mr Tom,

The solution that you have provided for getting around this problem is just excellent!

Thanks a lot

avoid waiting for the code to complete

September 19, 2002 - 3:36 am UTC

Reviewer: Guy from Israel

Tom,

When I execute a Pl/Sql code which includes DBMS_OUTPUT.PUT_LINE, the output is flushed only after the run has eneded.

Is there a way to circumvent this, and view the messages during the run?

Thanks.

Tom Kyte

Followup  

September 19, 2002 - 7:53 am UTC

If you have my book -- i describe in painstaking detail how dbms_output works (so you'll fully understand why with DBMS_OUTPUT the answer is "no, you cannot").

You can use UTL_FILE and fopen in append/ put a line/ fclose and "tail -f" the file.

You can use dbms_application_info.set_session_longops to report progress in real time via a v$ view.

In 9i, you can use a pipelined function:

ps$tkyte@ORA920.LOCALHOST> create or replace type myScalarType as object
  2  ( dt timestamp, msg varchar(40) )
  3  /

Type created.

ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> create or replace type myTableType as table of myScalarType
  2  /

Type created.

ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> create or replace function virtual_table( p_num_rows in number )
  2  return myTableType
  3  PIPELINED    -- NOTE the pipelined keyword
  4  is
  5  begin
  6          pipe row( myScalarType( systimestamp, 'Starting...' ) );
  7      for i in 1 .. p_num_rows
  8      loop
  9                  pipe row( myScalarType( systimestamp, 'processing row ' || i ) );
 10                  dbms_lock.sleep( 1 );
 11      end loop;
 12
 13      return; -- note return with no value!
 14  end;
 15  /

Function created.

ops$tkyte@ORA920.LOCALHOST>
ops$tkyte@ORA920.LOCALHOST> set arraysize 1
ops$tkyte@ORA920.LOCALHOST> select * from TABLE( virtual_table(5) );

DT
---------------------------------------------------------------------------
MSG
----------------------------------------
18-SEP-02 06.55.52.238409 AM
Starting...

18-SEP-02 06.55.52.242138 AM
processing row 1

18-SEP-02 06.55.53.260283 AM
processing row 2

18-SEP-02 06.55.54.280308 AM
processing row 3

18-SEP-02 06.55.55.300272 AM
processing row 4

18-SEP-02 06.55.56.320289 AM
processing row 5


6 rows selected.


you cannot see it here -- but those rows appeared on my screen as they were produced -- I saw the first row long before the last. 

ask for improvement

May 12, 2003 - 4:26 am UTC

Reviewer: Peter from Hungary, Europe

Hi Tom,

your my_dbms_output is great! Especially because of its amazing simplicity.
I tried to improve it: to make it session-sensitive (thus the simplicity gone...:(
Please, look through this code and write me how should it be improved (either technically or in functionality). Yes, I'm sure, it can be several ways. But how it should be?
(I removed the last ugly NULL-line in the case where the last writing is put_line.)

* I needed two values (the session identifier and .g_data("session ID").LAST) several times, that is why I created dop_assistant -- but it doesn't seem to be the best solution. How could it be eliminated (keeping the code centralized)?

* I tried to centralize the type definitions (VARCHAR2(80) for dop_assistant.sid and .last and fullsid and VARCHAR2(4000) etc.) to make the code more flexible but I couldn't: if I put "SID dop.fullsid" or anything similar (e.g. some %type ...) into dop_assistant header I get an error message like: "PLS-00201: identifier 'DOP_TYPES.FULLSID' must be declared". How can this be solved? (Among others I created a dop_types package to hold those four types (txt_row, txt_table, fullsid and session_table) but I couldn't use it in the way I want -- I left some trace about this in the code to inform you about what I wanted.)

I use Oracle 9iR2.

Here is the code. (As you will see I used several things learned from AskTom. Thanks for all!)

CREATE OR REPLACE TYPE dop_assistant AS OBJECT (
SID /*dop_types.fullsid, --*/VARCHAR2(80),
LAST /*dop_types.fullsid, --*/VARCHAR2(80),

constructor FUNCTION dop_assistant RETURN self AS result,

PRAGMA RESTRICT_REFERENCES( dop_assistant, wnds, wnps )
);
/
CREATE OR REPLACE PACKAGE Dop AS

SUBTYPE txt_row IS VARCHAR2(4000);
TYPE txt_table IS TABLE OF txt_row INDEX BY BINARY_INTEGER;

SUBTYPE fullsid IS VARCHAR2(80);
TYPE session_table IS TABLE OF txt_table INDEX BY fullsid;

g_data /*dop_types.*/session_table;

PROCEDURE put( s IN VARCHAR2 );
PROCEDURE p ( s IN VARCHAR2 );

PROCEDURE put_line( s IN VARCHAR2 );
PROCEDURE pl ( s IN VARCHAR2 );

PROCEDURE new_line;

FUNCTION get_line( n IN NUMBER ) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES( get_line, wnds );

FUNCTION get_line_count RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES( get_line_count, wnds, wnps );

PRAGMA RESTRICT_REFERENCES( Dop, wnds, wnps, rnps );

END;
/
CREATE OR REPLACE PACKAGE BODY Dop AS

PROCEDURE put( s IN VARCHAR2 ) IS
da dop_assistant := dop_assistant();
l_ntt /*dop_types.*/txt_table; /* Null Txt_Table */
BEGIN
IF NOT g_data.EXISTS(da.SID) THEN g_data(da.SID) := l_ntt; END IF;
IF da.LAST IS NULL THEN
g_data(da.SID)(1) := s;
ELSE
g_data(da.SID)(da.LAST) :=
g_data(da.SID)(da.LAST) || s;
END IF;
END;
----------
PROCEDURE p( s IN VARCHAR2 ) IS BEGIN put( s ); END;
----------
PROCEDURE put_line( s IN VARCHAR2 ) IS
da dop_assistant;
BEGIN
put( s );
da := dop_assistant();
g_data(da.SID)(da.LAST+1) := NULL;
END;
----------
PROCEDURE pl( s IN VARCHAR2 ) IS BEGIN put_line( s ); END;
----------
PROCEDURE new_line IS BEGIN put_line( NULL ); END;
----------
FUNCTION get_line( n IN NUMBER ) RETURN VARCHAR2 IS
da dop_assistant := dop_assistant();
l_str /*dop_types.*/txt_row DEFAULT g_data(da.SID)(n);
BEGIN
IF (n = da.LAST - 1) AND g_data(da.SID)(da.LAST) IS NULL THEN
g_data(da.SID).DELETE(n+1);
END IF;

g_data(da.SID).DELETE(n);

RETURN l_str;
END;
----------
FUNCTION get_line_count RETURN NUMBER IS
da dop_assistant := dop_assistant();
cnt NUMBER DEFAULT g_data(da.SID).COUNT;
BEGIN
IF da.LAST IS NOT NULL AND g_data(da.SID)(da.LAST) IS NULL THEN RETURN cnt;
ELSE RETURN cnt + 1; END IF;
END;

END;
/
CREATE OR REPLACE TYPE BODY dop_assistant AS
constructor FUNCTION dop_assistant RETURN self AS result AS
BEGIN
SELECT TO_CHAR(s.SID) || TO_CHAR(s.serial#)
INTO self.SID FROM v$session s WHERE s.SID = ( SELECT m.SID FROM v$mystat m WHERE ROWNUM = 1 );

IF NOT Dop.g_data.EXISTS(self.SID) THEN self.LAST := NULL;
ELSE self.LAST := Dop.g_data(self.SID).LAST; END IF;

RETURN;
END;
END;
/
CREATE OR REPLACE VIEW dov AS
SELECT ROWNUM lineno, Dop.get_line( ROWNUM ) text
FROM ALL_OBJECTS WHERE ROWNUM < ( SELECT Dop.get_line_count FROM dual );


TIA:
Peter

Tom Kyte

Followup  

May 12, 2003 - 7:40 am UTC

as you discovered, it already was session capable.

Sorry

May 12, 2003 - 4:54 am UTC

Reviewer: Peter

Huh, sorry for my foolishness -- after all I tried Tom's original code and I see it is session-sensitive already. Otherwise my questions remains!

TIA:
Peter

compilation errors

July 18, 2003 - 5:05 pm UTC

Reviewer: soy from usa

Warning: Package Body created with compilation errors.

I'm using 8i. SQL PLus 8.

When I paste the package body into SQL Plus it doesn't detect the end (the /) so i have to add another one. Then I get the error. What am I doing wrong?

Tom Kyte

Followup  

July 19, 2003 - 11:23 am UTC

you have a space in front of the "/" then

"/" must be in position one

Handle chr(10) nicely

December 22, 2003 - 4:28 pm UTC

Reviewer: Decibel from Austin, TX

I've tweaked the procedure Tom provided a bit to do a nicer job on embedded linefeeds:

CREATE OR REPLACE PROCEDURE p( p_string in varchar2 )
IS
l_string long default p_string;
l_next int;
l_add int;
BEGIN
LOOP
EXIT WHEN l_string is null;
l_next := instr( l_string, chr(10) );
IF l_next = 0 THEN
l_next := 250;
l_add := 1;
ELSE
l_next := l_next - 1;
l_add := 2;
END IF;
dbms_output.put_line( substr( l_string, 1, l_next ) );
l_string := substr( l_string, l_next + l_add );
END LOOP;
END;

Hope someone finds it useful (and yes, I know long is depricated, but I didn't feel like messing with it).

Lifesaver

March 01, 2004 - 4:24 pm UTC

Reviewer: Arun Mathur from Marietta, GA

Tom,

The package you provided works great. For some reason, I'm getting a java.lang.OutOfMemory exception when using the package to run a report. The report itself runs to completion, but I'm curious as to why I'm getting this exception. Any thoughts on where I can troubleshoot?

Thanks.
Arun

Tom Kyte

Followup  

March 01, 2004 - 4:58 pm UTC

you are getting a java exception running plsql? can you clarify the environment here and exactly what is happening? (this page doesn't do java)

Forgot to paste the error

March 01, 2004 - 4:29 pm UTC

Reviewer: Arun Mathur from Marietta, GA

ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemor
yError
ORA-06512: at "SYS.XMLPARSERCOVER", line 0
ORA-06512: at "SYS.XMLPARSER", line 101
ORA-06512: at line 40

I'm running Oracle 9.2.0.1.0.

Thanks again.
Arun

Tom Kyte

Followup  

March 01, 2004 - 5:10 pm UTC

oh, that is nothing to do with dbms_output'ing then.

you are running out of memory doing a huge parse of some really big xml document. You "ran out of memory available"

My mistake

March 01, 2004 - 4:34 pm UTC

Reviewer: Arun Mathur from Marietta, GA

It would've helped if I actually read the error message a little more carefully. The exception has nothing to do with your package, just with my brain. Sorry to take extra space from this discussion.

Regards,
Arun


Tom Kyte

Followup  

March 01, 2004 - 5:10 pm UTC

no worries :)

Word-wrapping version of p

March 08, 2004 - 10:58 am UTC

Reviewer: Paul from UK

CREATE OR REPLACE PROCEDURE p1 (p_str IN VARCHAR2)
IS
l_str LONG := p_str;
l_instr NUMBER;
BEGIN
LOOP
EXIT WHEN l_str IS NULL;
l_instr := INSTR(TRANSLATE(SUBSTR(l_str,1,250),chr(10)||chr(32),chr(44)||chr(44)),chr(44),-1);
IF l_instr = 0 OR LENGTH(l_str) <= 250 THEN
l_instr := 250;
END IF;
DBMS_OUTPUT.put_line(SUBSTR(l_str,1,l_instr));
l_str := SUBSTR(l_str,l_instr+1);
END LOOP;
END;
/

Could do with the odd tweak to make it perfect, though :-)

Peculiar Problem

March 16, 2004 - 6:01 am UTC

Reviewer: Tony from India

I'm writting shell progam embedded with PL/SQL block and use DBMS_OUTPUT.PUT_LINE to output messages. Only the first word of the message is displayed and 'not found' error message is also displayed. what could be the reason?
For example: If I've
DBMS_OUTPUT.PUT_LINE('Record Succesfully inserted.'), only the first word of the message is displayed with 'not found' error. This happens when the PL/SQL block is embedded with shell script. If I run the PL/SQL block alone in SQL Plus, complete message is displayed without any error.
Am I doing anything wrong?
Please help me Tom.




Tom Kyte

Followup  

March 16, 2004 - 8:05 am UTC

example please? (small, concise, yet 100% complete)

Here is mine:

$ cat test.sh
#!/bin/sh

sqlplus -s / <<EOF
exec dbms_output.put_line( 'Record Successfully Inserted.' );
EOF
$ ./test.sh
Record Successfully Inserted.


PL/SQL procedure successfully completed.


Peculiar Problem - Code

March 16, 2004 - 9:51 pm UTC

Reviewer: Tony from India

Since the original fuction is big, I tailored that function.


function LoadData
{
g_UserName="scott/tiger"

# Connect to DataBase and Start Script
`$SQLPLUS -s << EOF
$g_UserName
WHENEVER OSERROR EXIT OSCODE
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET SERVEROUTPUT ON SIZE 200000
SET HEADING OFF
SET VERIFY OFF
SET FLUSH OFF
SET ECHO OFF
SET PAGES 0
SET FEEDBACK OFF
SET TERM OFF
SET TAB OFF
SET SPACE 0
SET WRAP OFF
SET TRIM OFF

DECLARE

g_PgmId NUMBER:= 123;

PROCEDURE Insert
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Rows are inserted.');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Rows not are inserted.');

END Insert;

BEGIN

Insert;

END;
/
EXIT;
EOF`
return 0

}

Tom Kyte

Followup  

March 17, 2004 - 7:10 am UTC

hows about a complete example -- as I asked for:

example please? (small, concise, yet 100% complete)


I don't even know what shell you are using, I don't have a $SQLPLUS environment variable and so on.

small
concise
yet 100% complete <<<======== very important.

Make it as small as humanly possible (eg: do you really need an insert in there to reproduce your issue? start with an EMPTY script and add just that which is necessary to reproduce)

Where am I going wrong?

May 27, 2004 - 2:02 pm UTC

Reviewer: Zeb from UK

I've cut & pasted the example - I confess I renamed the procedure 'buffer' as I hate the M$ redundant 'my_' - "no Bill it ain't yours its mine"

The view only returns 3200 rows when there should be 50000+.
also
I get program too big error.

Is there a 3200 row limit? If so how is this better?

(I'd use pipe - if HAD 9). :)

Thanks anyway.

Tom Kyte

Followup  

May 27, 2004 - 8:35 pm UTC

program too big? what is the ora-xxxxx error?

Inelegant 'solution'

May 28, 2004 - 7:10 am UTC

Reviewer: Zeb from UK

Doh! The 3200 limit is because there were only 3200 rows in all_objects. So if you were migrating legacy data, couldn't use loader and needed to see what was happening in real time...

Program too big? I guess the array has to run out of memory some time. Better add my_dbms_output.flush!

Is there really no way of viewing the buffer as the procedure executes? It would have been easier and clearer to insert rows in a temporary table. This is just obfuscation.

Given these chronic limitations (and curious implementation) I can't see this being particularly useful.

Sorry.


Tom Kyte

Followup  

May 28, 2004 - 7:54 am UTC

yes there is -- but only in 9i -- it is called a PIPELINED FUNCTION.

or using UTL_FILE and "tail -f"

but as stated, using the way back machine to 7.3.4, well, you are limited. If you want, just use "from all_objects, all_objects", that'll give you 3200^2.

but, if you are running out of memory first, not much to be done.




Question

June 23, 2004 - 1:05 am UTC

Reviewer: Nermin from Canada

use utl_file and write directly to a file. utl_file (added in 7.3.3) has a
1022 byte limit/line (raised to 32k/line in 8.0.5)

How does this work?

Tom Kyte

Followup  

June 23, 2004 - 8:47 am UTC

how does what work? if you mean utl_file we document it in the supplied packages guide.



Can we use pipe opend file in utl_file

June 29, 2004 - 4:43 pm UTC

Reviewer: prashant mishra from USA

Hi

Tom,

In sequel to that how can i use mknod/mkfifo opend file in util_file.

Thanks


Tom Kyte

Followup  

June 29, 2004 - 6:46 pm UTC

well, you won't be using mknod/mkfifo from plsql - you can create the file beforehand and open in append mode.

we got implemented

June 30, 2004 - 3:20 pm UTC

Reviewer: prashant from USA

Thanks a lot !!

I have implemented from PL/SQL by using host command and fired in background.



Re : Pipe opened with utl_file

July 14, 2004 - 4:38 pm UTC

Reviewer: Prashant from USA

Hi Tom,

I have implemented but some times it works and some times It hangs.This is very interesting ,

Actually I am calling one sql file thru shell script by using sqlplus -s << EOF. I am calling host command to create pipe in background and sqlldr along with it in next line .


After that i am opening the same pipe file with utl_file in append mode. Some times sqlloader sucks the data which is pulling by utlfile in pipe and some times it hangs.

Really its a fast process to load the data. But i m loosing some point . can u help me out

Tom Kyte

Followup  

July 15, 2004 - 11:32 am UTC

I cannot imagine doing this???? why would you not just array insert right in the plsql code itself???? skip utl_file, blow off sqlldr -- instead of writing out data from plsql -- just insert it in the first place. it'll be much faster than what you are doing (actually insert into select would be even faster if possible)

Pipe opened with utl_file

July 15, 2004 - 1:38 pm UTC

Reviewer: prashant from usa

Hi Tom,

Thanks for reply but insertion is slow than this process. Because sqlloader is loading data with direct option, as we r using utl_file with pipe , this is not allocating physical space and not consuming time to dump data in file.

In just one go data will be inserted, I have tested in SQL prompt, but when i executed thru shell script sometimes it works and some time it hangs.

What cud be the remedy of this problem

Thanks a lot




Tom Kyte

Followup  

July 15, 2004 - 6:05 pm UTC

insert /*+ append */ from a pipelined function then.

that is a direct path load.

insert /*+ append */ into your_table select * from your_pipelined_function;


I'm not going to debug OS issues with named pipes and all sorts of complexity. we can do this simply and efficiently in the database itself. You do not need sqlldr for direct path.

replace the utl_file.writes with pipe row() calls.

November 11, 2004 - 5:14 pm UTC

Reviewer: A reader


November 30, 2005 - 5:39 pm UTC

Reviewer: A reader

Works GREAT!

Wonderful workaround MY_DBMS_OUTPUT

March 17, 2006 - 6:10 am UTC

Reviewer: Bandi from Cambridge

It's very useful to overcome the limit of linesize in oracle supplied DBMS_OUTPUT.

Tom Kyte

Followup  

March 17, 2006 - 5:28 pm UTC

10gr2 by the way removes these limits

32k linesize
set serveroutput on size UNLIMITED

and hence would obviate any need for a workaround.

Function DBMS_OUTPUT

May 29, 2006 - 8:57 am UTC

Reviewer: jash from India

I am calling this function from one procedure. How do i
(DBMS_OUTPUT.PUT_LINE('TemplateID @@@@'||TemplateID);) get the output from the above syntax

BEGIN



TemplateID := Types.PSEQ_GENERATOR_GET('PTTM');

DBMS_OUTPUT.PUT_LINE('TemplateID @@@@'||TemplateID);

INSERT INTO TPROVIDER_TPLLIB_TEMPLATE (RECORD_ID,
TEMPLATE_ID,
FOLDER_ID,
SECTION_ID,
FACILITY_ID,
RECORD_EFF_DATE,
RECORD_EXP_DATE,
TEMPLATE_NAME,
TEMPLATE_DESC_SHORT,
TEMPLATE_DESC_LONG,
CHECK_OUT_NOTIFY_FG,
CHECK_OUT_NOTIFY_MAIL_GRP,
CHECK_IN_NOTIFY_FG,
CHECK_IN_NOTIFY_MAIL_GRP,
LOCK_NOTIFY_FG,
LOCK_NOTIFY_MAIL_GRP,
UNLOCK_NOTIFY_FG,
UNLOCK_NOTIFY_MAIL_GRP,
CHECK_IN_DUE_COUNT,
CHECK_IN_DUE_UNIT,
CHECK_IN_FAIL_NOTIFY_FG,
CHECK_IN_FAIL_NOTIFY_MAIL_GRP,
UNLOCK_DUE_COUNT,
UNLOCK_DUE_UNIT,
UNLOCK_FAIL_NOTIFY_FG,
UNLOCK_FAIL_NOTIFY_MAIL_GRP,
DELETE_FG,
DELETE_NOTIFY_FG,
DELETE_NOTIFY_MAIL_GRP,
TEMPLATE,
STATUS_CODE,
SOURCE_SYSTEM_ID,
MOD_USER_ID)

VALUES(TPR_TPLLIB_TMPL_SEQ.nextval,
TemplateID,
FolderID,
SectionID,
FacilityID,
sysdate,
sysdate -1,
TemplateName,
TemplateDescShort,
TemplateDescLong,
CheckOutNotifyFG,
CheckOutNotifyMailGrp,
CheckInNotifyFG,
CheckInNotifyMailGrp,
LockNotifyFG,
LockNotifyMailGrp,
UnlockNotifyFG,
UnlockNotifyMailGrp,
CheckInDueCount,
CheckInDueUnit,
CheckInFailNotifyFG,
CheckInFailNotifyMailGrp,
UnlockDueCount,
UnlockDueUnit,
UnlockFailNotifyFG,
UnlockFailNotifyMailGrp,
DeleteFG,
DeleteNotifyFG,
DeleteNotifyMailGrp,
Template,
StatusCode,
SourceSystemID,
ModUserID
);

SELECT RECORD_ID INTO RecordID FROM TPROVIDER_TPLLIB_TEMPLATE
WHERE TEMPLATE_ID = TemplateID;

INSERT INTO TAUDIT_TRAIL(
RECORD_ID,
ACCESS_TABLE_NAME,
ACCESS_RECORD_ID,
ACCESS_USER_ID,
ACCESS_TYPE_CODE,
ACCESS_DATE_TIME,
SOURCE_SYSTEM_ID,
MOD_USER_ID)
VALUES(
TAUDIT_SEQ.nextval,
'TPROVIDER_TPLLIB_TEMPLATE',
RecordID,
ModUserID,
'I',
sysdate,
'LTCSEP',
ModUserID);

UPDATE TSEQ_GENERATOR SET LAST_ID_VALUE = SUBSTR(TemplateID,5,12) + 1
WHERE ENTITY_PREFIX = 'PTTM';
COMMIT;

EXCEPTION -- exception handlers begin
WHEN NOT_LOGGED_ON THEN -- handles 'Database call without being connected'error
Raise_application_error(1001 ,'Database call without being connected');
WHEN LOGIN_DENIED THEN -- handles 'Database username and/or password is invalid'error
Raise_application_error(1002 ,'Database username and/or password is invalid');
WHEN NO_DATA_FOUND THEN -- handles 'data not found 'error
Raise_application_error(1003 ,'Data not found');
WHEN ROWTYPE_MISMATCH THEN -- handles 'Table column Type is mismatch'error
Raise_application_error(1004 ,'Table column Type is mismatch');
WHEN STORAGE_ERROR THEN -- handles 'PL/SQL runs out of memory or memory has been corrupted'error
Raise_application_error(1005 ,'PL/SQL runs out of memory or memory has been corrupted');
-- WHEN OTHERS THEN -- handles all other errors
-- Raise_application_error(1000 ,'Others while retrieving');


END PPROVIDER_TMPL_LIB_ADD;


Tom Kyte

Followup  

May 30, 2006 - 8:17 am UTC

huh?

you just call

dbms_output.put_line( string.... );


and then if the environment you called the procedure from supports displaying of this stuff (sqlplus for example), it'll show it after the call - assuming "set serveroutput on" was enabled.



Compiling a procedure

June 14, 2006 - 2:03 pm UTC

Reviewer: Vikram Romeo from Switzerland

Hi Tom,

When I am trying to compile a procedure, it is not showing me all the errors it has at once.

As I keep fixing errors, it shows me new ones ... :-)

Any setting by which I can make it show all errors at once (probably something to do with the error stack??)

Thanks in advance.

Regards,
Vikram Romeo

Tom Kyte

Followup  

June 15, 2006 - 8:13 am UTC

it shows all it can figure out at that time, it doesn't hold back.

The correction you made must be making some more of the code "understandable" - but wrong still - to the compiler. Not too much you can do about that. Basically, the compiler spits out ever error it can recognize - but your errors must be making some more of the code unrecognizable.

Excellent,

August 22, 2006 - 1:31 pm UTC

Reviewer: mark wiltshire from London UK

Although I am having problems using this.

I have pre loaded the package and view in the database,

then in my sql I run

my_dbms_output.put_line

then when once I have completed my output, I run

select * from my_dbms_output_view;

I get the following error..

ORA-06550: line 107, column 2:
PLS-00428: an INTO clause is expected in this SELECT statement

?? what am i doing wrong.

Thanks

Mark



Tom Kyte

Followup  

August 27, 2006 - 3:01 pm UTC

you have that select in your plsql code. in plsql you either:


for x in (select * from my_dbms_output_viwe )
loop
....


or

select * <bulk collect> INTO host variables
from ....


if you put the select outside the plsql block like my examples.....

my_dbms_output

April 28, 2009 - 4:51 pm UTC

Reviewer: A reader from USA

Tom,
I was trying to think of a way to instrument my code using your MY_DBMS_OUTPUT package.

Wanted to be able to put output statements in my code that do not execute unless it checks a context attribute value to be set to some value.

If that value is set then your enable procedure will allow statements to be written if not then it wont do anything.

But I dont see a way to set a global context so say if a procedure is running and in the middle of it running I wont to start writing output I can and when the code finishes running the select * from my_dbms_output_view will return text.

Dont like the idea of a table select either but that may have to do.

Any ideas? Im on 9i R2.
Tom Kyte

Followup  

April 29, 2009 - 8:31 am UTC

... But I dont see a way to set a global context ....

you don't?



ops$tkyte%ORA9IR2> create or replace context my_global_ctx using my_proc accessed globally
  2  /

Context created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure my_proc( p_name in varchar2, p_val in varchar2 default null )
  2  as
  3  begin
  4      if (p_val is not null)
  5      then
  6          dbms_session.set_context( 'my_global_ctx', p_name, p_val );
  7      else
  8          dbms_session.clear_context( 'my_global_ctx', null );
  9      end if;
 10  end;
 11  /

Procedure created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_output.put_line( 'debug = ' || sys_context( 'my_global_ctx', 'debug' ) );
debug =

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> set echo off
in another session execute
exec my_proc( 'debug', 'yeah' )
ops$tkyte%ORA9IR2> pause

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_output.put_line( 'debug = ' || sys_context( 'my_global_ctx', 'debug' ) );
debug = yeah

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> set echo off
in another session execute
exec my_proc( 'debug' )
ops$tkyte%ORA9IR2> pause

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_output.put_line( 'debug = ' || sys_context( 'my_global_ctx', 'debug' ) );
debug =

PL/SQL procedure successfully completed.


More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here