Skip to Main Content
  • Questions
  • UTL FILE FRENAME (mv) doesn't work - ORA-29292

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 13, 2017 - 2:04 pm UTC

Last updated: March 14, 2017 - 3:22 am UTC

Version: 11

Viewed 1000+ times

You Asked

I have a a shared sftp directory mounted

\xx.xx.xx.xx\SFTP_AX\ /sftp/ cifs user,uid=54321,gid=54321,suid,username=user,password=pass,workgroup=application,file_mode=0775,dir_mode=0775,rw 0 0
Then a PL/SQL that moves my file to another mounted directory using UTL_FILE

Here's an example

Here how I created the directory

CREATE OR REPLACE DIRECTORY SFTP_DIR AS '/sftp/';

GRANT READ, WRITE ON DIRECTORY SFTP_DIR TO SCHEMA1;

GRANT READ, WRITE ON DIRECTORY SFTP_DIR TO SCHEMA2;
Here ls -l /sftp and /ext/files (775)

drwxrwxr-x 2 oracle oinstall 4096 Mar 13 14:24 sftp
drwxrwxr-x 6 oracle oinstall 4096 Mar 13 14:40 ext/files
So, when I execute

DECLARE
sftp_folder VARCHAR2(50) := 'SFTP_DIR';
l_file_test CONSTANT VARCHAR2(50) := 'test.txt';
external_dir VARCHAR2(50) := 'EXTERNAL_DIR';
BEGIN
UTL_FILE.FRENAME(sftp_folder, l_file_test, external_dir, l_file_test, true);
END;
I get

00000 - "file rename operation failed" *Cause: A file rename attempt was refused by the operating system either because the source or destination directory does not exist or is inaccessible, or the source file isn't accessible, or the destination file exists. *Action: Verify that the source file, source directory, and destination directory exist and are accessible, and that the destination file does not already exist.
I've checked everything, the files exists, the permissions are right (IMO) etc

Since I can't figure out, I took a approach like this I copy my file in ext/files (using FCOPY and it works) then I remove my file using FREMOVE..and guess what..this works How this can be possible? en fact mv it's like copying than removing the file..

So FREMOVE works but FRENAME doesn't..

Any ideas?

Thank you

and Chris said...

Are you sure the oracle OS user has write permissions on the file in the sftp directory?

As discussed in MOS note 458641.1, this is a prerequisite for frename:


This issue was reported in Bug:5578602 where it was determined that this is an expected behaviour due to deliberate coding.

This happens because utl_file deliberately tries to open the source file in append mode before it attempts the rename. When files are created through utl_file they are always read/write so this is an added level of security to prevent you moving a file that definitely wasn't created by oracle to start with.

So in case 1, though the file is owned by oracle user, it does not have write permissions on the file. Hence there is an ambiguity of whether oracle created/owned this file. So it failed.

Similarly in case 2, the file is owned by root user and the group is also different from the one to which oracle user belongs to. Moreover the fact that the directory is owned by oracle is also unknown. Hence it failed.

Rating

  (5 ratings)

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

Comments

A reader, March 13, 2017 - 3:14 pm UTC

Hi,

Thank you for answering

Well, I can write files..
I've tested running touch namefile etc

running as oracle user..
Chris Saxon
March 13, 2017 - 3:20 pm UTC

What exactly have you done?

A reader, March 13, 2017 - 3:19 pm UTC

Also I've tested FOPEN..but nothing

A reader, March 13, 2017 - 3:32 pm UTC

I've created a test file via bash and via pl sql

Same results, I cannot move directly..

The strangest thing is that I can remove the file..via FREMOVE but not FRENAME..

-rwxrwxr-x 0 oracle oinstall 0 Mar 13 16:24 test2



Connor McDonald
March 14, 2017 - 3:22 am UTC

Check the permissions on the parent directory as well.

Are you saying bash cannot do a move as well ? That would suggest there's an OS issue, aka, some security mapping issue between linux and Windows. For example, you move request is getting rejected by the Windows server.

A reader, March 13, 2017 - 5:18 pm UTC

the files are on sftp server which runs on a windows..

could be a bug between linux and windows of UTL_FILE?

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

Thank you, now I get it

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library