| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Line length limitations in PL/SQL
A copy of this was sent to Tansel Ozkan <tansel_at_openix.com> (if that email address didn't require changing) On Thu, 15 Oct 1998 12:39:40 -0400, you wrote:
>Christopher Beck wrote:
>>
>> On Wed, 14 Oct 1998 17:16:48 -0400, Tansel Ozkan <tansel_at_openix.com>
>> wrote:
>>
>> >Hello,
>> >
>> >I have to write a PL/SQL program that will output 1300 bytes
>> >per record. I have come to learn that DBMS_OUTPUT.PUT_LINE has
>> >a limitation of 255 bytes. Then I have looked into UTL_FILE.PUT_LINE
>> >and found out that it has a limitation of 1023 bytes per line.
>>
>> You could write your own wrapper around either dbms_output.put_line
>> or utl_file.put_line to chuck up you output into 255 byte size pieces.
>
>Thanks for your input.
>
>But I need 1300 bytes PER LINE. If I am not mistaken, when I use your
>wrapper, each record will be in more than one lines. This is not what I
>want. I tried using dbms_output.put instead of dbms_output.put_line
>function in your wrapper to make it work, but still got the same
>error message. 'Line longer than 255'
>
>
>> eg.
>>
>> procedure my_put_line( p_string varchar2 ) is
>> l_string varchar2(32767);
>> begin
>> l_string := p_string;
>> loop
>> exit when l_string is null;
>> dbms_output.put_line( substr( l_string, 1, 255 ) );
>
> ^^^^^^^^^
> This will output 255 bytes per line..
>
>> l_string := substr( l_string, 256 );
>> end loop;
>> dbms_output.put_line( chr(13) ); --new line between records
>> end my_put_line;
>>
>> >
>> >What is the solution? Is UTL_FILE.PUTF a solution since there is
>> >no mention of a limitation for it? If that doesn't work I am planning
>> >to store the record in a database table and do a select from SQLPlus
>> >where the only limitation is the varchar2 column length, which is 2000.
>>
>> Note: If your program fails and rolls back then you will also roll
>> back all your debug too. :-(
>>
Here is a way to do this with PL/SQL in sql*plus that lets you have upto 2000 character lines and won't lose data in a rollback.
In order to do this, you need to create a table as such (we only ever need one of these tables per database, consider it similar to DUAL)
create table dummy ( x int primary );
insert into dummy select rownum from all_objects;
so dummy is a table with rows (approx) 1 .. 1,000 or so. You can add more if you want. The important thing is that is has records 1, 2, 3, 4, .... N (all integers, no gaps)
Once you have that, you need to make your pl/sql table callable from SQL. We need a function "getcol" for example that if you say "getcol(5)" it would return the 5'th element from your pl/sql table. We also need a function, say getmax, that tells us how many elements are in the table currently. Lastly, we need a function "put_line" to add data to this pl/sql table So a package spec might look like:
create or replace package my_dbms_output as
pragma restrict_references(my_dbms_output, wnds, wnps, rnps);
type array is table of varchar2(2000) index by binary_integer;
procedure put_line( p_str in varchar2 );
function getcol( x in number ) return varchar2; pragma restrict_references(getcol,wnds,wnps);
function getmax return number;
pragma restrict_references(getmax,wnds,wnps);
TOO_MUCH_DATA exception;
end;
/
Now, we can create a view like this:
create or replace view
my_dbms_output_view
as
select my_dbms_output.getcol(x) theColumn
from dummy
where x <= ( select my_dbms_output.getmax from dual )
/
So, this view will select our function on column X for all X's <= the number of elements in our table. that is, we will get rows 1, 2, 3 ... N-Elements in the Table.
Then we implement our package body as such:
create or replace package body my_dbms_output as
g_theArray array; g_theCnt number default 0; g_maxCnt number;
function getcol( x in number ) return varchar2
is
begin
return g_theArray(x);
end;
function getmax return number
is
begin
return g_theCnt;
end;
procedure put_line( p_str in varchar2 )
as
begin
if ( g_theCnt = g_maxCnt )
then
raise TOO_MUCH_DATA;
end if;
g_theCnt := g_theCnt + 1;
g_theArray( g_theCnt ) := p_str;
end;
begin
select max(x) into g_maxCnt from dummy;
end my_dbms_output;
/
Now we can:
for i in 1 .. 10 loop
my_dbms_output.put_line( rpad( '*', 1300, '*' ) || 'Hello ' || i );
end loop;
end;
/
set heading off
set feedback off
set linesize 2000
set trimspool on
set termout off
spool test.dat
select * from my_dbms_output_view
/
spool off
set termout on
set heading on
and be done with it...
>> >
>> >Also, what is the maximum number of bytes you can output from PL/SQL?
>> >I know you can set it with eg. 'set serveroutput on size 100000' but
>> >I don't really know the maximum value for this parameter? (Oracle7.3)
>> >
>>
>> The default buffer size is 20000 bytes. The minimum is 2000 and the
>> maximum is 1,000,000.
>>
>> >Thanks..
>> >
>> >Tansel
>>
>> hope this helps.
>>
>> chris.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
-- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.Received on Thu Oct 15 1998 - 00:00:00 CDT
 |
 |