| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: DBMS_OUTPUT.PUT_LINE question
In article <393e373d.153280329_at_news.erols.com>,
Turdcutter_at_eremove.edu (Turdcutter) wrote:
> How do you get this function to insert leading spaces?
>
> DBMS_OUTPUT.PUT_LINE(' '|| var1)
>
> gives no leading spaces
>
> ?????
>
> also,,,
>
> how could you do a carriage return with this function
> DBMS_OUTPUT.PUT_LINE('') < ---- doesn't seen to work
>
> thank you
>
there are limits to dbms_output. 2 of them are
- no blank lines
- no leading blanks.
Here is something i wrote up for getting around dbms_output limits (it addresses a couple -- not just yours):
...
255 bytes/line limit:
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;
it avoids the 255 byte issue but does not put the output on a single line.
leading blanks not preserved/blank lines not preserved:
put out a TAB (char(9)) or some other "innocent" character first. dbms_output will not preserve leading blanks.
leading blanks not preserved, blank lines not supported, 255 bytes / line limit:
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)
All limitations removed (you own the code ;).....
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_at_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 );
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_at_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;
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_at_ORA734.WORLD>
tkyte_at_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
To test it and see how it works, we:
tkyte_at_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;
PL/SQL procedure successfully completed.
tkyte_at_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_at_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_at_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;
PL/SQL procedure successfully completed.
tkyte_at_ORA734.WORLD>
tkyte_at_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_at_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" ) )
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Tue Jun 06 2000 - 00:00:00 CDT
 |
 |