File to CLOB - 2

[mirall]

    После вдумчивого ковыряния asktom.oracle.com, документации и металинка получилась следующая процедурка.
    

  /*------------------------------------------------------------------
  Name:     store_file_to_clob
  Purpose:  Get data from external file and store it to clob
  Author:   longbowgirl
  Created:  2010-05-18
  Modified: 2010-05-18

  Return: output variable - clob
  Comment: i_sBFILE_CSID parameter stores predefined unicode encoding schemes
  ( Encoding Scheme | Oracle Name | bfile_csid Value
    UTF-8             AL32UTF8      873
    UTF-16BE          AL16UTF16     2000
    UTF-16LE          AL16UTF16LE   2002
    CESU-8            UTF8          871
    UTF-EBCDIC        UTFE          872
    UTF-16            UTF16         1000
   )
  ------------------------------------------------------------------*/
  procedure store_file_to_clob (
    i_sFileName in varchar2
  , i_sDirPath in varchar2
  , i_sBFILE_CSID in number
  , io_clFileContents in out nocopy clob
  ) is
    bfSourceFile bfile; 
    sWarningMessage number; 
    iDestinationOffset integer := 1; 
    iSourceOffset integer := 1; 
    iLanguageContext integer := sys.dbms_lob.DEFAULT_LANG_CTX; 

  begin 
    -- allocate clob
    sys.dbms_lob.CreateTemporary (
        lob_loc => io_clFileContents
      , cache => TRUE
      , dur => DBMS_LOB.SESSION
      );

    -- Define BFILE
    bfSourceFile := BFILENAME(i_sDirPath, i_sFileName); 

    sys.dbms_lob.open(bfSourceFile, sys.dbms_lob.FILE_READONLY); 

    sys.dbms_lob.LoadClobFromFile ( 
        dest_lob => io_clFileContents
      , src_bfile => bfSourceFile
      , amount => sys.dbms_lob.GetLength( bfSourceFile )
      , dest_offset => iDestinationOffset
      , src_offset => iSourceOffset
      , bfile_csid => i_sBFILE_CSID
      , lang_context => iLanguageContext
      , warning => sWarningMessage
    );

    if sWarningMessage = sys.dbms_LOB.WARN_INCONVERTIBLE_CHAR then
      -- logging of conversion warnings should be here
      null;
    end if;

    sys.dbms_lob.FileClose( bfSourceFile ); 

  exception 
    when others then
      -- exception handling will be added later
      null;
  end store_file_to_clob;