COPYING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK

 
REQUEST

 
RELATED

 
TESTCASE

In this test case I’m going to copy a file from dir1 to dir2 by UTL_FILE package. First, Let’s create two directories and a file for copying

oracle@dbpilot.net: mkdir /tmp/dir1
oracle@dbpilot.net: mkdir /tmp/dir2
oracle@dbpilot.net: echo "A test of UTL_FILE.FCOPY procedure" > /tmp/dir1/myfile.txt

oracle@dbpilot.net: ls -lh /tmp/dir1/ --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 35  myfile.txt

oracle@dbpilot.net: ls -lh /tmp/dir2/ --time-style='+'
total 0

SQL> create or replace directory DIR1 as '/tmp/dir1';

Directory created.

SQL> create or replace directory DIR2 as '/tmp/dir2';

Directory created.

So, there is a need to copy myfile.txt file from /tmp/dir1 to /tmp/dir2. The simplest code is

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

oracle@dbpilot.net: ls -lh /tmp/dir2/ --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 35  myfile.cloned.txt

oracle@dbpilot.net: cat /tmp/dir2/myfile.cloned.txt
A test of UTL_FILE.FCOPY procedure

If a file does not exist or you don’t have sufficient privileges you will get a following error

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

BEGIN
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 270
ORA-06512: at "SYS.UTL_FILE", line 1243
ORA-06512: at line 2

A little bit more complicated code

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 : 35
File system block size in bytes : 4096

For more details refer to the official documentation.