Ora-01403

[gsrk]

I am trying to load data into a table using pl/sql utl_file. I am getting the error message ORA-01403: no data found while am trying to insert the data into the table after reading it from the table.

Any help!!

Here is the piece of code

declare

l_input utl_file.file_type;
l_buffer long;
l_order number(10);
l_c_id number(10);
l_s_id number(10);
l_tran_dt date;
l_cntct_fpdate date;
l_s_fpdate date;
p_dir varchar2(100) ;
p_file varchar2(10) := 'test.dat' ;
cnt_ptype_cd varchar2(3);
s_ptype_cd varchar2(3);
errnum integer;
err varchar2(100);
errmsg varchar2(100);


begin

p_dir := '/dbms_output';
l_input := utl_file.fopen(p_dir,p_file,'R');


loop
begin
utl_file.get_line(l_input,l_buffer);
l_order := substr(l_buffer,1,10);
l_c_id := substr(l_buffer,11,10);
l_s_id := substr(l_buffer,21,10);
l_tran_dt := substr(l_buffer,37,8);
l_cntct_fpdate := substr(l_buffer,45,8);
l_s_fpdate := substr(l_buffer,53,8)

cnt_ptype_cd := 'ACQ';
s_ptype_cd := 'RET';

insert into
test
(order_no,
orderer_c_id,
orderer_s_id,
first_shipment_dt,
CONTACT_PURCH_TYPE_CD,
SITE_PURCH_TYPE_CD,
create_dt,
last_modification_dt)
values
(l_order,
l_c_id,
l_s_id,
l_tran_dt,
cnt_ptype_cd,
s_ptype_cd,
sysdate,
sysdate );

l_cnt := l_cnt+1;

dbms_output.put_line('Cnt' || l_cnt);

if (l_cnt = 1000) then
commit;
l_cnt := 0;
end if;

EXCEPTION
when OTHERS then
errnum := sqlcode;
errmsg := substr(sqlerrm,1,100);
rollback;
dbms_output.put_line('FATAL error ' || to_char(errnum) || ' ' || errmsg);


end;
end loop;
utl_file.fclose(l_input);



EXCEPTION

WHEN UTL_FILE.INVALID_PATH then
UTL_FILE.FCLOSE(l_input);

dbms_output.put_line(p_dir);
dbms_output.put_line('Invalid path for the file');

WHEN UTL_FILE.INVALID_MODE then
UTL_FILE.FCLOSE(l_input);
dbms_output.put_line('Invalid mode for the file');

WHEN UTL_FILE.INVALID_FILEHANDLE then
UTL_FILE.FCLOSE(l_input);
dbms_output.put_line('Invalid file handle ');

WHEN UTL_FILE.READ_ERROR then
UTL_FILE.FCLOSE(l_input);
dbms_output.put_line('Read error for the file');

WHEN OTHERS then
UTL_FILE.FCLOSE(l_input);


END;
/



Thanks

[rotem_fo]

Its there

[stmontgo]

what's the db version? the flat file is on the db server, not your client , correct?

steve