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:>

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

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;
/

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:

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

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

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

"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.

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

I have read this thread and the links...
Is anyone using this method : http://www.oracle.com/technology/sample_co...bc10g.html#how1

It basically passes a stream as a parameter to a stored procedure to load a LOB.
I have it working for CLOBs, but BLOBs seem to get jumbled up.

The size is right in the table, but the image is messed up in the case of an image file. For a pdf file the readers say the file is corrupted.

Any insight on using this method?

Hi,

>> I have it working for CLOBs, but BLOBs seem to get jumbled up.

First, the code in the link you sent was specific to CLOB's ("How To Handle CLOBs Easily in JDBC?")

You can send an e-mail to Justin Kestelyn at Oracle, he runs OTN and can get you in0touch with the author . . .

Maybe it was never designed to work with a BLOB?

I would start by checking MOSC for issues:

http://support.oracle.com

I don't know about your method, but I have documented several working ways to load a LOB, CLOB, BLOB here:

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

Thanks for the reply! You have always been such a great help!

I did get it to work with BLOB's. I had an issue with the stream that I was using.

Here is the history and solution...(test code still...not ready for production yet) :

I am using ColdFusion. There was a driver issue that was truncating data going into a BLOB when they called a stored procedure. I was tasked with working around this issue (...instead of fixing it...)

I now have coldfusion code that uses java to send a stream as a parameter to a stored procedure.
It is working well in testing so far. It is capable of loading a record of several fields including a CLOB in 1 call to the database.

So I created a stored procedure :

create or replace PROCEDURE ADD_TEST_bLOB
( blobid IN VARCHAR2
, blobdata IN BLOB
) AS
begin
INSERT INTO test_blob_table values(blobid, blobdata);
COMMIT;
END ADD_TEST_BLOB;

and I call it from this coldfusion code :

<!---this handles a binary file for a blob--->

<!---build the db connection--->
<cfset factory = createobject("java","coldfusion.server.ServiceFactory")>
<cfset dsService = factory.getDataSourceService()>
<cfset ds = dsService.getDatasource( "DataSource1" )><!---this is the named datasource from the cf server--->
<cfset con = ds.getConnection()><!---This is the database connection for java objects to use.--->

<!---get the file and read it into a variable--->
<cfhttp url="http://server.com/test2.pdf" getAsBinary="yes" result="myfile"></cfhttp>

<!---create the stream for the blob--->
<cfset bytestreamobj = createobject("java","java.io.ByteArrayInputStream")>
<cfset bytestream = bytestreamobj.init(myfile.filecontent)>

<!---create the callable statement--->
<cfset cs = con.prepareCall("begin db.add_test_blob(?,?); end;")>

<!---assign the parameters and execute the statement--->
<cfscript>
cs.setObject("blobid","id");
cs.setBinaryStream("blobdata",bytestream,len(myfile.filecontent));
cs.execute();
</cfscript>

Hi Cosimo,

>> I did get it to work with BLOB's.

Great! And thanks for sharing the working code!