loadlob.sql 
-----------
set echo on; 
connect sys/change_on_install;  
grant all on dbms_lob to scott;  
grant create any directory to scott;  
connect scott/tiger; 
drop directory some_dir_alias; 
create directory some_dir_alias as '/tmp';  
drop table multimedia; 
  
/* Create the table */  
  
CREATE TABLE multimedia  
(  
   id           NUMBER,  
   video_clip   CLOB DEFAULT empty_clob(),  
   audio_clip   CLOB DEFAULT NULL,  
   some_file    BFILE DEFAULT NULL  
) ;  
  
/* Load data into the table */  
/* Insert 10 rows into the table which defaults to initializing */   
/* the video_clip to empty and the audio_clip and some_file to null. */  
  
/* The fastest way to do this is to use array inserts with OCI */   
/* (see OCIBindArrayOfStruct) */  
/* The less speedy method is to use a loop in PL/SQL as follows. */   
  
declare  
   loop_count integer;  
begin  
   loop_count := 1;  
   while loop_count <= 10 loop  
     insert into multimedia (id) values (loop_count);  
     loop_count := loop_count + 1;  
   end loop;  
end;  
/  
  
/* Initialize the first audio clip to the actual value. */   
/* Then copy this value to all rows in the table. */  

declare  
  ac      clob;  
  amount  integer;  
  a_file  bfile := BFILENAME('SOME_DIR_ALIAS', 'sound_clip');  
begin  
  update multimedia set audio_clip = empty_clob() where id = 1 returning  
        audio_clip into ac;  
  
/* Open the server side file that contains the audio clip, load it into */  
/* the CLOB and then close the file.  Assume that the audio clip is */  
/* only 32,000 bytes long and that it starts at position 1 in the file. */  
 
  dbms_lob.fileopen(a_file, dbms_lob.file_readonly);  
  amount := 26; 
 
/* Note that the destination and source offsets default to 1 */
  
  dbms_lob.loadfromfile(ac, a_file, amount);  
  dbms_lob.fileclose(a_file);  
  commit;  
  
/* Update all rows in the table to the audio clip you just loaded. */  
  update multimedia set audio_clip =   
        (select audio_clip from multimedia where id = 1)  
        where audio_clip is null;  
end;  
/  
 
select id, audio_clip from multimedia;