-
Ora-01403
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
-
use SQLLDR
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
what's the db version? the flat file is on the db server, not your client , correct?
steve
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|