Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~
UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #154849] Wed, 11 January 2006 00:43 Go to next message
pravind
Messages: 4
Registered: January 2006
Junior Member
Hello,

We are using Oracle 10g Enterprise Edition Release 10.1.0.4.0.

Have a problem when writing to the file system using UTL_FILE package.

The error message is: INVALID_OPERATION, ORA-29283

We have done the following.
1. The target directory is world write-able "drwxrwxrwx" so that oracle can write to it.
2. Created the directory using the "CREATE DIRECTORY" command
3. Set the UTL_FILE_DIR to "*". (For testing purposes)

Below is the code that we are testing.

Create or Replace Procedure file_write is
log_handle Utl_File.File_Type;
log_name varchar2(100) := 'file_write_test.log';
log_path varchar2(100) := '/export/home/bscsix/WORK/LOG';
Begin
log_handle := Utl_File.fopen( log_path, log_name, 'W');
Utl_File.Put_Line (log_Handle,sysdate);
Utl_File.fclose( log_handle );
Exception
when UTL_FILE.INVALID_OPERATION
Then dbms_output.put_line (SQLERRM);
End file_write;
Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #154990 is a reply to message #154849] Wed, 11 January 2006 11:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
The command "create or replace directory ..." is somewhat misleading because it does not actually create a directory. It creates an Oracle directory object that points to a directory path. Oracle will accept any string for that path and does not attempt to validate it until it is used. That directory must already exist in the operating system. The directory object, like other Oracle objects is created in upper case by default and so must be referenced in upper case. Appropriate privileges on the directory object must be granted. It is also a good idea to provide a file extension, such as .txt for your file name in order to avoid possible confusion due to a default extension being provided. Please see the demonstration below. You may wish to add your own exception section and you may also wish to add utl_file.is_open. This is just a partial demonstration. Also, the strings may be used without assignment to variables. I only used the variables for similarity to what you were doing. The usage of the utl_file_dir parameter was for Oracle version 8i and earlier. Since Oracle 9i, you have to use directory objects, instead of directory paths.

-- create the directory in the operating system:
sys@ORA92> HOST MD c:\log


-- create an Oracle directory object for the directory path:
sys@ORA92> CREATE OR REPLACE DIRECTORY your_dir AS 'c:\log'
  2  /

Directory created.


-- observe that the directory object is created in upper case:
sys@ORA92> COLUMN directory_path FORMAT A15
sys@ORA92> SELECT * FROM all_directories WHERE	directory_name = 'YOUR_DIR'
  2  /

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ---------------
SYS                            YOUR_DIR                       c:\log


-- grant privileges on the Oracle directory object to the user:
sys@ORA92> GRANT READ, WRITE ON DIRECTORY my_dir TO scott
  2  /

Grant succeeded.


-- create your procedure:
sys@ORA92> CONNECT scott/tiger
Connected.
sys@ORA92> @ LOGIN
sys@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott@ORA92

