Read text file to insert data into Oracle SQL table

Question

I am studying Oracle SQL developer.

What I am doing is reading text file line by line from the folder. Then Inserting data to the SQL table.

I am able to compile my PROCEDURE, however, it doesn't seem to be inserting the data to file.

Create or Replace PROCEDURE Rfile is
f UTL_FILE.FILE_TYPE;
s VARCHAR2(200); 
BEGIN
f := UTL_FILE.FOPEN('C:\Projects\','testdatabinary.txt','R');
  IF UTL_FILE.IS_OPEN(f) THEN
    LOOP
      BEGIN
        UTL_FILE.GET_LINE(f,s);
        IF s IS NULL THEN
          EXIT;
        END IF;
      INSERT INTO DATAINSERT
      (COLUMN1, COLUMN2)
      VALUES
      (s, 'testdatabinary');
      END;
  END LOOP;
  COMMIT;
  END IF;
 END;

And I have a table DATAINSERT with two varchar(200) type cols

I am not really sure the reasons that PROCEDURE is not inserting data to table

I just checked error message

Error starting at line 1 in command:
EXEC Rfile
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "SYSTEM.RFILE", line 5

Is your first line NULL? You could use dbms_output.put_line(s) to see what it's reading. — sgeddes, Feb 12 '13 at 00:22
No , It is problem of directory path. I checked and copy and paste the directory of file exactly :(. however, it is still making a problem — Dc Redwing, Feb 12 '13 at 00:26
And that file/directory exists on the server? Not seeing anything wrong with the code. — sgeddes, Feb 12 '13 at 00:30
yes ! so I create path name like "MYDIR" and i repleased like f := UTL_FILE.FOPEN('MYDIR','testdatabinary.txt','R'); — Dc Redwing, Feb 12 '13 at 00:36
I'm at a loss -- but you shouldn't have quotes around mydir (assuming that is a variable). My only other guess would be a permissions issue... Good luck. — sgeddes, Feb 12 '13 at 00:47
possible duplicate of [UTL\_FILE.FOPEN() procedure not accepting path for directory?](http://stackoverflow.com/questions/2751113/utl-file-fopen-procedure-not-accepting-path-for-directory) — APC, Jul 30 '13 at 17:06

score 9 · Answer 1 · edited Sep 02 '15 at 14:45

Not sure what causing problems. For me its working fine here is my example code

--Reference Site --https://community.oracle.com/thread/3633577?start=0&tstart=0

     set serveroutput on;
     CREATE or replace DIRECTORY USER_DIR AS '/home/oracle'; 
     GRANT READ ON DIRECTORY USER_DIR TO PUBLIC;

     DECLARE 
        V1 VARCHAR2(200); --32767
        F1 UTL_FILE.FILE_TYPE; 
     BEGIN 
        F1 := UTL_FILE.FOPEN('USER_DIR','temp.txt','R'); 
        Loop
        BEGIN
    UTL_FILE.GET_LINE(F1,V1); 
    dbms_output.put_line(V1);
    EXCEPTION WHEN No_Data_Found THEN EXIT; END;
        end loop;

        IF UTL_FILE.IS_OPEN(F1) THEN
     dbms_output.put_line('File is Open');
        end if;

        UTL_FILE.FCLOSE(F1); 
     END; 
     /
    set serveroutput off;

Nizar · Answer 2 · 2015-05-28T06:35:03.873

You can't able to enter the path directly to the file open command like this

f := UTL_FILE.FOPEN('C:\Projects\','testdatabinary.txt','R');

Instead of entering path directly, you have to create directory using the below sql query

CREATE or replace DIRECTORY USER_DIR AS 'C:\PROJECTS\';

Then enter the directory name to the file open command. It will be like this

f := UTL_FILE.FOPEN('USER_DIR ','testdatabinary.txt','R');

score 1 · Answer 3 · edited Jun 04 '14 at 06:03

1

First procedure trying to load the file from PC into the server whereas in second procedure file is from server to server. UTL_FILE.FOPEN works for server-side PL/SQL. So first procedure wont execute properly... If you want to load a file from PC to server, then you need to user any front end like D@K or VB.

edited Jun 04 '14 at 06:03

bjb568

11,089
11
50
71

answered Jun 04 '14 at 05:39

Mahesh

11
1

score 0 · Answer 4 · edited Dec 12 '14 at 10:31

0

The mistake your making here is using the workstation path, you actually need to be using the directory path.

The quickest way to resolve this, is just to use the WINSCP command.

Use WINSCP to tell you what the directory path is, then simply substitute this new information for the old path you have in your code, and everything should work ok.

edited Dec 12 '14 at 10:31

shawty

5,729
2
37
71

answered Dec 12 '14 at 08:51

Prince Sharma

118
1
3

score -1 · Answer 5 · answered Apr 20 '17 at 13:59

This is very conceptual and tricky. which you can find more concepts and details below: https://ora-data.blogspot.in/2016/11/read-and-writ-text-file-using-UTLFILE.html

Sure will be helpful:

create or replace procedure read_file is 
f_line varchar2(2000); 
f utl_file.file_type; 
f_dir varchar2(250); 
fname varchar2(50); 
Comma1 varchar(10); 
Comma2 varchar(10); 
Comma3 varchar(10); 
Comma4 varchar(10); 
Comma5 varchar(10); 
f_empno emp.empno%type; 
f_ename emp.ename%type; 
f_job emp.job%type; 
f_mgr emp.mgr%type; 
f_hiredate emp.hiredate%type; 
f_sal emp.sal%type; 
begin 
f_dir := ‘E:\PLSQL’; 
fname := ‘input.txt’; 
f := utl_file.fopen(‘UTL_FILE_DIR’,fname,’r’); –opening the file using fopen function 
loop 
begin 
utl_file.get_line(f,f_line); 
–using a loop continuously get the file’s content using get_line function exception when no_data_found then 
exit; 
end; 

Comma1 := INSTR(f_line, ‘,’ ,1 , 1); 
Comma2 := INSTR(f_line, ‘,’ ,1 , 2); 
Comma3 := INSTR(f_line, ‘,’ ,1 , 3); 
Comma4 := INSTR(f_line, ‘,’ ,1 , 4); 
Comma5 := INSTR(f_line, ‘,’ ,1 , 5);
–Each field in the input record is delimited by commas. 
–We need to find the location of two commas in the line. 
–and use the locations to get the field from the line. 
f_empno := to_number(SUBSTR(f_line, 1, Comma1-1)); 
f_ename := SUBSTR(f_line, Comma1+1, Comma2-Comma1-1); 
f_job := SUBSTR(f_line, comma2+1, Comma3-Comma2-1); 
f_mgr := to_number(SUBSTR(f_line, comma3+1, Comma4-Comma3-1)); 
f_hiredate := to_date(SUBSTR(f_line, comma4+1, Comma5-Comma4-1),’dd-mon-yyyy’); 
f_sal := to_number(SUBSTR(f_line, comma5+1),’99999′); dbms_output.put_line(f_empno ||’ ‘|| f_ename || ‘ ‘ || f_job || ‘ ‘ || f_mgr ||’ ‘ || f_hiredate||’ ‘|| f_sal); 
insert into emp12 VALUES (f_empno,f_ename,f_job,f_mgr,f_hiredate,f_sal); 
end loop; 
utl_file.fclose(f); 

commit; 

end; 

/

score -2 · Answer 6 · answered Oct 08 '15 at 07:47

-2

first login with

username:sys as sysdba

password should be the same as used for user 'system'

now enter

SQL> grant execute on UTL_FILE to PUBLIC;

Now login with any user with which you want to create procedure

answered Oct 08 '15 at 07:47

Abhinav

7
1
8

Please specify the reason for downvoting the answer – Abhinav Oct 09 '15 at 09:24
1

I guess the reason for the downvote is that your answer didn't fit the question? The error message the OP got was not due to missing GRANTs, but due to an incorrect call. – Frank Schmitt Oct 09 '15 at 09:37

Read text file to insert data into Oracle SQL table

6 Answers6

Linked