Copy OS files from Oracle
Posted by Kamran Agayev A. on December 9th, 2008
Sometimes you need to copy some OS file from one location to another. If you’re using only Sql*Plus and you can’t connect directly to OS, you can user Oracle’s UTL_FILE.FCOPY function to achieve this goal.
Firstly, we’ll create two directories and one file. Then we’ll create a user and copy this file from one folder to another using UTL_FILE package
[oracle@localhost /]$ cd /tmp
[oracle@localhost tmp]$ mkdir copy_dir
[oracle@localhost tmp]$ mkdir backup_dir
[oracle@localhost tmp]$ ls backup_dir/
[oracle@localhost tmp]$
[oracle@localhost tmp]$ cd copy_dir
[oracle@localhost copy_dir]$ vi test.dat (We are adding a line with word MY_TEST to file test.dat)
[oracle@localhost copy_dir]$ ls -lh
total 4.0K
-rw-r–r– 1 oracle oinstall 8 Dec 7 04:11 test.dat
[oracle@localhost copy_dir]$ cat test.dat
MY_TEST
[oracle@localhost copy_dir]$sqlplus “/ as sysdba”
SQL> CREATE USER kamran IDENTIFIED BY kamran;
User created.
SQL> grant connect, resource to kamran;
Grant succeeded.
SQL> grant create any directory to kamran;
Grant succeeded.
SQL> grant execute on utl_file to kamran;
Grant succeeded.
SQL> conn kamran/kamran
Connected.
SQL> create directory file_source as ‘/tmp/copy_dir’;
Directory created.
SQL> create directory backup_source as ‘/tmp/backup_dir’;
Directory created.
SQL> exec utl_file.fcopy(‘FILE_SOURCE’,’test.dat’,’BACKUP_SOURCE’,’copied_test.dat’);
PL/SQL procedure successfully completed.
SQL>exit
[oracle@localhost tmp]$ cd backup_dir/
[oracle@localhost backup_dir]$ ls -lh
total 4.0K
-rw-r–r– 1 oracle oinstall 8 Dec 7 04:32 copied_test.dat
[oracle@localhost backup_dir]$
December 9th, 2008 at 11:59 am
dbms_file_transfer.copy_file can be another option after 10g.
December 9th, 2008 at 12:09 pm
You’re right Tonguc, I used it. On one of examples, I suceeded, but on another I got error :
ORA-19505: failed to identify file “c:\temp\source\sqlnet.log”
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 3223)
I searched around and found Tom’s answer:
this utility is NOT a general purpose “copy my file please” routine. It is specifically
for database (binary) files and backup – it is not to copy just any file.
AND the main part of answer was:
The size of the copied file must be a multiple of 512 bytes.
That’s why, in some situations, you’re getting error using ths package
You can find Tom’s answer here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53497738334919