Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE.FRENAME & INVALID_OPERATION (ORACLE 9.2.0.1.0)
UTL_FILE.FRENAME & INVALID_OPERATION [message #309800] Fri, 28 March 2008 20:53 Go to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
Confused

OK. I think I'm following the rules. I've been searching for 2 full days. I checked here and saw most of the references related to FRENAME were in this forum.

This instance is Oracle 9.2.0.1.0. I am executing in SQL*Plus.

I am processing XML files in PL/SQL. Once we are done with a file we want to move it from one directory (SOURCE) to another (TARGET).

We have a JAVA routine (inherited) but when a file exists by that name in the Target directory, the routine does nothing - there is no error processing. So I was looking for alternatives so I don't have to overly complicate the moving of the files.

I found UTL_FILE.FRENAME and tried using it.
I tried testing it a bunch of different ways and every time, I got an INVALID_OPERATION (29283) "file could not be opened or operated on". This happened repeatedly.

So today I used other UTL_FILE procedures to verify that I had READ and WRITE access to both directories and I do have access (I thought that might have been the problem).

Finally I got it to work. . .BUT - and this is a BIG BUT. . . it works VERY inconsistently. Right after it worked, the next 5 times it failed with the same INVALID_OPERATION (29283). And in most cases I am still getting this error.
Oh, and I've tried with the File existing in the Target, as well as when the file is NOT in the Target.
I've verified that the Source file always has existed (in the source directory). And I thought maybe it only worked if the Source File name was different than the Target File name - but no - GRRRRR - sometime that worked, other times it didn't.

I would appreciate any ideas on what could be causing this problem. I am on deadline and I cannot put this into Production if it is so unstable.

THANKS!

This database instance is Oracle 9.2.0.1.0

And a sample (one of the many) of the code is here:

DECLARE
BEGIN
  dbms_output.put_line('B4 ' || sqlcode);
  utl_file.frename('N2I_RECV','PROBCK22.XML','N2I_RECVHOLD','PROBCK22.XML',TRUE);
  dbms_output.put_line('AFT ' || sqlcode);
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
           DBMS_OUTPUT.PUT_LINE('INVALID_PATH');
           utl_file.fclose_all;
   WHEN UTL_FILE.INVALID_FILENAME  THEN
           DBMS_OUTPUT.PUT_LINE('INVALID FILENAME');
           utl_file.fclose_all;
   WHEN UTL_FILE.RENAME_FAILED THEN
           DBMS_OUTPUT.PUT_LINE('RENAME FAILED');
           utl_file.fclose_all;
   WHEN UTL_FILE.INVALID_OPERATION THEN
           DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');
           utl_file.fclose_all;
   WHEN UTL_FILE.ACCESS_DENIED THEN
           DBMS_OUTPUT.PUT_LINE('ACCESS DENIED');
           utl_file.fclose_all;
   WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('OTHER UTL_FILE ERROR' || sqlcode || substr(sqlerrm,1,150));
           utl_file.fclose_all;
END ;

USR@NQR> /
B4 0
INVALID OPERATION
Re: UTL_FILE.FRENAME & INVALID_OPERATION [message #309817 is a reply to message #309800] Sat, 29 March 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
OK. I think I'm following the rules.

No, you can't YES to the 3 questions posted in Not an EXPERT? Post in the NEWBIES forum, NOT here.

Add RAISE in at least "WHEN OTHERS", only a newbie can made this error.

As we don't know your environement, we can't reproduce what you have and can't answer.
For instance, are your file systems local or remote/network ones?

Regards
Michel

[Updated on: Sat, 29 March 2008 01:19]

Report message to a moderator

Re: UTL_FILE.FRENAME & INVALID_OPERATION [message #309826 is a reply to message #309800] Sat, 29 March 2008 01:36 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
We really need to see a copy and paste of a complete session showing that the source file exists and you have read and write privileges to the directories and reproducing the error, as I have done below. You do realize that once you have used frename, the source file is gone and therefore will produce an error the next 5 or whatever times, until you recreate the source file, don't you? That is what I have reproduced below and suspect may be happening. Since you have been able to do it once, it tends to indicate that it is not a permission problem, although there may be other issues, such as the file still being opened or being shared.

SYS@orcl_11g> CREATE OR REPLACE DIRECTORY N2I_RECV AS 'c:\oracle11g'
  2  /

Directory created.

SYS@orcl_11g> GRANT READ, WRITE ON DIRECTORY N2I_RECV TO scott
  2  /

Grant succeeded.

SYS@orcl_11g> CREATE OR REPLACE DIRECTORY N2I_RECVHOLD AS 'c:\temp'
  2  /

Directory created.

SYS@orcl_11g> GRANT READ, WRITE ON DIRECTORY N2I_RECVHOLD TO scott
  2  /

Grant succeeded.

SYS@orcl_11g> CONNECT scott/tiger
Connected.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> SELECT DBMS_LOB.FILEEXISTS (BFILENAME ('N2I_RECV', 'PROBCK22.XML')) FROM DUAL
  2  /

DBMS_LOB.FILEEXISTS(BFILENAME('N2I_RECV','PROBCK22.XML'))
---------------------------------------------------------
                                                        1

SCOTT@orcl_11g> BEGIN
  2    utl_file.frename('N2I_RECV','PROBCK22.XML','N2I_RECVHOLD','PROBCK22.XML', TRUE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT DBMS_LOB.FILEEXISTS (BFILENAME ('N2I_RECV', 'PROBCK22.XML')) FROM DUAL
  2  /

DBMS_LOB.FILEEXISTS(BFILENAME('N2I_RECV','PROBCK22.XML'))
---------------------------------------------------------
                                                        0

SCOTT@orcl_11g> BEGIN
  2    utl_file.frename('N2I_RECV','PROBCK22.XML','N2I_RECVHOLD','PROBCK22.XML', TRUE);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 336
ORA-06512: at "SYS.UTL_FILE", line 1214
ORA-06512: at line 2


SCOTT@orcl_11g>

Previous Topic: How to create month?
Next Topic: What is bind variable?where its stored?
Goto Forum:
  


Current Time: Fri Dec 09 02:01:28 CST 2016

Total time taken to generate the page: 0.09531 seconds