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