Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29283: invalid file operation (Oracle 10g Enterprise, HPUX-B.11.11)
ORA-29283: invalid file operation [message #612243] Tue, 15 April 2014 04:30 Go to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Hi All,

I'm having an issue with the following Procedure;
create or replace
PROCEDURE SP_IF334 
(
  AUD_ROWID IN VARCHAR2  
, FILE_PREFIX IN VARCHAR2  
, FILE_NAME OUT VARCHAR2  
) AS 
FoundGtransNum number(12);
fHandle  UTL_FILE.FILE_TYPE;
BEGIN
  dbms_output.put_line(' - IF334: Entered at '||to_char(sysdate,'dd/mm/yy hh24:mi:ss'));
  
  select gtrans_num
  into   FoundGtransNum
  from   user_ax.gtrans_trig
  where  rowid = AUD_ROWID;
  
  FILE_NAME := FILE_PREFIX||FoundGtransNum||'.csv';
  
  dbms_output.put_line(' - IF334: Filename: '||FILE_NAME);
  
  fHandle := UTL_FILE.FOPEN('AX_OUT', FILE_NAME, 'W');

  UTL_FILE.PUT_LINE(fHandle, 'This is the first line');
  UTL_FILE.PUT_LINE(fHandle, 'This is the second line');
  UTL_FILE.PUT_LINE(fHandle, 'This is the third line');

  UTL_FILE.FCLOSE(fHandle);
  
  
  dbms_output.put_line(' - IF334: Finished at '||to_char(sysdate,'dd/mm/yy hh24:mi:ss'));
END SP_IF334;


When I call this procedure, I get the following messages:
Error report:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "USER_AX.SP_IF334", line 23
ORA-06512: at line 130
29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.


The calling code for this procedure is as follows;
        elsif found_if.if_ref = 'IF334' then
          --IF334: Stock Movement
          dbms_output.put_line(' - Calling IF334');
          dbms_output.put_line(' - audit_record.rowid      : '||audit_record.rowid);
          dbms_output.put_line(' - found_if.output_prefix  : '||found_if.output_prefix);
          user_ax.SP_IF334(audit_record.rowid,found_if.output_prefix,res_output_filename);
          dbms_output.put_line(' - Procedure SP_IF334 completed. Filename returned is '''||res_output_filename||'''');


and the dbms output related to this section is as follows;
 - Calling IF334
 - audit_record.rowid      : AAAh6+AAvAAEmWkAAM
 - found_if.output_prefix  : 334_StkMove_
 - IF334: Entered at 15/04/14 10:26:12
 - IF334: Filename: 334_StkMove_8122560.csv


This is on a HPUX box, and the permissions of the directory are 777 (RWXRWXRWX) however i can't work out whats going wrong. Please Help!!!
Re: ORA-29283: invalid file operation [message #612245 is a reply to message #612243] Tue, 15 April 2014 04:47 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
What does 'AX_OUT' refer to in your code?
...
fHandle := UTL_FILE.FOPEN('AX_OUT', FILE_NAME, 'W');
...

Is that a directory alias created by CREATE DIRECTORY statement?
Re: ORA-29283: invalid file operation [message #612246 is a reply to message #612245] Tue, 15 April 2014 04:54 Go to previous messageGo to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Sorry, Yes;

select * from dba_directories where directory_name = 'AX_OUT';

OWNER   DIRECTORY_NAME   DIRECTORY_PATH
SYS	AX_OUT	         /procdi/tmpeoU/AX/
Re: ORA-29283: invalid file operation [message #612248 is a reply to message #612246] Tue, 15 April 2014 05:02 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
The owner is SYS, the user running the program is also SYS? otherwise you need also to grant proper access to this directory object (oracle level)
http://psoug.org/reference/directories.html
Re: ORA-29283: invalid file operation [message #612250 is a reply to message #612248] Tue, 15 April 2014 05:07 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
For more information about options and syntax of the GRANT statement
Re: ORA-29283: invalid file operation [message #612251 is a reply to message #612248] Tue, 15 April 2014 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A directory has no owner and, as for all these kinds of not-owned objects, the owner value is returned to SYS because owner# column is declared as NOT NULL and so it is set to 0 which is SYS id.

This does not change the fact you must have the appropriate privilege to use it.

Re: ORA-29283: invalid file operation [message #612252 is a reply to message #612250] Tue, 15 April 2014 05:16 Go to previous messageGo to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Hi Dariyoosh,

Please see below, as i'd already granted permissions. Sorry I wasn't sure how much detail I should post in the first comment!
select grantor, 
       grantee, 
       table_schema, 
       table_name, 
       privilege
from   all_tab_privs 
where  grantee = 'USER_AX';


Result:
GRANTOR     GRANTEE     TABLE_SCHEMA    TABLE_NAME    PRIVILEGE 
----------- ----------- --------------- ------------- ------------ 
PRO         USER_AX     SYS             AX_OUT        EXECUTE 
PRO         USER_AX     SYS             AX_OUT        READ 
PRO         USER_AX     SYS             AX_OUT        WRITE 
Re: ORA-29283: invalid file operation [message #612253 is a reply to message #612251] Tue, 15 April 2014 05:36 Go to previous messageGo to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Michel Cadot wrote on Tue, 15 April 2014 11:12

A directory has no owner and, as for all these kinds of not-owned objects, the owner value is returned to SYS because owner# column is declared as NOT NULL and so it is set to 0 which is SYS id.

This does not change the fact you must have the appropriate privilege to use it.


Thanks Michel,

I suspected as much, as directories on other servers we have are all owned by SYS, irrespective of their 'Real' owner in terms of what they're used for. I was typing my reply to Dariyoosh as you replied, but i've already granted permissions on the directory and so this shouldn't be the cause I don't think... but willing to be corrected!
Re: ORA-29283: invalid file operation [message #612254 is a reply to message #612252] Tue, 15 April 2014 05:38 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Inside your code, add the following put_line right after the filename concatenation
...
FILE_NAME := FILE_PREFIX||FoundGtransNum||'.csv';
sys.dbms_output.put_line('The file name is: ' || FILE_NAME);

Post the output here or rather check whether it is really set to what you expect. I don't really see the point of this concatenation with the FILE_PREFIX. This is ignored by fopen according to the documentation.

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/u_file.htm#i1003526
Quote:

...
File name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is ignored by FOPEN. On Unix, the filename cannot end with /.
...

[Updated on: Tue, 15 April 2014 05:38]

Report message to a moderator

Re: ORA-29283: invalid file operation [message #612255 is a reply to message #612254] Tue, 15 April 2014 05:48 Go to previous messageGo to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Hi Dariyoosh,

The concatenation is simply as the call to each procedure also passes through a filename 'prefix', it's not a directory at all. I added the output lines as requested, please see the output below;

 - Calling IF334
 - audit_record.rowid      : AAAh6+AAvAAEmWkAAM
 - found_if.output_prefix  : 334_StkMove_
 - IF334: Entered at 15/04/14 11:45:24
The file name is: 334_StkMove_8122560.csv
The file prefix is: 334_StkMove_
The FoundGtransNum is: 8122560
 - IF334: Filename: 334_StkMove_8122560.csv


As you can see this is just building the output filename, with the first 'part' being passed to the procedure, and the second part being retrieved from a table.

[Updated on: Tue, 15 April 2014 05:48]

Report message to a moderator

Re: ORA-29283: invalid file operation [message #612256 is a reply to message #612243] Tue, 15 April 2014 06:16 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Please see if you have necessay privileges on the mentioned directory
/procdi/tmpeoU/AX/
if you are not very sure just try chmod 777 intially (only for testing, have to set it correctly otherwise), hope it helps.

Regards,
Pointers
Re: ORA-29283: invalid file operation [message #612257 is a reply to message #612256] Tue, 15 April 2014 06:19 Go to previous messageGo to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Hi Pointers,

Unfortunately this is already set as 777. I think i mentioned that in my original post Smile
Re: ORA-29283: invalid file operation [message #612259 is a reply to message #612256] Tue, 15 April 2014 06:21 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
pointers wrote on Tue, 15 April 2014 13:16
if you are not very sure just try chmod 777 intially (only for testing, have to set it correctly otherwise), hope it helps.


I believe that the OP specified that clearly at the beginning Smile

"bryant1003 wrote Tue, 15 April 2014 11:30"

...
This is on a HPUX box, and the permissions of the directory are 777 (RWXRWXRWX) however i can't work out whats going wrong
...


[EDIT]: didn't see bryan reply !

[Updated on: Tue, 15 April 2014 06:22]

Report message to a moderator

Re: ORA-29283: invalid file operation [message #612264 is a reply to message #612257] Tue, 15 April 2014 07:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
bryant1003 wrote on Tue, 15 April 2014 07:19
Hi Pointers,

Unfortunately this is already set as 777. I think i mentioned that in my original post Smile


Sanity check: Does directory /procdi/tmpeoU/AX/ exist on your client box or on database server box (assuming they are not same box)? UTL_FILE is PL/SQL package and runs on database server side. It can't access clent side files (unless client connects from database server).

Now about 777. Having 777 on /procdi/tmpeoU/AX/ is not enough. UTL_FILE accesses OS files as OS user oracle (user Oracle was installed under, to be precise). So if user oracle can't read /procdi/tmpeoU or /procdi having 777 on /procdi/tmpeoU/AX/ will not help you.

SY.
Re: ORA-29283: invalid file operation [message #612266 is a reply to message #612264] Tue, 15 April 2014 08:10 Go to previous messageGo to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Hi Solomon,

Thanks for your reply - the permissions are 777 for the entire tree, and the oracle user (when logged in to the shell) can traverse and write to this folder location. The directory resides on the database server, not the client.
Re: ORA-29283: invalid file operation [message #612269 is a reply to message #612266] Tue, 15 April 2014 08:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post result from follow OS commands

df -k
ls -ltr /procdi/tmpeoU/AX/
Re: ORA-29283: invalid file operation [message #612271 is a reply to message #612266] Tue, 15 April 2014 08:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Any chance it is NFS/NAS mounted volume?

SY.
Re: ORA-29283: invalid file operation [message #612273 is a reply to message #612271] Tue, 15 April 2014 08:45 Go to previous messageGo to next message
bryant1003
Messages: 8
Registered: April 2014
Junior Member
Hi Solomon,

It's a mount to a 'dumb' EVA with no ACL etc setup on it - all permission management is done through the HPUX server (hosting the database). Also, the same user (with same permissions) can use the same functionality exposed through UTL_FILE on a different server, which mounts to the same EVA. Is there anything above the SID level that controls access to this package?
Re: ORA-29283: invalid file operation [message #612275 is a reply to message #612273] Tue, 15 April 2014 08:49 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
OK. Can you login to db server as user oracle and create a file in /procdi/tmpeoU/AX directory.

SY.
Previous Topic: How to check row count of sys_refcursor
Next Topic: how to use joins in hierarchical queries
Goto Forum:
  


Current Time: Fri Apr 19 00:51:10 CDT 2024