1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Copy files with Oracle

Discussion in 'Other Development Tools' started by Arju, Oct 17, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    We all know that with OS we can use copy(windows) or cp/scp(unix) or any other graphical tool to copy file between or within machines. But as oracle comes version to version it try to avoid dependency from OS.

    The DBMS_FILE_TRANSFER package help this a lot to do the task. Within this package the COPY_FILE procedure to the desired task.

    Suppose I want to copy file arju.txt from /export/arju.txt to /oracle/arju.txt
    The followings are the detail of this.

    1)Create directory for both- where source file resides and where the file will copy- destination location.
    Code (Text):
    CREATE DIRECTORY SOURCE_DIR as '/export';
    CREATE DIRECTORY DEST_DIR as '/oracle';
     
    2)Grant permission to the user who will do the operation.
    Suppose user ARJU will do the copy task. Then as a dba user do,
    Code (Text):
    GRANT EXECUTE ON DBMS_FILE_TRANSFER TO arju;
    GRANT READ ON DIRECTORY source_dir TO arju;
    GRANT WRITE ON DIRECTORY dest_dir TO arju;
    3)Connect as arju and run DBMS_FILE_TRANSFER.COPY_FILE like below.

    Code (Text):
    connect arju/test

    BEGIN
    DBMS_FILE_TRANSFER.COPY_FILE(
    source_directory_object => 'SOURCE_DIR',
    source_file_name => 'arju.txt',
    destination_directory_object => 'DEST_DIR',
    destination_file_name => 'arju.txt');
    END;
    /
    4)Be sure the file is copied.
    Code (Text):
    $ls /oracle