OraNA :: Oracle News Aggregator

A goal when dealing with multimedia is to load images into the multimedia types, and those images exist on a filesystem.

To load them in, an Oracle directory needs to be defined. This directory allows you to assign a name to a physical location. Access can then be given to users using the granted command. Directories are used by a number of tools including datapump.

Following are examples of creating a directory:

create or replace directory win_mydir as 'c:\temp';
create or replace directory unix_mydir as '/u01/imageloc';

You can then grant read access to a user like:

grant read on directory win_mydir to websys;

Note 1: The directory does not have to exist. Existance is only checked at runtime usage.
Note 2: Access is recursive. You can access subdirectories that exist in this top level directory.
e.g.
If there is a file called c:\temp\subdir\abc.jpg, then you can access it using directory win_mydir and file name called subdir\abc.jpg

The following highlights this concept:

create table myaudio(pk integer, myaud ordsys.ordaudio);
insert into myudio values(1,ordsys.ordaudio.init());
commit;

declare
 cursor c1 is select * from myaudio for update;
 c1rec c1%rowtype;
 ctx raw(4000);
begin
 open c1;
 fetch c1 into c1rec;
 close c1;
 c1rec.myaud.importfrom(ctx,'FILE','MYDIR','subdir\bwf_short.mp3');
 c1rec.myaud.setproperties(ctx);
 update myaudio set myaud = c1rec.myaud;
 commit;
end;
/

SQL> select m.myaud.audioduration from myaudio m;

MYAUD.AUDIODURATION
-------------------
                12

At the heart of the Oracle Multimedia data types (ORDSYS.ORDIMAGE etc) are blobs. These are binary large objects and can store huge amounts of data in them. At the time of writing the largest blob I have managed to load in was over 10G and I will soon be attempting to load in ones over 100G in size.

You can use the methods that come with multimedia to manipulate and manage the types, but you can also use the dbms_lob package to manipulate the lobs in other ways.

The doco for dbms_lob can be found at:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_lob.htm#BABDDFDH

Not much changed between 10g and 11g, so the 11g documentation is great as a reference.

You can use dbms_lob to copy, trim, load and manipulate the lob. You can use dbms_lob to load an image into Multimedia, then use the multimedia setproperties method to recognise it (or you can just use multimedia methods to load and recognise in the one method). Using dbms_lob just gives you other options.

I use dbms_lob for managing temporary lobs. I will take a lob loaded from apache, store it in temporary (cached in memory), then create web quality and thumbnail images from the temporary. It runs faster this way. Downside is that my temp storage area is very large.

