How to Move File from One Directory to Another Using PL SQL • Vinish.Dev

Sometimes, you may need to move a file from one directory to another within your Oracle database environment. Oracle provides a convenient way to accomplish this task using PL/SQL, but before you begin, you must ensure that directory objects exist for both the source and target directories. If these directory objects do not already exist, you will need to create them.

On This Page
Show More

Creating Directory Objects

First, you need to create a directory object for the source location. You can do this using the following command:

CREATE OR REPLACE DIRECTORY source_file_dir AS 'D:\text_files\';

Next, create a directory object for the target location:

CREATE OR REPLACE DIRECTORY target_file_dir AS 'D:\text_files_2\';

Note:
Make sure to adjust the directory paths according to your specific environment and operating system. For Linux or Unix systems, use the appropriate path format (e.g., /home/oracle/text_files/).

Moving the File Using UTL_FILE.FRENAME

After the directory objects are created, you can use the UTL_FILE.FRENAME procedure to move the file. This procedure allows you to transfer a file from the source directory to the target directory and optionally rename it during the move.

Here’s an example of how to use UTL_FILE.FRENAME in a PL/SQL block:

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

This example moves the file named 1_text_file.csv from the source directory to the target directory and renames it to new_1_text_file.csv. The last parameter, set to TRUE, allows the file in the destination to be overwritten if it already exists.

How UTL_FILE.FRENAME Works

The UTL_FILE.FRENAME procedure functions similarly to the Unix mv command. When executed, it copies the file from the source directory to the target directory and then removes the original file from the source location.

UTL_FILE.FRENAME Syntax

Below is the complete syntax for the UTL_FILE.FRENAME procedure:

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

src_location: Name of the source directory object.
src_filename: Name of the file to be moved.
dest_location: Name of the target directory object.
dest_filename: New name for the file in the target directory.
overwrite: Boolean flag indicating whether to overwrite an existing file in the target directory (default is FALSE).

By following these steps, you can easily move files between directories in Oracle using PL/SQL. This approach is efficient and leverages Oracle’s built-in file management capabilities.