Re: pl/sql overflow buffer problem

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 Wed Jun 04 2003 - 10:41:26 CDT