How to store image files in database using insert command

Regarding a post on oracle forum bout how to store image files in database using insert command. Actually, I don’t know it is running well or not cause  I haven’t test it yet but I just kept this for documentation. Sometimes I’ll explore it when I needed. Please give me a notes if You had already try it.

Here is the script.

CREATE TABLE MY_IMAGE_TABLE (
ID NUMBER,
NAME VARCHAR2(20),
IMAGE BLOB);

CREATE OR REPLACE DIRECTORY IMAGES AS ‘/tmp’;
GRANT READ, WRITE ON DIRECTORY IMAGES TO PUBLIC;

CREATE OR REPLACE PROCEDURE load_file_to_my_table (p_file_name IN MY_IMAGE_TABLE.NAME%TYPE) AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO MY_IMAGE_TABLE (id, name, image)
VALUES (1, p_file_name, empty_blob())
RETURN doc INTO v_blob;

v_bfile := BFILENAME(‘IMAGES’, p_file_name);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);
COMMIT;
END;

One thought on “How to store image files in database using insert command

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s