Copying an operating system file from one OS directory to another using the UTL_FILE package.

HOW TO WRITE TO A FILE FROM PL/SQL BLOCK
HOW TO READ A FILE FROM PL/SQL BLOCK
HOW TO REMOVE AN OPERATING SYSTEM FILE FROM PL/SQL BLOCK

The FCOPY procedure of the UTL_FILE package copies a contiguous portion of a file to a newly created file.

UTL_FILE.FCOPY (
   src_location    IN VARCHAR2,
   src_filename    IN VARCHAR2,
   dest_location   IN VARCHAR2,
   dest_filename   IN VARCHAR2,
   start_line      IN BINARY_INTEGER DEFAULT 1,
   end_line        IN BINARY_INTEGER DEFAULT NULL);


src_location  - Directory location of the source file, a DIRECTORY_NAME from the ALL_DIRECTORIES view
src_filename  - Source file to be copied
dest_location - Destination directory where the destination file is created
dest_filename - Destination file created from the source file
start_line    - Line number at which to begin copying. The default is 1 for the first line
end_line      - Line number at which to stop copying. The default is NULL, signifying end of file

Create two dummy OS directories and one file

## Create dummy directories

mkdir /tmp/dir1
mkdir /tmp/dir2

## Create a dummy file in the first dummy directory 

echo -e "\nA dummy file with dummy text.\n" > /tmp/dir1/myfile.txt

## Display content of dummy file 

cat /tmp/dir1/myfile.txt

A dummy file with dummy text.

Create two database DIRECTORY objects pointing to dummy OS directories

SQL> CREATE OR REPLACE DIRECTORY DIR1 AS '/tmp/dir1';

Directory created.

SQL>  CREATE OR REPLACE DIRECTORY DIR2 AS '/tmp/dir2';

Directory created.

Copy the dummy file from the first dummy directory to second dummy directory by FCOPY procedure

SET SERVEROUTPUT ON

BEGIN
   UTL_FILE.FCOPY('DIR1', 'myfile.txt', 'DIR2', 'cloned.myfile.txt');
END;
/

PL/SQL procedure successfully completed.

Confirm that new file was created

SQL> !cat /tmp/dir2/cloned.myfile.txt

A dummy file with dummy text.

That's it. The /tmp/dir1/myfile.txt file was successfully copied to /tmp/dir2/cloned.myfile.txt

Here is more advanced PL/SQL block providing more details about copied file

SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
   v_file_name     VARCHAR2(100);
   v_file_new_name VARCHAR2(100);
   v_file_dir1     VARCHAR2(100);
   v_file_dir2     VARCHAR2(100);
   v_existence     BOOLEAN;
   v_file_length   NUMBER;
   v_block_size    NUMBER;
BEGIN
   v_file_name     := 'myfile.txt';
   v_file_new_name := 'myfile.cloned.txt';
   v_file_dir1     := 'DIR1';
   v_file_dir2     := 'DIR2'; 
	
   -- Getting file size 
   UTL_FILE.FGETATTR(v_file_dir1 , v_file_name , v_existence , v_file_length, v_block_size);
	
   -- Verifying that file exists
   IF v_existence THEN
      DBMS_OUTPUT.PUT_LINE(CHR(10));	
      DBMS_OUTPUT.PUT_LINE(v_file_name || ' => ' || v_file_new_name );	
      DBMS_OUTPUT.PUT_LINE('Length of the file in bytes : ' || v_file_length);
      DBMS_OUTPUT.PUT_LINE('File system block size in bytes : ' || v_block_size);
		
      -- Copying file 
      UTL_FILE.FCOPY(v_file_dir1, v_file_name, v_file_dir2, v_file_new_name);
   ELSE 
      DBMS_OUTPUT.PUT_LINE('(*) Read_file_error : File not found.');
   END IF;
END;
/

myfile.txt => myfile.cloned.txt
Length of the file in bytes : 32
File system block size in bytes : 4096

PL/SQL procedure successfully completed.

Written At

04 JAN 202220:30

OEL Release

7.9 x64

Database Release

12.1.0.1.0 - 19.13.0.0.0