Multimedia Databases

Here is example on how to define table with large objects i.e LOB

CREATE TABLE grape
( grape_name VARCHAR2(30) ,
grape_txt CLOB DEFAULT EMPTY_CLOB() ,
picture BLOB DEFAULT EMPTY_BLOB() ,
CONSTRAINT prim_grape PRIMARY KEY (grape_name) )

here grape_text is the the description of grape and is stream of characters so is stored as CLOB. Whereas picture is stored as BLOB. Here the image will be stored in database itself. Other way is we can store it as BFILE. Then we can have used :
picture BFILE ,

Now while inserting data we have to do as :
INSERT INTO grape (grape_name, picture) VALUES ('chardonnay' , BFILENAME('PHOTO_DIR','chardonnay.jpg'))

Using DBMS_LOB Package :
CREATE PROCEDURE wine_read_bfile
IS
Lob_loc BFILE ;
Amount INTEGER :=32767
Position INTEGER := 1;
Buffer RAW(32767 ) ;
BEGIN
SELECT picture INTO Lob_loc FROM grape
WHERE grape_name = 'chardonnay' ;
DBMS_LOB.OPEN(LOB_loc, DBMS_LOBB.LOB_READONLY);
DBMS_LOB.READ(Lob_loc, Amount , Position , Buffer);
DBMS_LOB.CLOSE(LOb_loc);
END ;
here the file will be loaded in the buffer. The BFILE is manipulted through LOB locator.
You can also modify CLOB using
DBMS_LOB.WRITE(lob_loc,amount,offset, newtext ) ; here the newtext is added at position lob_loc + offset value.