When manipulating lobs using dbms_lob, you need to lock the row containing the blob to ensure no one else can modify it. That means doing a select .. for update statement. One has to be careful now. Once you lock the row, certain Oracle statements do implicit commits which will release the lock. You will then get an error if you try to modify the lob (you will need to reacquire the lock.

declare
cursor c1(vpk integer) is select myphoto from my_image where pk= vpk for update;
c1rec  c1%rowtype;
blb    blob;
begin
open c1(123);
fetch c1 into c1rec;
close c1;
-- create the temporary blob
dbms_lob.createtemporary(blb,TRUE);
-- copy the blob in the table to the temporary blob
dbms_lob.copy(blb,
                           c1rec.myphoto.source.localdata,
                           dbms_lob.getlength(c1rec.myphoto.source.localdata));
-- manipulate the temporary lob
... put in your own statements here

-- copy it back
dbms_lob.copy(c1rec.myphoto.source.localdata,
                          blb,
                          dbms_lob.getlength(blb));

-- set the properties on it
c1rec.myphoto.setproperties;

-- update the database to reflect the change
update my_image set myphoto = c1rec.myphoto where pk = 123;
commit;

dbms_lob.freetemporary(blb);
end;
/

Its best to trap any errors around the setproperties command (e.g. begin .. exception when others then ... end;) in case the manipulate blob isn't valid.

I can also create a temporary multimedia type as follows:

declare
mytemp ordsys.ordimage;
begin
mytemp := ORDSYS.ORDIMAGE.init;
dbms_lob.createtemporary(mytemp.source.localdata,TRUE);
.. add your manipulation commands here
dbms_lob.freetemporary(mytemp.source.localdata);
end;
/

You Tube interview on Oracle 11G

Upgrading to Oracle 11G - Tips. techniques (209Kb)

Q: When dealing with multimedia how big should my redo and undo be?
A: Because storage is cheap, don't get cheap on storage. When dealing with lob's the more storage you give the database the better it will perform. If I knew that the largest lob in my database was going to be 1G, I would try and ensure that my redo logs were as big as this or bigger
e.g. I would create my redo logs 10 x 1G in size (10 redo log each 1G in size). Oracle can quite happily deal with redo logs smaller than this. I have loaded into an Oracle database over 30G in one blob with redo logs 100mb in size. It just will not run as efficiently as if you made them bigger. This is crucial in an environment where multiple users are loading blobs at the same time (or if you are loading them in, in parallel).
I typically make my undo tablespaces 1G in size. Databases that are over 1Tb in size can have undo tablespaces over 50Gb in size.

Also, when dealing with Images I set my temp tablespace to be over 10G in size. Its because I do a lot of temporary work with temp lobs. Some of the databases I work with have temp over 30G in size. 30G might sound like a lot, but 1Tb costs less than $200. So don't get cheap with storage. Also remember that temp tablespace can sit on a cheap disk because if its lost, you don't need to recover it. So no need to worry about backing it up.

Q: Any special init.ora parameters needed?
A: No.
(what, you were expecting an answer here. There are some parameters but you don't need them. In Oracle 11G the defaults are great. You tune multimedia by getting your storage parameters right to begin with).

Q: What about Oracle Multimedia and Oracle XE (Express Edition)?
A: Oracle Multimedia uses Java for its methods. Java does not come with the Express Edition. The types for Oracle Multimedia need to be manually configured in the database, but having the types is only half the story, the Multimedia methods can be very useful to have.
So no, Oracle XE doesn't work with Oracle Multimedia. There are ways around it that require programming (see www.piction.com)

Q: What limitations are there with ORDSYS.ORDIMAGE, ORDVIDEO and ORDAUDIO?
A: ORDPHOTO can only process a small range of image types. This include JPG, PNG, TIF and GIF. These are the most commonly used ones. Once your JPG grows beyong 4mb in size, you might have issues processing it in 10G (you will likely run out of java memory). In Oracle 11G, Oracle tiles the image and processes it in chunks. It means that as the image grows in size it will take longer to process it. TIF images can be processed at any size in Oracle 10G and 11G as the compression algorithm used differs to JPEG.

With ORDVIDEO and ORDAUDIO, Oracle will recognise basic characteristics of the image (e.g. duration). There are no methods available for processing it (like extracting a frame, cropping or converting between formats). Dealing with audio and video codecs is very complex. Horribly complex and expensive. Converting a 1minute video of TV quality can take over 30 seconds on a well configured box. There are open source tools in the market place to do it, but this is where I have spent a lot of time doing it, so either go to www.piction.com for more info or email me with questions you have (marcel@piction.com)

I am putting together a series of blog's covering the basics of using Oracle multimedia. I am doing it to preempt questions that are usually asked when trying to configure it and hopefully fast track the usage of it.

To create an Oracle table that uses a multimedia type is very simple. There are a number of types you can reference depending on the data that is to be stored. Primary keys are not required on the table definitions.

The types are:

• ORDSYS.ORDIMAGE - for digital photos
• ORDSYS.ORDVIDEO - for video
• ORDSYS.ORDAUDIO - for audio
• ORDSYS.ORDDOC -for documents

Why such long names?

ORDSYS is actually an Oracle schema. This schema owns these types. So to access these types you need to be able to see this schema.

The following example shows how to create a table using the ORDSYS.ORDPHOTO type. Subsequent blogs will cover the storage and manipulation of these types.

create table my_image
(
mycolumn varchar2(100),
myphoto ordsys.ordimage
);

Its that easy.

The aim of the multimedia types is to group together related information. All the types reference another type called ORDSYS.ORDSOURCE which contains the binary (blob) for the type. The column that contains this type is called source. How do I know this? Its simple, from SQL*Plus you can reference the type's just by doing a describe on them. Downside is the describe will also list all the methods available which means there is a lot of information to see:

SQL> desc ordsys.ordimage
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 SOURCE                                             ORDSYS.ORDSOURCE
 HEIGHT                                             NUMBER(38)
 WIDTH                                              NUMBER(38)
 CONTENTLENGTH                                      NUMBER(38)
 FILEFORMAT                                         VARCHAR2(4000)
 CONTENTFORMAT                                      VARCHAR2(4000)
 COMPRESSIONFORMAT                                  VARCHAR2(4000)
 MIMETYPE                                           VARCHAR2(4000)

METHOD
------
 STATIC FUNCTION INIT RETURNS ORDIMAGE

A lot of information is displayed on the screen, most of it are the methods that are available to use. Think of a method as a PL/SQL function or procedure that is locked into the row. You can run a method that will take the current row (image) and perform an operation against it. With ORDSYS.ORDIMAGE you can invoke methods that rotate or crop the image.

The first bit of information displayed contains the attributes (sub types), or columns of the type. Internally in Oracle, when you define a type, Oracle breaks it down into the raw columns and stores them, which is why it is great that an Oracle table can store 1000 columns in it, because even if a table has a couple of types, if these types refer to other types and other columns then a table might contain hundreds of columns and you might no realise it.

Types can be nested. As seen in the example above, the column source is also a type of ORDSYS.ORDSOURCE. When we describe it, it looks like:

SQL> desc ordsys.ordsource
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 LOCALDATA                                          BLOB
 SRCTYPE                                            VARCHAR2(4000)
 SRCLOCATION                                        VARCHAR2(4000)
 SRCNAME                                            VARCHAR2(4000)
 UPDATETIME                                         DATE
 LOCAL                                              NUMBER
 MEMBER PROCEDURE SETLOCAL
 MEMBER PROCEDURE CLEARLOCAL

METHOD
------
 MEMBER FUNCTION ISLOCAL RETURNS BOOLEAN

and now we see the location of the blob. So to access the blob its column.source.localdata. Subsequent blogs will cover the syntax for accessing the columns/attributes in these types.

How do I access the attributes (column values) in a multimedia type?

Just remember that SQL*Plus can't deal with blobs, and as all multimedia types reference a blob, its not easy dumping this information out on the screen. Just keep in mind these tips:

1. Always use an alias on the table name
2. Use dot notation to access columns and attributes
3. In PL/SQL use a column alias to ensure the name is unique (and under 30 chars in length)
4. Don't use an alias that is an Oracle keyword
5. Avoid using alias names that might clash with PL/SQL variable names in your code
   

The following are examples (assume the column is myphoto of type ordsys.ordimage)

select u.myphoto.width width from my_image u;
select u.myphoto.source.srcname from my_image u where u.myphoto.width < 100;
select dbms_lob.getlength(u.myphoto.source.localdata) len from my_image u;

For those that are confused, just think of these types/attributes as very long column names. If it still confusing you can create a view thats sits over the table and hides the column names.

The advantage of using types is they are great for enforcing consistency in using common data sets. A good example is the person type. Rather than each developer creating their own table with its own attributes and column sizes for person (and their address and details). The DBA can create a central type and then everyone references it. It ensures consistency in data usage.

When creating a table there are a number of options available for deciding how the blob should be stored and accessed. You can determine the tablespace it will reside in, and whether it should be cached for performance.

Blobs should be stored in a tablespace with a block size 16K or greater, but if your database is created with 8K block size, don't feel obliged to create a separate tablespace with block size 16K, unless you (or your DBAs) are skilled in SGA memory management. If you are using RAC and your block size is 4K, tuning will be different, and its best not to cache the blobs.

The dimensions used to decide on the storage options are as follows:

1. How large in bytes will the blob be?
2. How often will the blob be accessed?
3. How critical is memory usage on the instance?
4. How critical is performance accessing the blob?

To highlight this, think of an application where you have an original image and its thumbnail. The thumbnail will be small (typically under 4k) and accessed frequently. The original might not be accessed that often. As such, the thumbnail should have a different storage option compared to the original:

create table my_image
(
my_key                    number(16),
image_thumbnail           ORDSYS.ORDIMAGE,
digital_image             ORDSYS.ORDIMAGE
)
tablespace tablespace_128K pctfree 0 storage( pctincrease 0 maxextents unlimited)
LOB (digital_image.source.localdata) STORE AS l_image
   (TABLESPACE tablespace_10m
    disable storage in row
    STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 0)
    CHUNK 16384
    NOCACHE LOGGING)
LOB (image_thumbnail.source.localdata) STORE AS l_image_thumbnail
   (TABLESPACE  tablespace_10m
    enable storage in row
    STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 0)
    CHUNK 16384
    CACHE PCTVERSION 100);

