Re: pl/sql overflow buffer problem

Thanks -- this did the work. I also found the disable method to the dbms_output package.

Thanks once again to one and all,
Ed

"Scott Mattes" <Scott_at_TheMattesFamily.ws> wrote in message news:<GqoDa.171$Jw6.136406_at_news1.news.adelphia.net>...
> You are using a lot of put_lines and dbms_output has severe limitations for
> what you are trying to do and I think you ran into it (the buffer defaults
> to 20,000 if I remember correctly). Try setting the dbms_output buffer to
> its max (1,000,000 for 8i, and you didn't mention your version).
>
> I suspect that you will meet that limit also, so you should consider making
> a pl/sql table and adding your debug lines to that and then add code to the
> end to - o never mind, the only way to output it to the screen is with
> dbms_output and it has these limits you see, so you can't.
>
> Another option is to create a table to hold the debug lines and at the end
> do a commit (did you notice that you have no commit in your code?). If you
> absolutely need to see those msgs during the run look into autonomous
> transactions.
>
>
>
> "Ed" <edreyes77_at_hotmail.com> wrote in message
> news:5bb04c8c.0306040651.735b675d_at_posting.google.com...
> > I have a flat file I am trying to read in through pl/sql. I have been
> > able to read the file but when I go to parse the pipe delimited '|'
> > record I get a buffer overflow issue.
> >
> > Here is an example of what I am trying to read:
> >
> > VERC|**|Verification Codes||||
> > VERC|ACDV|By going to ACDV system||||
> > VERC|CONS|By information sent to consumer||||
> > VERC|EMAL|By email||||
> > VERC|FAX|By fax||||
> > VERC|INTR|Through Internal||||
> > VERC|PHON|By phone||||
> > WARN|**|Warning Indicators||||
> > WARN|WA|Consumer reported as deceased||||
> > WARN|WB|Consumer under the age Of 18||||
> > WARN|WC|Input SSN is invalid||||
> > WARN|WD|Output SSN is invalid||||
> > WARN|WE|Iutput SSN has not been issued||||
> > WARN|WF|Output SSN has not been issued||||
> > WARN|WG|Address is non-residential||||
> >
> >
> > Here is how I am reading it:
> >
> > create or replace procedure uc is
> >
> >
> > v_line varchar2(2000);
> > c_location constant varchar2(80) := '/tmp';
> > c_filename constant varchar2(80) := 'tpcodes.txt';
> > v_handle Utl_File.File_Type := NULL;
> >
> > nLocation number := 0;
> > nLastLocation number := 0;
> >
> > strParsed varchar2(2000);
> > ii number;
> > start_of_subtext number;
> > code_count number;
> >
> >
> > tpcode_count number;
> > i_collection number;
> > found_value boolean;
> >
> > TYPE tp2_codes is record(
> > code_type varchar2(5),
> > code_value varchar2(15),
> > code_desc varchar2(80),
> > code_length number(2),
> > comments varchar2(60),
> > derog_ind varchar2(1)
> > );
> >
> > cursor archive_cursor is select * from tp_codes;
> >
> > tpcode tp2_codes;
> >
> > archive_codes TP_CODES%ROWTYPE;
> > tp_codes_rec TP_CODES%ROWTYPE;
> >
> > TYPE tp2codes_collection is VARRAY(20) of tp2_codes;
> >
> > codes_collection tp2codes_collection;
> >
> >
> > begin
> >
> > Dbms_Output.Put_Line ( 'xxx.000.000' );
> >
> > v_handle := UTL_FILE.FOPEN ( c_location,
> > c_filename,
> > 'r',
> > 32767 );
> >
> > Dbms_Output.Put_Line ( 'xxx.000.111' );
> >
> > codes_collection := tp2codes_collection();
> >
> > Dbms_Output.Put_Line ( 'xxx.000.112' );
> >
> > Utl_File.Get_Line ( file => v_handle,
> > buffer => v_line );
> > tpcode_count := 0;
> >
> > Dbms_Output.Put_Line ( 'xxx.000.113' );
> >
> > loop
> >
> > Dbms_Output.Put_Line ( 'xxx.000.222' );
> >
> > Utl_File.Get_Line ( file => v_handle, buffer => v_line );
> >
> > Dbms_Output.Put_Line ( 'xxx.000.223' );
> > Dbms_Output.Put_Line ( v_line );
> > nLastLocation := 0;
> >
> > Dbms_Output.Put_Line ( 'xxx.000.224' );
> > for ii in 0..5
> > loop
> > Dbms_Output.Put_Line ( 'xxx.000.300' );
> > nLocation := instr ( v_line, '|', nLastLocation + 1);
> > if nLastLocation > 0
> > then start_of_subtext := nLastLocation + 1;
> > else
> > start_of_subtext := 0;
> > end if;
> > Dbms_Output.Put_Line ( 'xxx.000.301' );
> > if ( start_of_subtext != nLocation ) then
> > strParsed := substr ( v_line, start_of_subtext,
> > nLocation - nLastLocation - 1 );
> > else
> > strParsed := '';
> > nLocation := nLocation + 1;
> > end if;
> > Dbms_Output.Put_Line ( 'xxx.000.302' );
> > Dbms_Output.Put_Line ( 'xxx.000.303' );
> > case
> > when ii = 0 then
> > if ( length( strParsed ) > 5 )
> > then RAISE_APPLICATION_ERROR ( -20110,
> > 'What the ...' );
> > end if;
> > tpcode.code_type := strParsed;
> > when ii = 1 then
> > if ( length( strParsed ) > 15 )
> > then RAISE_APPLICATION_ERROR ( -20111,
> > 'What the ...' );
> > end if;
> > tpcode.code_value := strParsed;
> > when ii = 2 then
> > if ( length( strParsed ) > 80 )
> > then RAISE_APPLICATION_ERROR ( -20112,
> > 'What the ...' );
> > end if;
> > tpcode.code_desc := strParsed;
> > when ii = 3 then
> > if ( length( strParsed ) > 60 )
> > then RAISE_APPLICATION_ERROR ( -20113,
> > 'What the ...' );
> > end if;
> > tpcode.code_length := to_number ( strParsed );
> > when ii = 4 then
> > if ( length( strParsed ) > 60 )
> > then RAISE_APPLICATION_ERROR ( -20113,
> > 'What the ...' );
> > end if;
> > tpcode.comments := strParsed;
> > when ii = 5 then
> > if ( length( strParsed ) > 1 )
> > then RAISE_APPLICATION_ERROR ( -20114,
> > 'What the ...' );
> > end if;
> > tpcode.derog_ind := strParsed;
> > end case;
> > Dbms_Output.Put_Line ( 'xxx.000.306' );
> > Dbms_Output.Put_Line ( ' -- ' || ii || ' -- ' ||
> > strParsed );
> > Dbms_Output.Put_Line ( 'xxx.000.307' );
> > nLastLocation := nLocation;
> > Dbms_Output.Put_Line ( 'xxx.000.308' );
> > end loop;
> >
> > Dbms_Output.Put_Line ( 'xxx.000.999' );
> > end loop;
> >
> > Dbms_Output.Put_Line ( 'xxx.111.888' );
> >
> >
> >
> > exception
> > WHEN UTL_FILE.INVALID_PATH THEN
> > RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
> > WHEN UTL_FILE.INVALID_MODE THEN
> > RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
> > WHEN UTL_FILE.INVALID_OPERATION then
> > RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
> > WHEN UTL_FILE.INVALID_FILEHANDLE then
> > RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
> > WHEN UTL_FILE.WRITE_ERROR then
> > RAISE_APPLICATION_ERROR(-20104,'Write Error');
> > WHEN UTL_FILE.READ_ERROR then
> > RAISE_APPLICATION_ERROR(-20105,'Read Error');
> > WHEN UTL_FILE.INTERNAL_ERROR then
> > RAISE_APPLICATION_ERROR(-20106,'Internal Error');
> > WHEN NO_DATA_FOUND then
> > Utl_File.Fclose ( file => v_handle );
> > WHEN OTHERS THEN
> > RAISE;
> > UTL_FILE.FCLOSE(v_handle);
> >
> > Utl_File.Fclose ( file => v_handle );
> >
> > end uc;
> >
> > /
> >
> > show errors
> >
> >
> > When I run this code I get this to the tail end:
> >
> > VERC|CONS|By information sent to consumer||||
> > xxx.000.224
> > xxx.000.300
> > xxx.000.301
> > xxx.000.302
> > xxx.000.303
> > xxx.000.306
> > -- 0 -- VERC
> > xxx.000.307
> > xxx.000.308
> > xxx.000.300
> > xxx.000.301
> > xxx.000.302
> > BEGIN uc; END;
> >
> > *
> > ERROR at line 1:
> > ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
> > ORA-06512: at "ICCONTST2.UC", line 157
> > ORA-06512: at line 1
> >
> > I am not sure what buffer they are talking about. Can someone provide
> > some insight?
> >
> > Thanks In Advance,
> > Ed
Received on Thu Jun 05 2003 - 10:54:18 CDT