Oracle Apps Knowledge Sharing

Copy Files from one directory to another directory in PLSQL ( UTL_FILE.FCOPY, DBMS_LOB.FILEEXISTS example )

In this post, we have given a custom function which uses UTL_FILE.FCOPY to copy files from one directory to another directory. Further, custom function has below additional validations to check 

1. Source DBA directory is valid or invalid
2. Destination DBA directory is valid or invalid
   
3. source directory exists in server
   
4. destination directory exists in server
   
5. source file exists or not. 
6. Override of destination file is allowed based on user parameters
   

This function can be further expanded with other validations and utilized for file copy operations. Hope this is useful :)

Standard Utility Used: UTL_FILE.FCOPY and DBMS_LOB.FILEEXISTS

Custom Copy Function:

CREATE OR REPLACE FUNCTION FILECOPY (p_source_directory IN VARCHAR2,
                                     p_source_filename  IN VARCHAR2,
                                     p_dest_directory   IN VARCHAR2,
                                     p_dest_filename    IN VARCHAR2,
                                     p_override_allowed IN VARCHAR2 DEFAULT 'N'
                                    )
RETURN VARCHAR2
AS
  lb_file_exists  BOOLEAN;
  ln_dummy        NUMBER;
  ln_file_size    NUMBER;
  ln_block_size   NUMBER;
BEGIN
  --Check whether source directory exists
  BEGIN 
    ln_dummy:= 0;
	
    SELECT 1
      INTO ln_dummy
      FROM all_directories
     WHERE directory_name = p_source_directory;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      RETURN 'Source DBA Directory Does not exists';
  END;
  
  -- Check whether the directory present in server
  IF DBMS_LOB.FILEEXISTS(BFILENAME(p_source_directory,'.')) <> 1
  THEN 
    RETURN 'Source Directory Does not exists';
  END IF;
  
  --Check whether destination directory exists
  BEGIN 
    ln_dummy:= 0;
	
    SELECT 1
      INTO ln_dummy
      FROM all_directories
     WHERE directory_name = p_dest_directory;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      RETURN 'Destination DBA Directory Does not exists';
  END;
  
  -- Check whether the directory present in server
  IF DBMS_LOB.FILEEXISTS(BFILENAME(p_dest_directory,'.')) <> 1
  THEN 
    RETURN 'Destination Directory Does not exists';
  END IF;
  
  --Check whether source file exists
  IF DBMS_LOB.FILEEXISTS(BFILENAME(p_source_directory,p_source_filename)) <> 1
  THEN 
    RETURN 'Source File Does not exists';
  END IF;
  
  --Check whether source file exists
  IF DBMS_LOB.FILEEXISTS(BFILENAME(p_dest_directory,p_dest_filename)) = 1
     AND p_override_allowed = 'N'
  THEN 
    RETURN 'Destination File Override is not allowed. But, destination file exists';
  END IF;
  
  BEGIN 
    UTL_FILE.FCOPY (p_source_directory,
                    p_source_filename,
                    p_dest_directory,
                    p_dest_filename
                   );
				   
    --Check whether destination file created
    IF DBMS_LOB.FILEEXISTS(BFILENAME(p_dest_directory,p_dest_filename)) = 1
    THEN 
      RETURN 'File Copied Successfully';
    ELSE 
      RETURN 'File Copy Failed. Please contact technical team';	
    END IF;				   
  EXCEPTION 
    WHEN UTL_FILE.WRITE_ERROR THEN
	  RETURN 'Destination Write/File Access denied';
	WHEN UTL_FILE.READ_ERROR THEN 
	  RETURN 'Source Directory/File Access denied';
	WHEN UTL_FILE.INVALID_OPERATION THEN 
	  -- This is true because we already checked other possible errors like
	  -- source dir, destination dir , source file and dest file exists 
	  -- since above errs was not found, its assumed that access issue
	  RETURN 'Directory/File Access denied';
    WHEN OTHERS THEN 
	  RETURN SQLERRM;	  
  END;  
END FILECOPY;                                   
 Sample Call to copy Function: 
SET SERVEROUTPUT ON;
DECLARE 
  lv_file_copy_sts  VARCHAR2(2000);
BEGIN 
  lv_file_copy_sts := FILECOPY(p_source_directory => 'TEST1',
                               p_source_filename  => 'testfile.txt',
                               p_dest_directory   => 'TEST2',
                               p_dest_filename    => 'test.txt',
                               p_override_allowed => 'Y'
			      );
  DBMS_OUTPUT.PUT_LINE('File copy Status: '||lv_file_copy_sts);	
END;

 Sample runs to copy Function: