This forum is currently locked. You can't register or post questions at this time. (read more)

Reading file with UTL_FILE

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

luciano.aol
Member
Posts: 1
Joined: Mon Jul 08, 2013 11:56 am

Reading file with UTL_FILE

Postby luciano.aol » 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

DECLARE
arquivo_ler UTL_File.File_Type;
Linha Varchar2 (1000);
BEGIN
arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
Loop
   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.');
END;
/


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

SELECT *
FROM ALL_TAB_PRIVS
WHERE table_name = 'INTRANET_LOAD';


Grateful for any help.

Luciano

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

Re: Reading file with UTL_FILE

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

Hi.

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

CREATE OR REPLACE DIRECTORY INTRANET_LOAD AS '/tmp/';


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

Cheers

Tim...
Tim...
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: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests

cron