0

I am trying to upload data from text file to my table using UTL_FILE from SQL PLUS. the text file name testb.txt contain data:

10,mike

20,bill

30,kim

and in my the table in the database called mytable contain columns (empno, empname)

I tried this code on SQL plus:

declare
filehand utl_file.file_type;
line varchar2(4000);
vempno varchar2(50);
vempname varchar2(50);
begin
filehand := utl_file.fopen('c:\users\myuser\desktop', 'testb.txt', 'r');
loop
utl_file.get_line(filehand, line);
if line is not null then
vempno := substr(line, 1, instr(line, ',')-1);
vempname := substr(line, instr(line, ',')+1, length(line));
dbms_output.put_line(vempno);
dbms_output.put_line(vempname);
insert into mytable (empno, empname) values (vempno, vempname);
commit;
else
exit;
end if;
end loop;
commit;
utl_file.fclose(filehand);
exception
when others then
null;
end:
/

when I run this code I got PL/SQL procedure successfully completed. but when I see my table nothing change and the data not loaded from the text file to my table. ???

whats wrong in my code and what to do to upload the data from the text file to my table using UTL_file and dbms ??

Please guys help me I really need it.

Thank you very much

C.Z
  • 1
  • 1
  • 3
  • 3
    Remove `when others then null;` and you'll see the errors it is presumably throwing. For a start the first argument to `utl_file.fopen()` should be an Oracle directory object - which points to a location on the DB server - and not an OS directory path (particularly one on your client, if the DB is remote). Why are you doing this manually. rather than using SQL\*Loader or external tables? – Alex Poole Aug 06 '18 at 18:30
  • can u give me an example about the utl_file.fopen() I didnt got it. and I cant use sql loader because in other files the data in the text file not organized like the table columns and types and the terminator between the words not always the same – C.Z Aug 06 '18 at 20:35
  • `select * from all_directories` <= Those are the Oracle **directory objects** on your database server. You have to use the **directory name**, not the path, e.g. `utl_file.fopen(DATA_PUMP_DIR, 'myfile.txt')` – kfinity Aug 06 '18 at 21:03

0 Answers0