UTL_FILE.FRENAME Fails With ORA-29292 When old and new Files are on Different Filesystems (Doc ID 267469.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version 9.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 06-Jul-2017***

Symptoms

UTL_FILE.FRENAME Fails With

ORA-29292: file rename operation failed

 

 

when source directory is local directory and the destination directory is on different file systems.

Internally the error is

'Cross-device link' error has occurred renaming MyFile

To Reproduce:

SQL> create or replace directory TESTDIR1 as 'c:\temp\';

Directory created.

SQL> grant read on directory TESTDIR1 to public;

Grant succeeded.

-- P: is a network mounted Share

SQL> create or replace directory TESTDIR2 as 'P:\temp\';

Directory created.

SQL> grant read on directory TESTDIR1 to public;

Grant succeeded.

SQL> create or replace PROCEDURE test_utl is
  2  file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
  3
  4
  5  BEGIN
  6  -- Open file to write into and obtain its file_handle.
  7  file_handle :=
  8  UTL_FILE.FOPEN('TESTDIR1','MyFile','W');
  9
 10
 11  UTL_FILE.PUT_LINE(file_handle, 'this is line 1 as a test');
 12
 13  --Close the file.
 14  UTL_FILE.FCLOSE(file_handle);
 15  UTL_FILE.FRENAME('TESTDIR1','MyFile','TESTDIR2','MyFile1',TRUE);
 16  END;
 17  /

Procedure created.

SQL> exec test_utl
BEGIN test_utl; END;

*
ERROR at line 1:
ORA-29292: file rename operation failed
ORA-06512: at "SYS.UTL_FILE", line 340
ORA-06512: at "SYS.UTL_FILE", line 1186
ORA-06512: at "SCOTT.TEST_UTL", line 15
ORA-06512: at line 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms