| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> pl/sql overflow buffer problem
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;
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 );
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
xxx.000.307 xxx.000.308 xxx.000.300 xxx.000.301 xxx.000.302
*
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 - 09:51:14 CDT
 |
 |