Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 (Oracle 9.1)
ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361117] Tue, 25 November 2008 01:45 Go to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
Hi,
I am getting following error while excuting the procedure pasted below:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "myproc.TEST12", line 6
ORA-06512: at line 1

Procedure:

CREATE OR REPLACE PROCEDURE test12(i IN NUMBER)
AS
f UTL_FILE.file_type;
s VARCHAR2 (200);
BEGIN
f := UTL_FILE.fopen ('mydir', 'sample1.txt', 'R');
UTL_FILE.get_line (f, s);
UTL_FILE.fclose (f);
DBMS_OUTPUT.put_line (s);
END test12;
/

then execute command: exec test12(1)

I have created the directory mydir at server using command:
create directory mydir as '/appl/mydir_ex';
grant read, write on directory mydir to tamanna;

and also the directory has been created using mkdir command on the server.
Please help me regarding this. I need to solve one issue As soon as possible.

Thanking you in anticipation!

Regards,
Tamanna

Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361118 is a reply to message #361117] Tue, 25 November 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals), real one 9.1 never existed.

Directory name must be in UPPER case in utl_file call.

Regards
Michel

[Updated on: Tue, 25 November 2008 01:47]

Report message to a moderator

Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361122 is a reply to message #361118] Tue, 25 November 2008 01:52 Go to previous messageGo to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
Hi,
Thanks for your reply.
I tried with UPPER case also, but same error.
OneQuestion is it ok to specify directory name alphanumeric like MYDIR123?
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361126 is a reply to message #361122] Tue, 25 November 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can.
Did you check the parameters of each function you call for your version you still didn't post?

Regards
Michel
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361133 is a reply to message #361126] Tue, 25 November 2008 02:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you sure that the file you're trying to read exists?
Are you sure that the Oracle user on your box has permission to access that directory and file?
Have you explicitly granted privileges on that Directory to the user who is compiling the procedure?
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361134 is a reply to message #361122] Tue, 25 November 2008 02:43 Go to previous messageGo to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
Hi Michel,
How to know the oracle version?
Server is a Solaris machine and what i could find out is that the version is (9.2.0.1.0)
Is that what we were looking for?
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361136 is a reply to message #361134] Tue, 25 November 2008 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this was that, now the other questions.

Regards
Michel
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361140 is a reply to message #361133] Tue, 25 November 2008 02:54 Go to previous messageGo to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
hi,
Thanks for your reply.
yes i have given the read, write permission to the user explicitly.
can there be any other cause except permissions?

Regards,
Tamanna
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361143 is a reply to message #361140] Tue, 25 November 2008 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
HAve you checked the first two points I mentioned?

Try putting the access mode as 'r' rather than 'R' when you open the file.

How long is the line that you're going to read in? Utl_File comes with a default maximum line length of (I think) 1024. If you try to read a longer line than that it'll give you some sort of error.
Try replacing the line where you open the file with
 f := UTL_FILE.fopen ('mydir', 'sample1.txt', 'r',32767);
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361154 is a reply to message #361143] Tue, 25 November 2008 03:50 Go to previous messageGo to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
Hi,
file contains only two letters.
files exists at the location and user can access the directory and file and also the directory is visible to user. I execute below command and got the result:
SELECT * FROM ALL_DIRECTORIES

Result:
OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS MYDIR /appl/mydir_ex

So I don't think the user can not access the directory.
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361156 is a reply to message #361154] Tue, 25 November 2008 03:59 Go to previous messageGo to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
Just confirm me if executing below command is enough for giving sufficient priviledges to user?

grant read, write on MYDIR to tamanna;

Or anything else needs to be done for the permisisons?
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361161 is a reply to message #361156] Tue, 25 November 2008 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The permissions to the OS level Oracle user (ie the user under who's account the Oracle database runs) needs to have OS level permission to access the file and directory.

This is completely independent of privileges granted on the Directory object inside the database.
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361167 is a reply to message #361161] Tue, 25 November 2008 04:29 Go to previous messageGo to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
ok..
so how the OS level permissions to access the files and directories can be given to the Oracle user?

Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361233 is a reply to message #361167] Tue, 25 November 2008 08:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You haven't told us what the OS is, but based on the path you used in creating the directory, I'll guess it's Unix/Linux of some flavour.

Here is an intro into linux file permissions, what they mean, and how to set them.
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361392 is a reply to message #361233] Wed, 26 November 2008 03:59 Go to previous messageGo to next message
tamanna
Messages: 8
Registered: November 2008
Junior Member
Hi All,

Thanks a lot for solutions.
I got my problem resolved by entering the utl_file_dir parameter in init.ora and granting the privilege to public on that particular directory.

I guess the problem was due to permissions only as you all mentioned.
Thanks a lot for your help!

Regards,
Tamanna
Re: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449 [message #361394 is a reply to message #361392] Wed, 26 November 2008 04:17 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Since you are using oracle 9 you could use directory objects instead of utl_file_dir parameter. Utl_file_dir parameter is deprecated. Please don't use it.

Regards

Raj
Previous Topic: query
Next Topic: need to convert a set of sql scripts into pl\sql program which needs to be triggered automatically
Goto Forum:
  


Current Time: Mon Dec 05 09:10:25 CST 2016

Total time taken to generate the page: 0.07221 seconds