How to insert into Table in Oracle 10g from Text file using PLSQL program?

Question

I'm trying this PL/SQL program in Oracle 10g. To Read text file (data) from loaction 'C:\Oracle' and load it into Oracle Table using PLSQL script. But, I'm getting the following errors:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation

Here's my Script:

Create or Replace PROCEDURE Rfile is
    line VARCHAR2(100);
    namesfile UTL_FILE.FILE_TYPE;
BEGIN
    --  Syntax : FOPEN ( directory alias, filename, open mode)

    namesfile  := UTL_FILE.FOPEN('FILESDIR1','NAMES2.TXT','R'); -- open in read mode 

    LOOP
      UTL_FILE.GET_LINE(namesfile,line,100);
      dbms_output.put_line(line);

      insert into names2 values(line);                  -- insert into NAMES table
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Others exceptions....');
END;

See my response to this other question on the topic: stackoverflow.com/questions/2751113/… — APC
– APC, Commented Mar 28, 2012 at 17:51
Please, Refer the below link for more details(directory and all..) : srikanthtechnologies.com/blog/utl_file.html — Balakumaran
– Balakumaran, Commented Mar 30, 2012 at 11:09

A.B.Cade · Accepted Answer · 2012-03-28 07:26:16Z

1

You can try a different approach:
Create an external table, which is a table mapped to a file, to read the file and then just insert-select to your table from the external
(or maybe you don't even need to insert it to a different table ?)

answered Mar 28, 2012 at 7:26

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cagcowboy · Accepted Answer · 2012-03-28 07:02:32Z

0

You may need a lowercase 'r' on this line...

namesfile  := UTL_FILE.FOPEN('FILESDIR1','NAMES2.TXT','r'); -- open in read mode 
                                                       ^

answered Mar 28, 2012 at 7:02

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

1 Comment

cagcowboy Over a year ago

It needs to be a lower case 'r'. You had it in uppercase in your original question.

Rob Heusdens · Accepted Answer · 2016-12-07 00:50:14Z

0

Your loop does not check for end-of-file, so logically at some point the file read operation should raise an exception, and that is the one you got presumably.

Also you have to check that the directory (FILESDIR1) is pointing to the right OS directory AND you have been granted access to that directory, and the file is on that OS directory (not on your local file system).

Btw. in some cases you could better use SQL Loader to bulk load data in a table, esp. if the file is large because you can direct SQL loader to directly load the data in the datafiles, bypassing the SQL layers (generated by the INSERT statements) all together.

edited Dec 7, 2016 at 0:50

answered Dec 7, 2016 at 0:39

Rob Heusdens

1555 bronze badges

Collectives™ on Stack Overflow

How to insert into Table in Oracle 10g from Text file using PLSQL program?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related