You Asked

My question has been asked already at
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533980100346684832

Anyway I cannot understand why I can create in that directory a file using FOPEN, why I can copy it, why I can remove it
but cannot FRENAME

I can do all the operations, write, read everything..
Could this be a bug of Oracle between 2 mounted network dir?

and Connor said...

You can run into that with utl_file.frename because it is a rename command, a rename is *not* necessarily a move operation.

From MOS 267469.1:

"This is the expected behavior. Although this is not documented in the "Supplied PL/SQL Packages and Types Reference", this is a restriction. UTL_FILE.FRENAME calls the UNIX rename() function under the covers. Unix rename() will not able to rename file from different file system."

It will depend on what the underlying OS does when given the rename request.

For example, I did this on Windows

SQL> create directory TMP1 as 'c:\tmp';

Directory created.

SQL> create directory TMP2 as 'c:\temp';

Directory created.

SQL> create directory TMP3 as 'd:\tmp';

Directory created.

SQL> host dir > c:\tmp\my_file.dat

SQL> host dir c:\tmp\my_file.dat
 Volume in drive C is OS
 Volume Serial Number is 9CB0-0212

 Directory of c:\tmp

14/03/2017  11:15 AM             1,328 my_file.dat
               1 File(s)          1,328 bytes
               0 Dir(s)  255,950,286,848 bytes free

SQL> exec utl_file.frename('TMP1','my_file.dat','TMP2','my_file2.dat', TRUE);

PL/SQL procedure successfully completed.

SQL> host dir c:\temp\my_file2.dat
 Volume in drive C is OS
 Volume Serial Number is 9CB0-0212

 Directory of c:\temp

14/03/2017  11:15 AM             1,328 my_file2.dat
               1 File(s)          1,328 bytes
               0 Dir(s)  255,950,290,944 bytes free

SQL> host dir > d:\tmp\my_file3.dat

SQL> host dir d:\tmp\my_file3.dat
 Volume in drive D is TOSH2TB
 Volume Serial Number is 92F6-7EDE

 Directory of d:\tmp

14/03/2017  11:16 AM             1,328 my_file3.dat
               1 File(s)          1,328 bytes
               0 Dir(s)  448,014,528,512 bytes free

SQL> exec utl_file.frename('TMP3','my_file3.dat','TMP3','my_file4.dat', TRUE);

PL/SQL procedure successfully completed.

SQL> host dir d:\tmp\my_file4.dat
 Volume in drive D is TOSH2TB
 Volume Serial Number is 92F6-7EDE

 Directory of d:\tmp

14/03/2017  11:16 AM             1,328 my_file4.dat
               1 File(s)          1,328 bytes
               0 Dir(s)  448,014,528,512 bytes free

SQL> host dir c:\temp\my_file2.dat
 Volume in drive C is OS
 Volume Serial Number is 9CB0-0212

 Directory of c:\temp

14/03/2017  11:15 AM             1,328 my_file2.dat
               1 File(s)          1,328 bytes
               0 Dir(s)  255,950,290,944 bytes free

SQL> exec utl_file.frename('TMP2','my_file2.dat', 'TMP3','my_file5.dat', TRUE);

PL/SQL procedure successfully completed.

SQL> host dir d:\tmp\my_file5.dat
 Volume in drive D is TOSH2TB
 Volume Serial Number is 92F6-7EDE

 Directory of d:\tmp

14/03/2017  11:15 AM             1,328 my_file5.dat
               1 File(s)          1,328 bytes
               0 Dir(s)  448,014,524,416 bytes free

So I could rename within a drive and with a couple of local drives.

Then I did this with Linux on VirtualBox, talking from local to a mounted drive from the parent OS

SQL> create directory TMP1 as '/tmp';

Directory created.

SQL> create directory TMP2 as '/media/sf_C_DRIVE/tmp';

Directory created.

SQL> exec utl_file.frename('TMP1','my_file.dat','TMP2','my_file2.dat', TRUE);
BEGIN utl_file.frename('TMP1','my_file.dat','TMP2','my_file2.dat', TRUE); END;

*
ERROR at line 1:
ORA-29292: file rename operation failed
ORA-06512: at "SYS.UTL_FILE", line 348
ORA-06512: at "SYS.UTL_FILE", line 1290
ORA-06512: at line 1


SQL> exec utl_file.frename('TMP1','my_file.dat','TMP1','my_file2.dat', TRUE);

PL/SQL procedure successfully completed.

SQL> exec utl_file.frename('TMP2','remote_file.dat','TMP2','remote_file2.dat', TRUE);

PL/SQL procedure successfully completed.

So

- local to local rename was fine
- remote mount to remote mount rename was fine
- local rename to remote mount was not

As a workaround, you'll need to copy then delete.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ANSWERED

A reader, March 15, 2017 - 3:27 pm UTC

Thank you, Now I get it

Expected behavior?

Neil Barsema, September 20, 2020 - 9:44 am UTC

I recently ran into this issue.

You(Oracle) claim this is expected behavior, but I most vehemently disagree. It is only expected behavior if you accept that frename is nothing more than a thin wrapper for the OS rename function there is however no way for a user to know or expect this to be the case. especially since the documentation explicitly states it is "similar to the UNIX mv function"

I think you should have made it so that if the rename fails a move operation is attempted and only after that one fails raise an exception. Alternatively just use the move operation to begin with, I'm sure this logic is already present in the OS or at least it should be.

At the very least the documentation needs updating.

Just my opinion.

September 21, 2020 - 1:58 am UTC

Just my opinion.

and we always welcome all of them, even if they disagree with ours.

At the very least the documentation needs updating.

The best way to do this is to use the "Feedback" button that is on every documentation page. I've just done that for this page, but you should too, because customer feedback (rightly) carries more weight than internal feedback.

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/UTL_FILE.html#GUID-AA485F8A-3C02-448C-A92A-42F2362C49D9

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

ANSWERED

Expected behavior?

More to Explore

PL/SQL demos

PL/SQL docs

UTL_FILE FRENAME between 2 mounted directories

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

ANSWERED

Expected behavior?

More to Explore

PL/SQL demos

PL/SQL docs