loading images using lob
RAGHUNATH A, November 09, 2001 - 5:02 am UTC
When creating the table given in the example i get an error message saying "ordsys.ordimage" invaid datatype
what needs to be done
How about loading images from blob in the other table.
Ian Matyssik, April 12, 2004 - 10:47 am UTC
Hello,
I have been looking all over the web and on tahiti.oracle.com for example of loading BLOB image in one table into ORDImage. Could you be kind and give some hints on how to do it in PL/SQL anonymous block.
April 12, 2004 - 1:16 pm UTC
I'm assuming that
a) you have an existing table of images just in blobs
b) you want to convert them into ordsys.ordimages
ops$tkyte@ORA9IR2> create table image_load(
2 id number,
3 name varchar2(255),
4 image ordsys.ordimage
5 )
6 /
Table created.
<b>that is where we ultimately want the images to go...</b>
ops$tkyte@ORA9IR2> create table t ( x blob );
Table created.
ops$tkyte@ORA9IR2> create or replace directory image_dir as '/home/tkyte/Desktop'
2 /
Directory created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_bfile bfile;
3 l_blob blob;
4 begin
5 insert into t (x) values ( empty_blob() ) returning x into l_blob;
6
7 l_bfile := bfilename( 'IMAGE_DIR', 'asktom2.gif' );
8 dbms_lob.fileopen( l_bfile );
9 dbms_lob.loadfromfile( l_blob, l_bfile,
10 dbms_lob.getlength(l_bfile) );
11 dbms_lob.fileclose( l_bfile );
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
<b>that is our table of existing images...
All we need to do is get an image variable, put the blob into it, call setproperties and insert it. that'll copy the blob over for us</b>
ops$tkyte@ORA9IR2> declare
2 l_image ordsys.ordimage := ordsys.ordimage.init();
3 l_blob blob;
4 l_rowid rowid;
5 begin
6 select x
7 into l_image.source.localdata
8 from t;
9
10 l_image.setproperties;
11
12 insert into image_load
13 ( id, name, image )
14 values
15 ( 1, 'foobar', l_image );
16 commit;
17 end;
18 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select t.id, t.name,
2 t.image.height,
3 t.image.width,
4 t.image.fileformat,
5 dbms_lob.getlength( t.image.source.localdata )
6 from image_load t;
ID NAME IMAGE.HEIGHT IMAGE.WIDTH
---------- ------------------------------ ------------ -----------
IMAGE.FILEFORMAT
------------------------------------------------------------------------
DBMS_LOB.GETLENGTH(T.IMAGE.SOURCE.LOCALDATA)
--------------------------------------------
1 foobar 57 100
GIFF
1823
which is better way to store images
suhail, June 24, 2004 - 1:15 pm UTC
Tom,
I have a question, we have very large images of forest, road maps etc , some pretty big ( > 50MB) , we need to store in the Oracle 9i db. Which is the best way to do it , should I use blob or intermedia feature ordsys.ordimages?
Thanks for your help.
Suhail
June 24, 2004 - 3:13 pm UTC
if you need/want/desire the features of ordsys types -- use that.
if you just want to stick a glob of data in there and retrieve a glob of data later, just use a blob.
Convert From Blob to ORDImage
Nikki, February 05, 2006 - 9:35 am UTC
Hi
we had table1(id Number, image BLOB) with 20 milions of 17k images. we decide to migrate to ORDSYS.ORDImage, so we create table2(id Number, image ORDSYS.ORDImage) and we run this query:
insert into table2
(select id, ORDSYS.ORDImage(ORDSYS.ORDSource(image,NULL,NULL,NULL,SYSDATE,1),NULL,NULL,NULL,NULL,NULL,NULL,NULL)) as image from table1);
After running this query and moving data to new table, when we want to get properties such as height or width or ... it doesn't show any number. or when we run setProperties() method of ORDImage for any rows of table2 it raises "User Defined Exception" error!
How we can solve this problem?
Mysql to oracle migration using sql developer offline method
Chilly, October 25, 2012 - 6:41 pm UTC
Hi Tom,
i have a project assigned which needs migrate to oracle from mysql using sql developer tool. i have to use offline method to migrate the data because of huge size (3TB). i have already build ed the tables. just need to migrate the data. can u please suggest me the process. i have gone through the oracle white paper documentation. but couldnt understand the steps. kindly help me asap.