Home » SQL & PL/SQL » SQL & PL/SQL » Issue with UTL_FILE.INVALID_OPERATION (11g )
Issue with UTL_FILE.INVALID_OPERATION [message #652092] Wed, 01 June 2016 09:03 Go to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Hello,

I have a package which

identifies clients that need to receive files.

LOOP
Calls a procedure that opens 7 files per client.
Writes to the files.
Then closes the files.
End loop


I am encountering issue while opening 7th file of 44th client, it is not opening 7th file and ends up with error

Unable to open file due to UTL_FILE.INVALID_OPERATION

Solutions Tried:

In order to identify if 44th client is cause, I deleted the 44th client and encountered same issue with 45th client.

I am using a simple OPEN_NEW_FILE to open the files by passing in (location, file_name, 'W')

FUNCTION OPEN_NEW_FILE(P_LOCATION              VARCHAR2,
                     P_FNAME                 VARCHAR2,
                     P_FP             IN OUT UTL_FILE.FILE_TYPE,
                     P_MODE                  VARCHAR2 := 'w'
                    ) 
  RETURN VARCHAR2 IS
  BEGIN
    
       p_fp := utl_file.fopen(p_location, p_fname, p_mode);
    
    RETURN '';
  EXCEPTION
    WHEN UTL_FILE.READ_ERROR        THEN RETURN 'Unable to open file due to UTL_FILE.READ_ERROR';
    WHEN UTL_FILE.WRITE_ERROR       THEN RETURN 'Unable to open file due to UTL_FILE.WRITE_ERROR';
    WHEN UTL_FILE.INVALID_PATH      THEN RETURN 'Unable to open file due to UTL_FILE.INVALID_PATH';
    WHEN UTL_FILE.INVALID_MODE      THEN RETURN 'Unable to open file due to UTL_FILE.INVALID_MODE';
    WHEN UTL_FILE.INVALID_OPERATION THEN RETURN 'Unable to open file due to UTL_FILE.INVALID_OPERATION';
    WHEN UTL_FILE.INTERNAL_ERROR    THEN RETURN 'Unable to open file due to UTL_FILE.INTERNAL_ERROR';
    WHEN OTHERS                     THEN RETURN 'Unable to open file in OPEN_FILE function';
  END;




I am using the same function to open 7 different files for each client, 1st, 2nd ....43 times I have no issues to open file, once I hit 44 I ran into this.

Is there a limit I am reaching, please advise. I checked the space on the server and that is not an issue.

Thank You
Namita


Re: Issue with UTL_FILE.INVALID_OPERATION [message #652093 is a reply to message #652092] Wed, 01 June 2016 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does your code ever close any of the file it opens?
Re: Issue with UTL_FILE.INVALID_OPERATION [message #652094 is a reply to message #652092] Wed, 01 June 2016 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-29283: 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.
Re: Issue with UTL_FILE.INVALID_OPERATION [message #652100 is a reply to message #652093] Wed, 01 June 2016 13:26 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Yes, Its a loop........

we start the loop , open the file, write and then close it.

for the next client same thing, open the file, write and then close it.
Re: Issue with UTL_FILE.INVALID_OPERATION [message #652102 is a reply to message #652100] Wed, 01 June 2016 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So rest the official "cause" and you have to do the "action" part.

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652104 is a reply to message #652100] Wed, 01 June 2016 14:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3067
Registered: January 2010
Location: Connecticut, USA
Senior Member
UTL_FILE is executed on database server, not on client machine. So it's not clear what do you mean by client. Are clients connecting to same database or not? Are file names client specific, if clients connects to same database?

SY.
Re: Issue with UTL_FILE.INVALID_OPERATION [message #652105 is a reply to message #652104] Wed, 01 June 2016 14:58 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Clients in my context is consumers who are getting the file.
Re: Issue with UTL_FILE.INVALID_OPERATION [message #652193 is a reply to message #652105] Fri, 03 June 2016 18:58 Go to previous message
namitanamburi
Messages: 35
Registered: March 2009
Member
I found the solution to this.

I was not closing 3rd file which is only opened if consumer requests it, however the error was pointing to 7th file so it was not very obvious, once I started closing the 3rd file (if open), the problem got resolved.
Previous Topic: FIFO Algorithm to calculate Avg weighted value
Next Topic: try to insert data in to table using procedure
Goto Forum:
  


Current Time: Sun Aug 01 17:19:03 CDT 2021