Home » PLSQL » How to Move File from One Directory to Another Using PL SQL

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

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);

### 2 thoughts on “How to Move File from One Directory to Another Using PL SQL”

1. 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. 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;
/