35
DBMS_FILE_TRANSFER

The DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases.

See Also: Oracle Database Concepts for conceptual information about file transfer Oracle Database Administrator's Guide for instructions about using file transfer Oracle Streams Concepts and Administration for applications of file transfer.

This chapter contains the following topic:

• Summary of DBMS_FILE_TRANSFER Subprograms

---

Summary of DBMS_FILE_TRANSFER Subprograms

Table 35-1 DBMS_FILE_TRANSFER Package Subprograms

Subprogram	Description
COPY_FILE Procedure	Reads a local file and creates a copy of it in the local file system
GET_FILE Procedure	Contacts a remote database to read a remote file and then creates a copy of the file in the local file system
PUT_FILE Procedure	Reads a local file and contacts a remote database to create a copy of the file in the remote file system

---

COPY_FILE Procedure

This procedure reads a local file and creates a copy of it in the local file system. The file that is copied is the source file, and new file that results from the copy is the destination file. The destination file is not closed until the procedure completes successfully.

Syntax

DBMS_FILE_TRANSFER.COPY_FILE(
   source_directory_object       IN  VARCHAR2,
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2);

Parameters

Table 35-2 COPY_FILE Procedure Parameters

Parameter	Description
source_directory_object	The directory object from which the file is copied in the local file system. This directory object must exist.
source_file_name	The name of the file that is copied in the local file system. This file must exist in the local file system in the directory associated with the source directory object.
destination_directory_object	The directory object into which the file is placed in the local file system. This directory object must exist in the local file system.
destination_file_name	The name of the file copied to the destination directory object in the local file system. A file with the same name must not exist in the destination directory in the local file system.

Usage Notes

To run this procedure successfully, the current user must have the following privileges:

• Read privilege on the directory object specified in the source_directory_object parameter
• Write privilege on directory object specified in the destination_directory_object parameter

This procedure converts directory object parameters to uppercase unless they are surrounded by double quotation marks, but this procedure does not convert file names to uppercase.

Also, the copied file must meet the following requirements:

• The size of the copied file must be a multiple of 512 bytes.
• The size of the copied file must be less than or equal to two terabytes.

Transferring the file is not transactional. The copied file is treated as a binary file, and no character set conversion is performed. To monitor the progress of a long file copy, query the V$SESSION_LONGOPS dynamic performance view.

---

GET_FILE Procedure

This procedure contacts a remote database to read a remote file and then creates a copy of the file in the local file system. The file that is copied is the source file, and the new file that results from the copy is the destination file. The destination file is not closed until the procedure completes successfully.

Syntax

DBMS_FILE_TRANSFER.GET_FILE
   source_directory_object      IN  VARCHAR2,    
   source_file_name             IN  VARCHAR2,  
   source_database              IN  VARCHAR2,  
   destination_directory_object IN  VARCHAR2,
   destination_file_name        IN  VARCHAR2);

Parameters

Table 35-3 GET_FILE Procedure Parameters

Parameter	Description
source_directory_object	The directory object from which the file is copied at the source site. This directory object must exist at the source site.
source_file_name	The name of the file that is copied in the remote file system. This file must exist in the remote file system in the directory associated with the source directory object.
source_database	The name of a database link to the remote database where the file is located.
destination_directory_object	The directory object into which the file is placed at the destination site. This directory object must exist in the local file system.
destination_file_name	The name of the file copied to the local file system. A file with the same name must not exist in the destination directory in the local file system.

Usage Notes

To run this procedure successfully, the following users must have the following privileges:

• The connected user at the source database must have read privilege on the directory object specified in the source_directory_object parameter.
• The current user at the local database must have write privilege on the directory object specified in the destination_directory_object parameter.

This procedure converts directory object parameters to uppercase unless they are surrounded by double quotation marks, but this procedure does not convert file names to uppercase.

Also, the copied file must meet the following requirements:

• The size of the copied file must be a multiple of 512 bytes.
• The size of the copied file must be less than or equal to two terabytes.

Transferring the file is not transactional. The copied file is treated as a binary file, and no character set conversion is performed. To monitor the progress of a long file transfer, query the V$SESSION_LONGOPS dynamic performance view.

---

PUT_FILE Procedure

This procedure reads a local file and contacts a remote database to create a copy of the file in the remote file system. The file that is copied is the source file, and the new file that results from the copy is the destination file. The destination file is not closed until the procedure completes successfully.

Syntax

DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object       IN  VARCHAR2,   
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2,  
   destination_database          IN  VARCHAR2);

Parameters

Table 35-4 PUT_FILE Procedure Parameters

Parameter	Description
source_directory_object	The directory object from which the file is copied at the local source site. This directory object must exist at the source site.
source_file_name	The name of the file that is copied from the local file system. This file must exist in the local file system in the directory associated with the source directory object.
destination_directory_object	The directory object into which the file is placed at the destination site. This directory object must exist in the remote file system.
destination_file_name	The name of the file placed in the remote file system. A file with the same name must not exist in the destination directory in the remote file system.
destination_database	The name of a database link to the remote database to which the file is copied.

Usage Notes

To run this procedure successfully, the following users must have the following privileges:

• The current user at the local database must have read privilege on the directory object specified in the source_directory_object parameter.
• The connected user at the destination database must have write privilege to the directory object specified in the destination_directory_object parameter.

This procedure converts directory object parameters to uppercase unless they are surrounded by double quotation marks, but this procedure does not convert file names to uppercase.

Also, the copied file must meet the following requirements:

• The size of the copied file must be a multiple of 512 bytes.
• The size of the copied file must be less than or equal to two terabytes.

Transferring the file is not transactional. The copied file is treated as a binary file, and no character set conversion is performed. To monitor the progress of a long file transfer, query the V$SESSION_LONGOPS dynamic performance view.