2 Replies Latest reply on Oct 8, 2013 11:05 AM by ed09548c-05cf-4fd5-9e39-a96ae2020a02

    How to extract BLOB content from database column and put that content into file using Oracle data integrator ?


      The scenario :


      There is BLOB content (ZIP & .dat) in database column as files. The ZIP file contains binary data and .DAT file contains ascii.

      I need to use ODI so as to copy the content from each column(file) as it is and then make a new file as an output in which we

      can see the actual contents of BLOB file. Also , we need to create different file for each row of table i.e new file for each ZIP/.DAT



      The solutions I tried :

      1. PL/SQL's  UTL_FILE package , But we do not have access to oracle file server. 

      2. Normal ODI processing using LKM SQL to File  APPEND copies encoded data as Oracle.sql.BLOB@fgsj4h4 something like this.

      3. UTL_RAW package converts maximum 2000 bytes. As size of .DAT file exceeds 2000 bytes and can be upto 10000 bytes.

         i used utl_raw.cast_to_varchar2(dbms_lob.substr(file_content,2000,1)).

         Using this package converts data upto 2000 bytes and show them in file.



      Experts please advice. Thanks in advance..!!!