Copying Files Using the Oracle Database Server

We all know that with we can copy file from one location to another location using scp/cp( in unix/linux) or using copy/explorer/ctl+c(in windows). Within Oracle this can also be done to copy file from one location to another.

I will try to demonstrate it with example.

With Oracle we can achive our desired copy file task by two ways.

1)Using DBMS_FILE_TRANSFER Package.
2)Using Oracle Streams Propagation.

The 2nd Step inshallah will be shown in another of my threads about streams Replication.I here will discuss about DBMS_FILE_TRANSFER Package.

1)Using DBMS_FILE_TRANSFER Package.
---------------------------------------
Copying a File on a Local File System
-----------------------------------------
Using the COPY_FILE procedure in the DBMS_FILE_TRANSFER package we can copy a file on a local file system. With an example I will show you how you can copy a file named test.txt from directory to /export/home/Arju2

Step1:
---------
In SQL*Plus connect as a user who can grant privilege and create directory objects using SQL.

Step2:
-----------
Create a directory named SOURCE_DIR in which source file resides. Here it is /export/home/Arju

SQL> CREATE DIRECTORY SOURCE_DIR as '/export/home/oracle/Arju';

Step3:
---------
Also create a destination directory named DEST_DIR which copied file go. Here it is /export/home/Arju2.

SQL>CREATE DIRECTORY DEST_DIR as '/export/home/oracle/Arju2';

Step4:
---------
Grant Permission to the user who will do the operation.

GRANT EXECUTE ON DBMS_FILE_TRANSFER TO arju;
GRANT READ ON DIRECTORY source_dir TO arju;
GRANT WRITE ON DIRECTORY dest_dir TO arju;

Step5:
-----------
Connect as the user and execute procedure.

Example:
conn arju/a

SQL> !ls /export/home/oracle/Arju
test.txt

SQL> !mkdir /export/home/oracle/Arju2

SQL> !ls /export/home/oracle/Arju2

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'SOURCE_DIR',
source_file_name => 'test.txt',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'test.txt');
END;
/

PL/SQL procedure successfully completed.

SQL> !ls /export/home/oracle/Arju2
test.txt