Upload an image using BLOB Options

[LittleGirl_Andre...]

Hi,

I am attempting to Insert an image "C:/hi.jpg" into
oracle10g with BLOB using SQLPlus.

I hv created a table IMAGES...

CREATE TABLE IMAGES (
ID NUMBER(4) PRIMARY KEY,
IMAGE BLOB
);

Is that a correct way to start?

How would you upload the image onto the table?

Thanks in advance.
Andrea:>

[maxdbo]

hi
yes you r going the right way but I would also add a column mime type along with the 2 columns listed. further you can either write a plsql procedure / package to do the job as suggested by tom in this link below. The Insert Link option doesnt seem to work nowadays just copy paste the link below.

http://asktom.oracle.com/Please/ask/f?p=49...#10676351446970

hope this helps
thanks
MAX

[LittleGirl_Andre...]

Ok, I hv tried copy and paste the following code into Oracle10g using SQLPlus...
It returned "Package Body created with compilation errors"

and entering "exec image.load('toolbar.gif;);" into SQLPlus
will return "IMAGE.LOAD must be declared".

Does anyone know wot's wrong with the following code
or any other way for uploading an image to Oracle?

Desperate for help, Thank You!

Andrea
----------------------------------------------------------

CREATE TABLE IMAGES
(
IMAGE_NAME VARCHAR2(30) primary key,
MIME_TYPE VARCHAR2(30),
CONTENT BLOB
)

CREATE OR REPLACE PACKAGE BODY IMAGE AS

PROCEDURE load(filename VARCHAR2) AS
f_lob BFILE;
b_lob BLOB;
image_name VARCHAR2(30);
mime_type VARCHAR2(30);
dot_pos NUMBER;
BEGIN
dot_pos := INSTR(filename,'.');
image_name := filename; --SUBSTR(filename,1,dot_pos-1);
mime_type := 'image/'||SUBSTR( filename,dot_pos+1,length(filename) );

INSERT INTO images values(image_name,mime_type,empty_blob() )
RETURN content INTO b_lob;
f_lob := BFILENAME('SOURCE_DIR',filename);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
COMMIT;
END;
END;
/

[ahmedb72]

when you get the error "Package Body created with compilation errors", type the following in SQL Plus and let us know about the returning message you get:

CODE

SQL> Show errors

[Siddharta]

--You need first to declare your package :

CREATE OR REPLACE PACKAGE IMAGE AS
PROCEDURE LOAD(filename VARCHAR2);
PROCEDURE get(name varchar2);
end;
/

--then you specify your package
CREATE OR REPLACE PACKAGE BODY IMAGE AS

PROCEDURE load(filename VARCHAR2) AS
f_lob BFILE;
b_lob BLOB;
image_name VARCHAR2(30);
mime_type VARCHAR2(30);
dot_pos NUMBER;
BEGIN
dot_pos := INSTR(filename,'.');
image_name := filename; --SUBSTR(filename,1,dot_pos-1);
mime_type := 'image/'||SUBSTR( filename,dot_pos+1,length(filename) );

INSERT INTO images values(image_name,mime_type,empty_blob() )
RETURN content INTO b_lob;
f_lob := BFILENAME('SOURCE_DIR',filename);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
COMMIT;
END;
/*=====*/
PROCEDURE get(name varchar2) AS
vblob BLOB;
mime_type VARCHAR2(30);
BEGIN
select content,mime_type
into vblob,mime_type
from images
where image_name=name;
owa_util.mime_header(mime_type, false);
owa_util.http_header_close;
wpg_docload.download_file(vblob);
exception when others then
htp.p(sqlcode || sqlerrm);
END;
/*=====*/
END;
/

But this code doesn't seem to fit your need because you said that you would like to store the c:\mydir\myfile.jpg from you computer (client side) into blob field in database (server side), and with this package , you need to create on server side (data base) a source directory (SQL> create directory source_dir as '/mydir'; --if you have an image file in /mydir/toolbar.gif) in which you will store all yours file you need to load in blob field with this kind of statement :
SQL> exec image.load('toolbar.gif');

So with this exemple you can not load 'client-side' file in 'server-side' blob field, and AS ME you would like to load from client side , if someone would have a suggestion, he/she would answer to a problem which concern 2 persons now

[burleson]

Hi,

Other options include Perl:

http://www.dba-oracle.com/t_dbi_perl_lob_large_objects.htm

and you can load LOB's with Java OCI:

http://www.dba-oracle.com/t_java_graphics_blob_retrieval.htm

[ranatahirfarooq]

--You need first to declare your package :

CREATE OR REPLACE PACKAGE IMAGE AS
PROCEDURE LOAD(filename VARCHAR2);
PROCEDURE get(name varchar2);
end;
/

--then you specify your package
CREATE OR REPLACE PACKAGE BODY IMAGE AS

PROCEDURE load(filename VARCHAR2) AS
f_lob BFILE;
b_lob BLOB;
image_name VARCHAR2(30);
mime_type VARCHAR2(30);
dot_pos NUMBER;
BEGIN
dot_pos := INSTR(filename,'.');
image_name := filename; --SUBSTR(filename,1,dot_pos-1);
mime_type := 'image/'||SUBSTR( filename,dot_pos+1,length(filename) );

INSERT INTO images values(image_name,mime_type,empty_blob() )
RETURN content INTO b_lob;
f_lob := BFILENAME('SOURCE_DIR',filename);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
COMMIT;
END;
/*=====*/
PROCEDURE get(name varchar2) AS
vblob BLOB;
mime_type VARCHAR2(30);
BEGIN
select content,mime_type
into vblob,mime_type
from images
where image_name=name;
owa_util.mime_header(mime_type, false);
owa_util.http_header_close;
wpg_docload.download_file(vblob);
exception when others then
htp.p(sqlcode || sqlerrm);
END;
/*=====*/
END;
/

But this code doesn't seem to fit your need because you said that you would like to store the c:\mydir\myfile.jpg from you computer (client side) into blob field in database (server side), and with this package , you need to create on server side (data base) a source directory (SQL> create directory source_dir as '/mydir'; --if you have an image file in /mydir/toolbar.gif) in which you will store all yours file you need to load in blob field with this kind of statement :
SQL> exec image.load('toolbar.gif');

So with this exemple you can not load 'client-side' file in 'server-side' blob field, and AS ME you would like to load from client side , if someone would have a suggestion, he/she would answer to a problem which concern 2 persons now

So with this exemple you can not load 'client-side' file in 'server-side' blob field, and AS ME you would like to load from client side , if someone would have a suggestion, he/she would answer to a problem which concern 3 persons now wink.gif

[HAL9000]

"AS ME you would like to load from client side"

First, be clear, Oracle DOES NOT do this, and you must write it yourself, in Java, webmethods, or whatever client side app software that you are using.

The Oracle SQL*Net "client" is disconnected from the Oracle server, and the server can only communicate to the client as you define it.

If you want to store a BLOB image on the server, you must write code to either "push" or "pull" it:

PUSH - Use an FTP program to transfer the BLOB to a known directory on the server. After FTP is complete, invoke PL/SQL to load it.

PULL - Have the server open a secure connection to the client disk as a shared device. Once the client-side disk is mounted, PL/SQL can reach it.

[burleson]

Greetings,

You don't need to open external connections to store a BLOB into a table from a PC client, you just need to use Java to "stream" the image into a pre-selected row.

Here is how we do it:

http://www.dba-oracle.com/t_insert_image_blob_client_pc.htm