inserting an image

user11153253

Hi folks,

how can i insert a image which is GIF or JPG format into a oracle database table

for example i have tabe imagetesttable  and image is TEST1.GIF

>desc ebase.imagetesttable

Name  Null Type

----- ---- ----

IMAGE      BLOB

>insert into ebase.imagetesttable values('C:\Users\mohammh1\Desktop\TEST1.GIF');

it throwing an error.

Error report:

SQL Error: ORA-01465: invalid hex number

01465. 00000 -  "invalid hex number"

*Cause:   

*Action:

can any body suggest me.

TPD-Opitz

There is no auto conversion for blobs in sqlpus.

3 secons in Google lea to this with some more effort you may find a document for your actual database release but that may containg the verry same information...

bye

TPD

Nimish Garg

you need to dbms_lob.loadfromfile to convert file to binary and put them in BLOB

check following link.

One Can Succeed at Almost Anything For Which He Has Enthusiasm...: Insert an Image File into Oracle Database

Moazzam

1) First create directory object e.g.

CREATE DIRECTORY imgdir AS 'C:\images\';

2) Write procedure to insert data e.g. sample procedure:

CREATE OR REPLACE PROCEDURE load_file ( pfname VARCHAR2) IS 
    l_size number; 
    l_file_ptr bfile; 
    l_blob blob; 
BEGIN 
    l_file_ptr := bfilename('IMGDIR', pfname); 
    dbms_lob.fileopen(l_file_ptr); 
    l_size := dbms_lob.getlength(l_file_ptr); 
    INSERT INTO images ( img_id, icon ) VALUES ( image_id.NEXTVAL, empty_blob() ) RETURNING icon 
    INTO l_blob; 
    dbms_lob.loadfromfile(l_blob, l_file_ptr, l_size); 
    COMMIT; 
    dbms_lob.close(l_file_ptr); 
END; 

3) Now execute the procedure passing it the file name. e.g.

EXECUTE LOAD_FILE('img1.jpg');

Please read How to create the table in Oracle that is populated by images(blob fields) | Jaspersoft Community for details.

user11153253

Hi Mozzam,

i have don this by below

insert into ebase.image_table values('\\HQIT2-EVEREST\G:\image\test2.JPG');

1 row inserted

commit;

thanks