5 Replies Latest reply: Feb 9, 2013 10:25 AM by rp0428 RSS

    Reg : Package DBMS_FILE_TRANSFER -

    ranit B
      Hi Experts,

      I've have a simple doubt regarding the SYS package DBMS_FILE_TRANSFER.
      <b>Doc</b> says -
      >
      The DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases.
      >
      What exactly does a Binary file mean? Can't I transfer a +.txt+ file or +.csv+ file from 1 server box to another?

      Also, from <b>Oracle-Base.com</b>
      >
      - File transfers are not transactional.
      - Files are copied as binary, so no character conversions are performed.
      >
      What does these mean?

      Any pointer guys?

      Thanks,
      Ranit B.

      My database -
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE    11.2.0.2.0      Production
      TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
        • 1. Re: Reg : Package DBMS_FILE_TRANSFER -
          stefan nebesnak
          ranit B wrote:
          Hi Experts,

          I've have a simple doubt regarding the SYS package DBMS_FILE_TRANSFER.
          What exactly does a Binary file mean? Can't I transfer a +.txt+ file or +.csv+ file from 1 server box to another?
          Yes, you can.
          It's a 'binary' file on the filesystem. (Like your mp3, jpg, txt.. file.)
          The maximum transferred file size is 2 terabytes.

          Binary data consists of sequences of 0's and 1's:
          010001 00001 00010 = *♫♪*, *(ಠںಠ)* , abcd..

          (They can be stored as 'BLOB' (mp3, jpg..) or 'CLOB'/'NCLOB' (txt, csv..) datatype in database tables, but these types can only hold up to 4 GB of data.
          You can also use the 'BFILE' datatype as a pointer or locator for the actual external data files.)
          - File transfers are not transactional.
          What does these mean?
          If any error occurred during the file transfer, then (only) the part of a 'binary' file has been transferred into the destination directory, and remains there.

          (A transaction usually means a sequence of information exchange that is treated as a unit, it has to be completed in its entirety.)

          (During transactional file transfer, if any error occurred, then transferred part
          of a 'binary' file is 'rolled back'.)
          - Files are copied as binary, so no character conversions are performed.
          What does these mean?
          They are copied as binary, no (binary) data change is performed.
          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);
          CREATE DIRECTORY source_dir AS
          '/u01/app/oracle';
          CREATE DIRECTORY dest_dir AS
          '/u01/app/oracle/example';
          BEGIN
          DBMS_FILE_TRANSFER.COPY_FILE(
          SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
          SOURCE_FILE_NAME => 'exm_old.txt',
          DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
          DESTINATION_FILE_NAME => 'exm_new.txt');
          END;
          
          --http://www.remote-dba.net/oracle_10g_new_features/database_server.htm
          Edited by: stefan nebesnak on Feb 9, 2013 6:14 AM
          • 2. Re: Reg : Package DBMS_FILE_TRANSFER -
            Hoek
            Can't I transfer a +.txt+ file or +.csv+ file from 1 server box to another?
            Nope, since "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."
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:53497738334919

            ps. I'm back again only for a couple of hours today, you'll receive some more updates (hopefully) this evening or else tomorrow ;)
            • 3. Re: Reg : Package DBMS_FILE_TRANSFER -
              ranit B
              Heyyyy Hoek, welcome back... thanks for the inputs :)

              Like you always suggest, I'll try to workout these (best way to conclude things) but got no production DB access now. Just my personal lappy and an XE installed.
              • 4. Re: Reg : Package DBMS_FILE_TRANSFER -
                ranit B
                Thanks Stefan.
                I'll do some workout with these and 'll get back here.
                • 5. Re: Reg : Package DBMS_FILE_TRANSFER -
                  rp0428
                  >
                   File transfers are not transactional.
                  What does these mean?
                  {quote}
                  If any error occurred during the file transfer, then (only) the part of a 'binary' file has been transferred into the destination directory, and remains there.
                  {quote}
                  Where do you get that information? That is NOT what 'File transfers are not transactional' means at all.
                  
                  In an Oracle context the term 'non-transactional' (and similarly worded terms) means that the activity takes places in its own transaction and not as part of the current transaction.
                  
                  DDL is a non-transactional activity. That doesn't mean that if Oracle starts executing DDL and it fails that you are left with a partial object.
                  
                  An autonomous transaction is a 'non-transactional' activity; it executes in its own transaction.
                  
                  From the Oracle docs for queues:
                  {quote}
                  A nontransactional queue is one in which each user-enqueued event is its own transaction. That is, an apply process performs a COMMIT after each user-enqueued event it applies. In either case, the user-enqueued events may or may not contain user-created LCRs
                  {quote}
                  File transfers performed using the DBMS_FILE_TRANSFER are not transactional. 
                  
                  That means, just as it does for queues, that the operation is its own transaction and a COMMIT is performed for that transaction upon SUCCESSFUL completion. It operates just as if you had put the operation into an autonomous transaction procedure.
                  
                  If a user begins a transaction (e.g. a procedure) and performs DML statements and then uses the transfer package to transfer a file and then executes a ROLLBACK then the two DML statements will be 'undone' but the file will STILL have been transferred because that file transfer was 'not transactional'.