Reading file with UTL_FILE

All posts relating to Oracle PL/SQL development.

Moderator: Tim...
Posts: 1
Joined: Mon Jul 08, 2013 11:56 am

Reading file with UTL_FILE

Postby » Mon Jul 08, 2013 12:05 pm

Hello good day.

I'm trying to utilize the utl file to read a txt file and import the data into a table in Oracle.
I've read in various forums and have researched a lot on oracle documentation site and on the internet but can not find the answer to the problem.

The source follows:
Set serveroutput on

Code: Select all

arquivo_ler UTL_File.File_Type;
Linha Varchar2 (1000);
arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
   UTL_File.Get_Line (arquivo_ler, Linha);
   dbms_output.put_line (Linha);
End Loop;
UTL_File.Fclose (arquivo_ler);
DBMS_OUTPUT.PUT_LINE ('File processed with sucess.');

The errors:

Code: Select all

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

What has been done:

Created DIRECTORY (INTRANET_LOAD) and given the GRANT read, write to the user
On Linux where Oracle is installed, was given full access to the Oracle user folder: / u01/app/oracle/product/11.2.0/db_1/adp

When writing the query;

Code: Select all

WHERE table_name = 'INTRANET_LOAD';

Grateful for any help.


Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Reading file with UTL_FILE

Postby Tim... » Mon Jul 08, 2013 3:48 pm


Well, this is obviously not the code you are running as it has a bunch of errors in it. Also, the path you say your directory object is pointing to has a typo also...

Ignoring those issues, these sort of errors *always* come down to one of several things:

- The directory object does not exist.
- The use performing the action does not have permissions on the directory oject.
- The directory object does not point to a real physical directory.
- The Oracle user does not have the relevant permissions on the physical directory.

The latter is not always as clear cut as it sounds, as the permissions have to cascade through the directory structure. It is always best to start off with something simple, like writing to "/tmp", which you know will have permissions. So try pointing the directory object to that and repeating your test.

Code: Select all


If this works, you know your problem was with the directory you were specifying.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests