One DBMS_LOB example

-- &1 for the Oracle directory where the files (gem_custom.zip, Help.xml) are put
-- Refer to http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i998978 for reference
declare
    -- Insert BFILE into BLOB column
    -- Using DBMS_LOB.LoadFromFile OR DBMS_LOB.LoadBLOBFromFile
    src_zip    bfile := BFILENAME('&1', 'gem_custom.zip');
    dest_zip   blob;
    
    -- Insert BFILE into CLOB column
    -- Using DBMS_LOB.LoadCLOBFromFile
    src_help   bfile := BFILENAME('&1', 'Help.xml');
    dest_help  clob;
    dst_offset number := 1;
    src_offset number := 1;
    warning    number;
begin
    -- zip file
    -- first get the lob locator
    update Customization_env
    set    custom_env_file = EMPTY_BLOB()
    where product = 'G' 
    returning custom_env_file into dest_zip;
    
    --1.open    
    DBMS_LOB.open(src_zip, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.open(dest_zip, DBMS_LOB.LOB_READWRITE);
    --2.load
    DBMS_LOB.LOADFROMFILE(dest_lob => dest_zip, 
                          src_lob => src_zip, 
                          amount => DBMS_LOB.getLength(src_zip));
    --2.close                      
    DBMS_LOB.close(dest_zip);
    DBMS_LOB.close(src_zip);
    commit;
    
    -- help xml file
    -- first get the lob locator
    update Customization_env
    set    help_dll_function = EMPTY_CLOB()
    where product = 'G'
    returning help_dll_function into dest_help ;
    
    --1.open
    DBMS_LOB.open(src_help, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.open(dest_help, DBMS_LOB.LOB_READWRITE);
    --2.load
    DBMS_LOB.LoadCLOBFromFile(DEST_LOB     => dest_help,
                              SRC_BFILE    => src_help,
                              AMOUNT       => DBMS_LOB.GETLENGTH(src_help),
                              DEST_OFFSET  => dst_offset,
                              SRC_OFFSET   => src_offset,
                              BFILE_CSID   => DBMS_LOB.DEFAULT_CSID,
                              LANG_CONTEXT => DBMS_LOB.DEFAULT_LANG_CTX,
                              WARNING      => warning);
    --3.close
    DBMS_LOB.close(dest_help);
    DBMS_LOB.close(src_help);
    commit;
exception
 when others then 
     dbms_lob.filecloseall;
     rollback;
     raise;
end;
/

exit

上面的例子是把文件存到数据库中，下面这个例子是从数据库中读取LOB然后存入文件...

-- Refer to http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i997577 
-- for the use of UTL_FILE
declare
    output_file    utl_file.file_type;
    chunk_size     constant pls_integer := 4096;
    buf            raw                    (4096); -- Must be equal to chunk_size 
    written_sofar  pls_integer := 0;              --(avoid PLS-00491: numeric literal required)
    bytes_to_write pls_integer;
    LOB_LEN        PLS_INTEGER;
    V_LOB  BLOB;
    V_DIR VARCHAR2(20) := 'LO_DUMP';
    v_file varchar2(20) := 'gem_custom.zip';
  BEGIN
    
    select custom_env_file into v_lob from CUSTOMIZATION_ENV where product='G'; 
    
    lob_len := dbms_lob.getlength(v_lob);

    output_file := utl_file.fopen(v_dir, v_file, 'WB');
    
    while written_sofar + chunk_size < lob_len loop 

      bytes_to_write := chunk_size;
      dbms_lob.read(v_lob,bytes_to_write,written_sofar+1,buf);
      utl_file.put_raw(output_file,buf);
      written_sofar := written_sofar + chunk_size;

    end loop;

    bytes_to_write := lob_len-written_sofar;
    dbms_lob.read(v_lob,bytes_to_write,written_sofar+1,buf);
    utl_file.put_raw(output_file,buf);

    utl_file.fclose(output_file);

  END;
/

exit