sql - Inserting Image Into BLOB Oracle 10g

Question

I am trying to insert an image into an BLOB field in a signatures which i will then select from the table and render on a report. I cannot seem to figure how to get the image into the table. I did an insert however when i render only the path to the image was shown on the report and not the image itself.

Table

CREATE TABLE esignatures (
  office   NUMBER(6,0)  NOT NULL,
  username VARCHAR2(10) NOT NULL,
  iblob    BLOB         NOT NULL
)

INSERT Statement (SQL)

INSERT INTO esignatures  
VALUES (100, 'BOB', utl_raw.cast_to_raw('C:picturesimage1.png'));

I know for sure i am inserting the String location in the form of a HEX value how can i get the image HEX value in the table so when i render i will see the image being displayed.

Answer 1

You cannot access a local directory from pl/sql. If you use bfile, you will setup a directory (create directory) on the server where Oracle is running where you will need to put your images.

If you want to insert a handful of images from your local machine, you'll need a client side app to do this. You can write your own, but I typically use Toad for this. In schema browser, click onto the table. Click the data tab, and hit + sign to add a row. Double click the BLOB column, and a wizard opens. The far left icon will load an image into the blob:

SQL Developer has a similar feature. See the "Load" link below:

If you need to pull images over the wire, you can do it using pl/sql, but its not straight forward. First, you'll need to setup ACL list access (for security reasons) to allow a user to pull over the wire. See this article for more on ACL setup.

Assuming ACL is complete, you'd pull the image like this:

declare
    l_url varchar2(4000) := 'http://www.oracleimg.com/us/assets/12_c_navbnr.jpg';
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_raw RAW(2000);
    l_blob BLOB;
begin
   -- Important: setup ACL access list first!

    DBMS_LOB.createtemporary(l_blob, FALSE);

    l_http_request  := UTL_HTTP.begin_request(l_url);
    l_http_response := UTL_HTTP.get_response(l_http_request);

  -- Copy the response into the BLOB.
  BEGIN
    LOOP
      UTL_HTTP.read_raw(l_http_response, l_raw, 2000);
      DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_http_response);
  END;

  insert into my_pics (pic_id, pic) values (102, l_blob);
  commit;

  DBMS_LOB.freetemporary(l_blob); 
end;

Hope that helps.