How to Move File from One Directory to Another Using PL SQL

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

I am giving an example below to move a file from one directory to another using PL SQL in Oracle. To move a file from one location to another in Oracle, the database directory object for source directory and target directory must exist. If not then you must create to do this task.

Create the directory object for source location as shown below:

Create or Replace Directory source_file_dir as 'D:\text_files\';

Create the directory object for target location as shown below:

Create or Replace Directory target_file_dir as 'D:\text_files_2\';

Change path accordingly to yours and for Linux / Unix environments as well. Now run the procedure UTL_FILE.FRENAME to move the file as shown below:

BEGIN
 UTL_FILE.FRENAME ('SOURCE_FILE_DIR',
 '1_text_file.csv',
 'TARGET_FILE_DIR',
 'new_1_text_file.csv',
 TRUE);
END;

The functionality of UTL_FILE.FRENAME procedure is similar to Unix mv command. It will remove the file from source directory after copying to the target directory. The complete usage syntax is below (Learn More...).

UTL_FILE.FRENAME (
 src_location IN VARCHAR2,
 src_filename IN VARCHAR2, 
 dest_location IN VARCHAR2,
 dest_filename IN VARCHAR2,
 overwrite IN BOOLEAN DEFAULT FALSE);

 

This Post Has 2 Comments

  1. Subhash KR September 23, 2020

    I have generated couple of reports and i would like to FTP it from source server to client server using UTL_FILE package. How can i achieve this.

    1. Vinish Kapoor September 23, 2020

      You can use UTL_FTP package instead. Below is a basic example:

      Create a database directory object for the source files:

      CREATE OR REPLACE DIRECTORY my_files AS '/usr01/appdata/files/';

      PL/SQL program:

      DECLARE
  l_connection  UTL_TCP.connection;
BEGIN
  l_connection := ftp.login('ftp.server.com', '21', 'youruser', 'yourpsw');
  ftp.ascii(p_conn => l_connection);
  ftp.put(p_conn      => l_connection,
          p_from_dir  => 'MY_FILES',
          p_from_file => 'source_file.txt',
          p_to_file   => '/usr01/appdata/files/target_file.txt');
  ftp.logout(l_connection);
END;
/

Comments are closed.