Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Shawn.

Asked: July 06, 2000 - 10:27 am UTC

Last updated: June 24, 2004 - 3:13 pm UTC

Version: Oracle 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi,
I have posed this question to many different Oracle people and lists, but have not recieved a solution yet.

I am using Oracle 8.1.6, with the Intermdedia Cartridge, and Web Agent and Clipboard. I am trying to load images into my database table using sqlldr. The images are mass scanned in to a file system directory. Once the images are in a specific directory, I need to load these images into the database table. I am able to use sqlldr to upload bfiles, but what I actually need to load are BLOBs. Can anyone tell me what I need to do to use sqlldr to upload BLOBs? I have read all of Oracle's documentation on sqlldr, and they claim that you can load blobs, but don't say how to do it. The table that I am trying to load to is named example13, and the field "image" is defined as ordsys.ordimage. Any help would be greatly appreciated!

Also, is there a better way to get a large number of images into the database?
Thanks,
Shawn

---control file starts here----

LOAD DATA
INFILE example13.dat
INTO TABLE EXAMPLE13
FIELDS TERMINATED BY ','
( EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
RES_FILE FILLER CHAR(60),
"IMAGE" BFILE(CONSTANT "ORDIMGDIR", RES_FILE)
)
---control file ends here----


and Tom said...


given a table:


create table image_load(
id number,
name varchar2(255),
image ordsys.ordimage
)
/

you can use the following .ctl file to load the table

LOAD DATA
INFILE *
INTO TABLE image_load
REPLACE
FIELDS TERMINATED BY ','
( "ID" INTEGER EXTERNAL,
"NAME" CHAR,
file_name FILLER CHAR,
"IMAGE" column object
(
source column object
(
localdata LOBFILE (file_name) TERMINATED BY EOF
NULLIF file_name = 'NONE'
)
)
)

BEGINDATA
1,CLBECK,my_image.gif
2,TKYTE,tom_image.gif
3,CLBECK,NONE
4,TKYTE,another_tom_image.gif




After all the data is loaded, you will probably want to setproperties on all the images so

begin
for c in ( select * from image_load ) loop
c.image.setproperties;
end loop;
end;
/

would do that.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Tom Kyte
November 09, 2001 - 10:23 am UTC

You need to have Oracle interMedia installed and configured

</code> http://docs.oracle.com/docs/cd/A81042_01/DOC/inter.816/index.htm <code>

You can just use a BLOB if you like as well.

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.

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


Tom Kyte
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.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here