Access CLOB datatypes

i think this example will help u.

Example
The example that follows shows the PL/SQL version of method 4(a) listed above for loading a LONG which has been written to a flat file named /tmp/sound_clip into a LOB column.


--------------------------------------------------------------------------------
Note:

There is a separate bulletin which addresses how to convert a LONG to a LOB using the OCILobWrite command.
--------------------------------------------------------------------------------

Complete the following steps to execute the loadlob.sql PL/SQL script:

Create the file sound_clip with the following contents and copy it to the /tmp directory:
sound_clip: abcdefghijklmnopqrstuvwxyz


Run the following SQL script:

% sqlplus scott/tiger @loadlob
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;


The output should resemble:
SQL> @loadlob
SQL> set echo on;
SQL> connect sys/change_on_install;
Connected.
SQL> GRANT ALL on dbms_lob to scott;
Grant succeeded.
SQL> GRANT CREATE ANY DIRECTORY to scott;
Grant succeeded.
SQL> CONNECT scott/tiger;
Connected.
SQL> DROP DIRECTORY some_dir_alias;
Directory dropped.
SQL> CREATE DIRECTORY some_dir_alias as '/tmp';
Directory created.
SQL> DROP TABLE multimedia;
Table dropped.
SQL>  
SQL> /* CREATE THE TABLE */
SQL>  
SQL> create table multimedia
  2  (
  3        id           number,
  4        video_clip   clob default empty_clob(),
  5        audio_clip   clob default null,
  6        some_file    bfile default null
  7  ) ;
 
Table created.
 
SQL>  
SQL>  
SQL> /* LOAD DATA INTO THE TABLE */
SQL> /* Insert 10 rows into the table which defaults to initializing */
DOC> /* the video_clip to empty and the audio_clip and some_file to null.*/
DOC> */
SQL>  
SQL> /* The fast way to do this is to use array inserts with OCI */
DOC> /* (see OCIBindArrayOfStruct) */
DOC> /* The not so fast way is to use a loop in plsql as follows. */

SQL>  
SQL> declare
  2        loop_count integer;
  3  begin
  4        loop_count := 1;
  5        while loop_count <= 10 loop
  6          insert into multimedia (id) values (loop_count);
  7          loop_count := loop_count + 1;
  8        end loop;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> /* Initialize the first audio clip to the actual value. */
DOC> /* Then copy this value to all rows in the table. */

SQL> DECLARE
  2    ac      CLOB;
  3    amount  INTEGER;
  4    a_file  BFILE := BFILENAME('SOME_DIR_ALIAS', 'sound_clip');
  5  BEGIN
  6    UPDATE multimedia SET audio_clip = empty_clob() WHERE id = 1
returning
  7             audio_clip into ac;
  8  
  8  /* Open the server side file that contains the audio clip, load it  */
  9        /* into the clob and then close the file.  Note, assume that the */
 10        /* audio clip is only 32,000 bytes long and that it starts at */
 11        /* position 1 in the file.*/
 12    dbms_lob.fileopen(a_file, dbms_lob.file_readonly);
 13    amount := 26;
 14    /* note that the destination and source offsets default to 1 */
 15    dbms_lob.loadfromfile(ac, a_file, amount);
 16    dbms_lob.fileclose(a_file);
 17    COMMIT;
 18  
 18  /* Update all rows in the table to the audio clip we just loaded. */
 19    UPDATE multimedia SET audio_clip =
 20             (SELECT audio_clip FROM multimedia WHERE id = 1)
 21             WHERE audio_clip is null;
 22  end;
 23  /
 
PL/SQL procedure successfully completed.
 
SQL>  
SQL> select id, audio_clip from multimedia;
 
        ID
-----------                                                                                                                                      
AUDIO_CLIP
---------------------------------------------------------------------------                                                                    
           1                                                                    
abcdefghijklmnopqrstuvwxyz                                                    
         2                                                                    
abcdefghijklmnopqrstuvwxyz                                                    
         3                                                                    
abcdefghijklmnopqrstuvwxyz                                                    
 
        ID
-----------                                                                                                                                      
AUDIO_CLIP
---------------------------------------------------------------------------                                                                    
         4                                                                    
abcdefghijklmnopqrstuvwxyz                                                    
         5                                                                    
abcdefghijklmnopqrstuvwxyz                                                    
         6                                                                    
abcdefghijklmnopqrstuvwxyz                                                    
 
        ID
-----------                                                                                                                                      
AUDIO_CLIP
---------------------------------------------------------------------------                                                                    
         7                                                                    
abcdefghijklmnopqrstuvwxyz                                                    
         8                                                                  
abcdefghijklmnopqrstuvwxyz                                                    
         9                                                                  
abcdefghijklmnopqrstuvwxyz                                                    
 
        ID
-----------                                                                                                                                      
AUDIO_CLIP
---------------------------------------------------------------------------                                                                    
        10                                                                  
abcdefghijklmnopqrstuvwxyz                                                    
 
10 rows selected.
 
SQL>  
SQL> quit