Forum Stats

  • 3,826,663 Users
  • 2,260,687 Discussions
  • 7,897,044 Comments

Discussions

How to extract blob from a table and save it as a file?

363269
363269 Member Posts: 21
edited May 13, 2011 1:34AM in SQL & PL/SQL
Dear All

i have table employee (emp_id number , emp_name varchar2(60) emp_image blob)

1 - i want to extract the emp_image of every employee into a operating system folder c:\images
2 - the file name should be emp_id.gif or emp_id.jpg

How can i do that?

Thanks a lot in advance for your co-operation

Regards
Mohamed Hammed
Tagged:

Comments

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,828 Red Diamond
    BLOBs (and CLOBs) can be written to the server's file system using UTL_FILE. Keep in mind to treat BLOBs as raw data and CLOBs as character data for file I/O.

    UTL_FILE is documented in the [url http://oracle.telkom.co.za:7777/oracle/oradoc102/appdev.102/b14258/u_file.htm#sthref14093]Oracle® Database PL/SQL Packages and Types Reference guide.
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,828 Red Diamond
    Oh yes.. you also need to use DBMS_LOB to read the BLOBs contents. This is documented in the same guide as UTL_FILE.

    Simplistically, you need to do the following (pseudo code):
    select blob into blob_variable from table where filter condition
    open file using UTL_FILE
    loop
      read a raw chunk from the blob_variable into a raw buffer using DBMS_LOB
      write the raw buffer to disk using UTL_FILE
    
      exit when there are no more data to read from blob_variable
    end loop
    
    close file
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    This link might help you as well:

    http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_10.shtml
  • 860025
    860025 Member Posts: 9
    I want to put an XML in a table's column using BLOBs.

    I tried the following ways:

    Created a table:

    CREATE TABLE lob_table (id NUMBER, doc BLOB);


    Created a directory:

    create or replace directory XML_DIR as '\app\granite\rnd';

    Created a PL/SQL procedure:

    CREATE OR REPLACE
    PROCEDURE BLOB_PROCEDURE AS
    src_lob BFILE := BFILENAME('XML_DIR', '1.xml');
    dest_lob BLOB;
    BEGIN
    DBMS_OUTPUT.PUT_LINE('1');
    INSERT INTO lob_table VALUES(1, EMPTY_BLOB())
    RETURNING doc INTO dest_lob;
    DBMS_OUTPUT.PUT_LINE('2');

    DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
    DBMS_OUTPUT.PUT_LINE('3');
    DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
    SRC_LOB => src_lob,
    AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
    DBMS_OUTPUT.PUT_LINE('Done');
    DBMS_LOB.CLOSE(src_lob);

    Exception
    when others then
    DBMS_OUTPUT.PUT_LINE('Exception');

    COMMIT;
    END BLOB_PROCEDURE;


    I am using Oracle SQL Developer.

    When I try to run the above procedure, it says:
    "Source does not have a runnable target."

    When I use Toad, the procedure executes properly, but there is no blob data in the table.

    Kindly help me in this regard.
  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    Please do not dig out a 4 years old post. There are lots of examples in this forum about inserting BLOB into database tables. See those, write yours and post in a new thread if you face any specific problem.
    For tool specific problems (like SQL Developer and TOAD), they have separated forum, post the problem there.
  • 860025
    860025 Member Posts: 9
    Thanks for you reply Saubhik.

    My earlier question involved in reading of all the related threads (in their respective forums) regarding insertion of an XML file into a BLOB using PL/SQL.
    I also mentioned the method in which I tried and also posted the output there.

    Kindly help me in achieving the task of successfully putting an XML file in a BLOB column of a table from a specified path.

    My task(after insertion) is to retrieve the XML from the table and parse it. This parsing, I am able to do it using Java. No issues.

    Thanks in advance. :)
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,828 Red Diamond
    edited May 9, 2011 5:00AM
    What do you NOT understand? That ..

    - resurrecting an old thread is unacceptable?

    - hijacking someone else's thread for your own question is unacceptable?

     
    Ask a question that you have as a new posting.

    If you want to refer to another thread or posting, add a URL to your posting,

    That simple. Asking questions meaningfully here (or ant other web forum) are governed by simple logical rules. Not rocket science.

    So please follow these simple rules...
  • 860025
    860025 Member Posts: 9
    Sorry Billy

    Thanks
This discussion has been closed.