OraFAQ Forum: SQL & PL/SQL » Issue with UTL_FILE.INVALID

Home » SQL & PL/SQL » SQL & PL/SQL » Issue with UTL_FILE.INVALID_OPERATION (11g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issue with UTL_FILE.INVALID_OPERATION [message #652092]

Wed, 01 June 2016 09:03

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

Report message to a moderator

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652093 is a reply to message #652092]

Wed, 01 June 2016 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

does your code ever close any of the file it opens?

Report message to a moderator

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652094 is a reply to message #652092]

Wed, 01 June 2016 09:37

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652100 is a reply to message #652093]

Wed, 01 June 2016 13:26

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.

Report message to a moderator

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652102 is a reply to message #652100]

Wed, 01 June 2016 13:28

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652104 is a reply to message #652100]

Wed, 01 June 2016 14:03

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652105 is a reply to message #652104]

Wed, 01 June 2016 14:58

namitanamburi
Messages: 35
Registered: March 2009

Member

Clients in my context is consumers who are getting the file.

Report message to a moderator

Re: Issue with UTL_FILE.INVALID_OPERATION [message #652193 is a reply to message #652105]

Fri, 03 June 2016 18:58

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.

Report message to a moderator

Previous Topic:	FIFO Algorithm to calculate Avg weighted value
Next Topic:	try to insert data in to table using procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:53:14 CDT 2024