scott@ORA92> CREATE OR REPLACE PROCEDURE file_write
  2  AS
  3    v_log_handle UTL_FILE.FILE_TYPE;
  4    v_log_path   VARCHAR2(30) := 'YOUR_DIR'; -- YOUR_DIR must be in upper case
  5    v_log_name   VARCHAR2(30) := 'file_write_test_log.txt'; -- provide txt or other extension
  6  BEGIN
  7    v_log_handle := UTL_FILE.FOPEN (v_log_path, v_log_name, 'W');
  8    UTL_FILE.PUT_LINE (v_log_handle, TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24MI'));
  9    UTL_FILE.FCLOSE (v_log_handle);
 10  END file_write;
 11  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.


-- execute your procedure:
scott@ORA92> EXECUTE file_write

PL/SQL procedure successfully completed.

scott@ORA92>

[Updated on: Wed, 11 January 2006 11:22]

Report message to a moderator

Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #155403 is a reply to message #154990] Mon, 16 January 2006 00:07 Go to previous messageGo to next message
pravind
Messages: 4
Registered: January 2006
Junior Member
Hi,

Thank you for your response. We were able to work around the problem. However, we have another issue now.

Oracle is able to write the file however it does so with with the following privileges.
-rw-r----- 1 oracle dba 10 Jan 13 15:43 file_write_test13-01-2006:15-43-07.log
-rw-r----- 1 oracle dba 10 Jan 13 15:59 file_write_test13-01-2006:15-59-23.log
-rw-r----- 1 oracle dba 10 Jan 13 16:27 file_write_test13-01-2006:16-27-05.log
-rw-r----- 1 oracle dba 10 Jan 13 17:32 file_write_test13-01-2006:17-32-50.log
-rw-r----- 1 oracle dba 10 Jan 13 17:35 file_write_test13-01-2006:17-35-29.log

Now no other user is able to read these file.

What could be possible reason?

Looking forward to your comments.

Pravind
PS: We are using Oracle 10g Enterprise Edition Release 10.1.0.4.0.
Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #155507 is a reply to message #155403] Mon, 16 January 2006 11:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
What code did you use to write the files? Did you end with utl_file.fclose, so that the file is not locked? How are the other users trying to read the files, through Oracle or something else? What error message do they get when they try to read the files? Did you grant read privileges to the other users, through the operating system and through Oracle? What operating system are you using?



Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #155519 is a reply to message #155403] Mon, 16 January 2006 21:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
The following is a continuation of the previous demonstration, showing reading of the same file by another user with proper permissions.

sys@ORA92> CREATE USER test IDENTIFIED BY test
  2  /

User created.

sys@ORA92> GRANT CONNECT, RESOURCE TO test
  2  /

Grant succeeded.

sys@ORA92> GRANT READ ON DIRECTORY your_dir TO test
  2  /

Grant succeeded.

sys@ORA92> CONNECT test/test
Connected.
sys@ORA92> @ LOGIN
sys@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
test@ORA92

test@ORA92> SET SERVEROUTPUT ON
test@ORA92> DECLARE
  2    v_log_handle UTL_FILE.FILE_TYPE;
  3    v_log_path   VARCHAR2(30) := 'YOUR_DIR';
  4    v_log_name   VARCHAR2(30) := 'file_write_test_log.txt';
  5    v_text	    VARCHAR2(250);
  6  BEGIN
  7    v_log_handle := UTL_FILE.FOPEN (v_log_path, v_log_name, 'R');
  8    UTL_FILE.GET_LINE (v_log_handle, v_text, 100);
  9    UTL_FILE.FCLOSE (v_log_handle);
 10    DBMS_OUTPUT.PUT_LINE (v_text);
 11  END;
 12  /
16-JAN-2006 0916

PL/SQL procedure successfully completed.

test@ORA92>  

Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #155520 is a reply to message #155519] Mon, 16 January 2006 22:01 Go to previous messageGo to next message
pravind
Messages: 4
Registered: January 2006
Junior Member
Hi Brabara,

Apologies for not being clear in my earlier post.

We are running Oracle 0g Enterprise Edition Release 10.1.0.4.0 on Sun Solaris

As mentioned in my previous posts, we created a procedure that calls the Oracle supplied UTL_FILE package to create and write files. The file is closes using Utl_File.Fclose.

We try to “vi” the file to read it and we get the permission denied message. The directory into which oracle writes the file belongs to the user that is reading the file.

It’s like this. We have an application user called “apps” that owns a directory called “apps/log”. Now Oracle files that’s written into “apps/log” has the “-rw-r-----“ permission.

One thing to note here is that the user "apps" is not an oracle user, it's a UNIX user. So will the command "GRANT READ ON DIRECTORY dir_name TO apps" do anything?

Secondly, the directory belongs to unix user "apps"

Does this have anything to do with the umask of user "apps"?

Regards,
Pravind
Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #155524 is a reply to message #155520] Mon, 16 January 2006 23:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Under those circumstances, the Oracle command "GRANT READ ON DIRECTORY dir_name TO apps;" would not help. I am unable to reproduce the error, but I am using Windows. After writing to the file, I can use any text editor to read the file in the operating system. Hopefully someone else can help you.
Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #155528 is a reply to message #155524] Tue, 17 January 2006 00:13 Go to previous messageGo to next message
pravind
Messages: 4
Registered: January 2006
Junior Member
Hello Barbara,

No problem. Thank you very much for your time.

Regards,
Pravind.
Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #203756 is a reply to message #154990] Thu, 16 November 2006 04:01 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Hello,

I was trying to learn from the conv.

I tried this got this error plz help
begin
file_write;
end;
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "SCOTT.FILE_WRITE", line 7
ORA-06512: at line 2

Re: UTL_FILE Package, INVALID_OPERATION Exception. ~~@~~ [message #203881 is a reply to message #203756] Thu, 16 November 2006 18:14 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
I would need to see a complete copy and paste of everything you ran, including creation of the directory object and procedure. I would also need to know whether the directory exists on your server, not your local machine, and when there are sufficient privileges.
Previous Topic: Escape ( and )
Next Topic: Why are these different?
Goto Forum:
  


Current Time: Tue Dec 06 16:20:52 CST 2016

Total time taken to generate the page: 0.08573 seconds