In this example the original image is stored in the column called digital_image. Its configured not to be cached as its not accessed frequently. Accessing it will not consume SGA and will require i/o. The column image_thumbnail is typically under 4000 bytes, so for performance we store it in the row. We cache it in memory for faster access.

Tablespace will have to be created as locally managed. In the example above two tablespaces are used. One is created with extent size 128K and is designed for storing relational data. The other has a much larger extent size (10Mb) and is designed for storing large number of blobs. For those not familiar with tablespace creation I have included some example creation scripts below.

If running on Oracle 11G, we can enhance this example to use securefiles which are faster and better to use just by changing the STORE AS clause
from:
LOB (digital_image.source.localdata) STORE AS l_umo_photo
to:
LOB (digital_image.source.localdata) STORE AS SECUREFILE l_umo_photo

Following are example tablespace creation scripts:

CREATE TABLESPACE tablespace_128k EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K segment space management auto datafile size 400M;

Will create a tablespace of size 400M. OMF is assumed to be configured (OMF is Oracle Managed Files, and is the default location for datafiles when created. Its configured using a database parameter. To see if its configured - and if you have DBA access - type this command in:

SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
db_create_file_dest                  string      c:\oradata
db_create_online_log_dest_1          string

CREATE TABLESPACE tablespace_10m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M segment space management auto datafile 'r:\oradata\VNLZ\piction_img_1.dbf' size 1G;

Will create a tablespace of size 1G on windows on the r: drive.

CREATE TABLESPACE tablespace_ml EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16K datafile '/usr/lib/oracle/xe/oradata/XE/piction_sml_1.dbf' size 100M segment space management manual;

Will create a tablespace used for storing small tables. Segment space must be manual as the database block size (16k) x 5 is greater than the uniform size. This example shows how to create a tablespace on Oracle XE and OMF is not used.