Inserting Image into Blob Oracle 10G

Inserting Image Into BLOB Oracle 10g

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.

How to insert image as BLOB into Oracle Database

After dozens of hours trying to figure out how to accomplish BLOB insertion into OracleDB using goracle Go driver, I got answer.

TL;DR

Full code (obviously adapt variables like DB connection and file location && file name to your needs):

package main

import (
    "bytes"
    "context"
    "database/sql"
    "fmt"
    "io/ioutil"
    "time"
goracle "gopkg.in/goracle.v2"
)

func main() {
    fmt.Println("... Setting up Database Connection")
    testDb, err := sql.Open("goracle",
        "sys" +
            "/" +
            "Oracle18" +
            "@" +
            "localhost:32118" +
            "/" +
            "XE as sysdba")
    if err != nil {
        fmt.Println("... DB Setup Failed")
        fmt.Println(err)
        return
    }
    defer testDb.Close()

// Get file as bytes
dat, err := ioutil.ReadFile("./sample.png")
if err != nil {
    fmt.Println(".....Error Opening File")
    fmt.Println(err)
    return
}

// BLOB creation && passing file into it
directLob := goracle.Lob{ Reader: bytes.NewReader(dat[:])}

fmt.Println("\nINSERTING BLOB - starting....")
// Working BLOB insertion - INSERT COMMAND
_, err = testDb.Exec("insert into AA_LIVE_FRAMES(id, frame) VALUES(:1, :2)",  1, directLob)
if err != nil {
    fmt.Println(".....Error Inserting BLOB")
    fmt.Println(err)
    return
}
fmt.Println("\nINSERTING BLOB - ended.")

fmt.Println("\nSuccess")
}

This is working main.go file.

LONGER VERSION ANSWER

Why this took me so long if code is so simple?

