Kamran Agayev's Oracle Blog

Oracle Certified Master

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]$

2 Responses to “Copy OS files from Oracle”

  1. H.Tonguç Yılmaz Says:

    dbms_file_transfer.copy_file can be another option after 10g.

  2. kamranagayev Says:

    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

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>