Transferring(Copy, Clone) Files(Binary, Text etc.) In Oracle

Oracle provides a wide use file processing operations.Using Oracle's supplied packages dbms_file_transfer or utl_file it can be possible to copy files.  I simply demonstrate it:


C:\>sqlplus sys/sys@XE as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Per Mar 15 23:31:26 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> grant create any directory to hr;
Grant succeeded.
SQL> grant execute on dbms_file_transfer to hr;
Grant succeeded.
SQL> connect hr/hr@XE
Connected.
SQL> create directory ftp_in as 'c:\ftp_in';
Directory created.
SQL> create directory backup_ftp as 'c:\backup_ftp';
Directory created.
SQL> grant execute on utl_file to hr;
grant execute on utl_file to hr
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect sys/sys as sysdba
Connected.
SQL> grant execute on utl_file to hr;
Grant succeeded.
SQL> connect hr/hr@XE
Connected.
SQL> exec dbms_file_transfer.copy_file('ftp_in','15032007.dat','backup_ftp','150
32007.backup.dat' );
BEGIN dbms_file_transfer.copy_file('ftp_in','15032007.dat','backup_ftp','1503200
7.backup.dat' ); END;
*
ERROR at line 1:
ORA-19505: failed to identify file "c:\ftp_in\15032007.dat"
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch
O/S-Error: (OS 70) Uzaktaki sunucu durdurulmu? veya halen ba?latma i?lemi
s?r?yor.
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 1

SQL>
SQL>
SQL> exec utl_file.fcopy('FTP_IN','15032007.dat','BACKUP_FTP','15032007.backup.d
at' );
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> show parameter control_files
ORA-00942: table or view does not exist

SQL> connect sys/sys as sysdba
Connected.
SQL> grant select on v_$parameter to hr
  2  ;
Grant succeeded.
SQL> connect hr/hr@XE
Connected.
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\ORACLEXE\ORADATA\XE\CONTROL
                                                 .DBF
SQL> create or replace directory data_files as 'C:\ORACLEXE\ORADATA\XE';
Directory created.
SQL>
SQL> BEGIN
  2    dbms_file_transfer.copy_file('data_files',
  3                                 'CONTROL.DBF',
  4                                 'BACKUP_FTP',
  5                                 'CONTROL.BAKUP.DBF');
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-19504: failed to create file "c:\backup_ftp\CONTROL.BAKUP.DBF"
ORA-27038: created file already exists
OSD-04010: option specified, file already exists
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2

SQL> host del c:\backup_ftp\CONTROL.BAKUP.DBF;
SQL> BEGIN
  2    dbms_file_transfer.copy_file('data_files',
  3                                 'CONTROL.DBF',
  4                                 'BACKUP_FTP',
  5                                 'CONTROL.BAKUP.DBF');
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>