When I've started to work with that problem, I didn't find any tutorial or examples in the goracle documentation showing how to INSERT BLOB into OracleDB. Sure, there was this goracle.Lob structure with nice function - Read(). It was my first try, declare this goracle.Lob and use it's Read() function, but it gave me errors - nil address and so on. I didn't know why, tried to debug it - without results. Then I went deeper, I pull out some test codes from goracle package showing how to create temporary LOB - some of this code is shown in my question answer. Again, without success. Then, I was trying raw SQL, failed again (I think because my OracleDB is docker - creating directory, which is needed to insert BLOB into OracleDB didn't work; nvm).

And finally, I am not sure why, I have tried struct literal:

directLob := goracle.Lob{ Reader: bytes.NewReader(dat[:])}

and worked.

Alright, why I am typing all of this? Because, AFAIK, doing this:

directLob := goracle.Lob{ Reader: bytes.NewReader(dat[:])}

and this:

directLob := goracle.Lob{}
directLob.Read(dat[:])

should be the same? Or am I completely wrong?

oracle sql error: Inserting a picture in blob format

You don't pass a path to a file as a BLOB, you pass the actual bytes of the file - see Using PL/SQL how do you I get a file's contents in to a blob?

Though, I (personally) dare say your problematic code has the right idea; I'd recommend to NOT store your pictures inside your database. Store them in the file system as files and then use the paths in the DB. It's handy for all sorts of things such as being able to individually back them up, manipulate or substitute them, and you don't need to write code in order to serve them over the web; web servers already know how to serve files out of the file system but as soon as you put your picture (or any bytes data) into a DB, it becomes much harder to work with and you have to write all the code that pulls it out, and puts it back - and that's really all you can do. By storing files in your mission critical DB it means the DB now has to dedicate resources to fetching those files out any time they're needed - really files like pictures should be put on a CDN and made available close to the users who will use them/the job of storing, caching and serving them be handed off to existing technologies dedicated to the task

ps; There are a lot of reasonable arguments for and against, in https://dba.stackexchange.com/questions/2445/should-binary-files-be-stored-in-the-database - my thoughts, personally, align with Tek's

how to insert picture or image into oracle database?

You should use BLOB (Binary Large Object) which is ideal for storing multimedia content like images.

Check this out for storing images using BLOB.

Insert HEX string as IMAGE in Oracle

your approach is right. its possibly the input HEX that is wrong? how are you getting it.

for example:

SQL> create table img (img blob);

Table created.

SQL> insert into img values (hextoraw('47494638396120002000f700000000000000330000660000990000cc0000ff002b00002b33002b66002b99002bcc002bff0055000055330055660055990055cc0055ff008
0000080330080660080990080cc0080ff00aa0000aa3300aa6600aa9900aacc00aaff00d50000d53300d56600d59900d5cc00d5ff00ff0000ff3300ff6600ff9900ffcc00ffff3300003300333300663300993300cc3300f
f332b00332b33332b66332b99332bcc332bff3355003355333355663355993355cc3355ff3380003380333380663380993380cc3380ff33aa0033aa3333aa6633aa9933aacc33aaff33d50033d53333d56633d59933d5cc3
3d5ff33ff0033ff3333ff6633ff9933ffcc33ffff6600006600336600666600996600cc6600ff662b00662b33662b66662b99662bcc662bff6655006655336655666655996655cc6655ff668000668033668066668099668
0cc6680ff66aa0066aa3366aa6666aa9966aacc66aaff66d50066d53366d56666d59966d5cc66d5ff66ff0066ff3366ff6666ff9966ffcc66ffff9900009900339900669900999900cc9900ff992b00992b33992b66992b9
9992bcc992bff9955009955339955669955999955cc9955ff9980009980339980669980999980cc9980ff99aa0099aa3399aa6699aa9999aacc99aaff99d50099d53399d56699d59999d5cc99d5ff99ff0099ff3399ff669
9ff9999ffcc99ffffcc0000cc0033cc0066cc0099cc00cccc00ffcc2b00cc2b33cc2b66cc2b99cc2bcccc2bffcc5500cc5533cc5566cc5599cc55cccc55ffcc8000cc8033cc8066cc8099cc80cccc80ffccaa00ccaa33cca
a66ccaa99ccaaccccaaffccd500ccd533ccd566ccd599ccd5ccccd5ffccff00ccff33ccff66ccff99ccffccccffffff0000ff0033ff0066ff0099ff00ccff00ffff2b00ff2b33ff2b66ff2b99ff2bccff2bffff5500ff553
3ff5566ff5599ff55ccff55ffff8000ff8033ff8066ff8099ff80ccff80ffffaa00ffaa33ffaa66ffaa99ffaaccffaaffffd500ffd533ffd566ffd599ffd5ccffd5ffffff00ffff33ffff66ffff99ffffccffffff0000000
0000000000000000021f904010000fc002c00000000200020000008ff00371429b2619fc183080dbe899630e140810407366c2870e1c47d103764bc887020135017230e8cc8f1601168251f1661387119ca7dd194edabb3a
c24cb92758c286482b188c5921ca31154267483b29120815e7c03d10e44262f955e84080ad4c88755056e80131528c1a4a0b41601ab72a5d20d490d5a458b30ec489940d31eacda10d4929a0da325d5bb4fae5abf7db94e7
c53446161b673bfb68d28f8a0d0224405421b4896aa4168102923842370099c8146b4561d29da6d91253783d68908ca944881a309323dcd59e24168b535f7150b566051a204bb46232cd0cee2b18bf77d36b87a22b437804
1194f08121a5c972d3f3634155d296ecb8991275156ba6cb568b51111bb2e28f539d5b5a46317967a713893919f05d2cf4b78a5e468beed979032f715449861023a07074c7b016692562595a7547a22a5765b6d6791f6d04
5e51134a158247134d984230504003b'));

1 row created.

SQL> commit;

Commit complete.

then in my PL/SQL IDE (pl/sql developer) i see..

---

Related Topics