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  pictures image1 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

User · Accepted Answer

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:

enter image description here

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

enter image description here

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.

User · Answer

You should do something like this   1  create directory object what would point to server-side accessible folder  CREATE DIRECTORY image files AS   data images      2   Place your file into OS folder directory object points to  3  Give required access privileges to Oracle schema what will load data from file into table   GRANT READ ON DIRECTORY image files TO scott     4  Use BFILENAME  EMPTY BLOB functions and DBMS LOB package  example NOT tested - be care  like in below   DECLARE   l blob BLOB     v src loc  BFILE    BFILENAME  IMAGE FILES    myimage png      v amount   INTEGER  BEGIN   INSERT INTO esignatures     VALUES  100   BOB   empty blob    RETURN iblob INTO l blob     DBMS LOB OPEN v src loc  DBMS LOB LOB READONLY     v amount    DBMS LOB GETLENGTH v src loc     DBMS LOB LOADFROMFILE l blob  v src loc  v amount     DBMS LOB CLOSE v src loc     COMMIT  END      After this you get the content of your file in BLOB column and can get it back using Java for example   edit  One letter left missing  it should be LOADFROMFILE

[sql] Inserting Image Into BLOB Oracle 10g

Examples related to sql

Examples related to oracle

Examples related to oracle10g

Examples related to blob