Recall that the BFILENAME() and OCILobFileSetName() functions do not verify that the directory and path name you specify actually exist. You can use the sanity checking functions to verify that a BFILE exists and to extract the directory and file names from a BFILE locator.

A BFILE locator operates like a file descriptor available as part of the standard input/output library of most conventional programming languages. This implies that once you define and initialize a BFILE locator, and open the file pointed to by this locator, all subsequent operations until the closure of the file must be done from within the same program block using the locator or local copies of it. The BFILE locator variable can be used as a parameter to other procedures, member methods, or external function callouts. However, it is recommended that you open and close a file from the same program block at the same nesting level.

You must close all the open BFILE instances even in cases, where an exception or unexpected termination of your application occurs. In these cases, if a BFILE instance is not closed, then it is still considered open by the database. Ensure that your exception handling strategy does not allow BFILE instances to remain open in these situations.

You can close all open BFILEs together by using a procedure like DBMS_LOB.FILECLOSEALL or OCILobFileCloseAll().

Loading a LOB with BFILE Data

In PLSQL, the DBMS_LOB.LOADFROMFILE procedure is deprecated in favor of DBMS_LOB.LOADBLOBFROMFILE and DBMS_LOB.LOADCLOBFROMFILE. Specifically, when you use DBMS_LOB.LOADCLOBFROMFILE procedure to load a CLOB or NCLOB instance, it will perform the character set conversions.

Specifying the Amount of BFILE Data to Load

The value you pass for the amount parameter to functions listed in the table below must be one of the following:

• An amount less than or equal to the actual size (in bytes) of the BFILE you are loading.
• The maximum allowable LOB size (in bytes). Passing this value, loads the entire BFILE. You can use this technique to load the entire BFILE without determining the size of the BFILE before loading. To get the maximum allowable LOB size, use the technique described in the following table:

Loading a BLOB with BFILE Data

The DBMS_LOB.LOADBLOBFROMFILE procedure loads a BLOB with data from a BFILE. It can be used to load data into any persistent or temporary BLOB instance. This procedure returns the new source and the destination offsets of the BLOB, which you can then pass into subsequent calls, when used in a loop.

Loading a CLOB with BFILE Data

The DBMS_LOB.LOADCLOBFROMFILE procedure loads a CLOB or NCLOB with character data from a BFILE. It can be used to load data into a persistent or temporary CLOB or NCLOB instance. You can specify the character set ID of the BFILE when calling this procedure and ensure that the character set is properly converted from the BFILE data character set to the destination CLOB or NCLOB character set. This procedure returns the new source and destination offsets of the CLOB or NCLOB, which you can then passe into subsequent calls, when used in a loop.

CREATE OR REPLACE PROCEDURE loadCLOB1_proc (dst_loc IN OUT CLOB) IS
  src_loc     BFILE := BFILENAME('MEDIA_DIR','monitor_3060.txt') ;
  amt         NUMBER := DBMS_LOB.LOBMAXSIZE;
  src_offset  NUMBER := 1 ;
  dst_offset  NUMBER := 1 ;
  lang_ctx    NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
  warning     NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE ------------');
  DBMS_LOB.FILEOPEN(src_loc, DBMS_LOB.FILE_READONLY);

  /* The default_csid can be used when the BFILE encoding is in the same charset
   * as the destination CLOB/NCLOB charset
   */
   DBMS_LOB.LOADCLOBFROMFILE(dst_loc,src_loc, amt, dst_offset, src_offset,       
       DBMS_LOB.DEFAULT_CSID, lang_ctx,warning) ;
  DBMS_OUTPUT.PUT_LINE(' Amount specified ' || amt ) ;
  DBMS_OUTPUT.PUT_LINE(' Number of bytes read from source: ' || (src_offset-1));
  DBMS_OUTPUT.PUT_LINE(' Number of characters written to destination: ' ||(dst_offset-1) );
  IF (warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR)
  THEN
    DBMS_OUTPUT.PUT_LINE('Warning: Inconvertible character');
  END IF;
  DBMS_LOB.FILECLOSEALL() ;
END;
/

CREATE OR REPLACE PROCEDURE loadCLOB2_proc (dst_loc1 IN OUT NCLOB,dst_loc2 IN OUT NCLOB) IS
  src_loc     BFILE := BFILENAME('MEDIA_DIR','monitor_3060.txt');
  amt         NUMBER := 100;
  src_offset  NUMBER := 1;
  dst_offset  NUMBER := 1;
  src_osin    NUMBER;
  cs_id       NUMBER := NLS_CHARSET_ID('JA16TSTSET'); /* 998 */
  lang_ctx    NUMBER := dbms_lob.default_lang_ctx;
  warning     NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE ------------');
  DBMS_LOB.FILEOPEN(src_loc, DBMS_LOB.FILE_READONLY);
  DBMS_OUTPUT.PUT_LINE(' BFILE csid is ' || cs_id) ;

  /* Load the first 1KB of the BFILE into dst_loc1 */

  DBMS_OUTPUT.PUT_LINE(' ----------------------------' ) ;
  DBMS_OUTPUT.PUT_LINE('   First load  ' ) ;
  DBMS_OUTPUT.PUT_LINE(' ----------------------------' ) ;

  DBMS_LOB.LOADCLOBFROMFILE(dst_loc1, src_loc, amt, dst_offset, src_offset,
      cs_id, lang_ctx, warning);

  /* the number bytes read may or may not be 1k */

  DBMS_OUTPUT.PUT_LINE(' Amount specified ' || amt ) ;
  DBMS_OUTPUT.PUT_LINE(' Number of bytes read from source: ' ||
      (src_offset-1));
  DBMS_OUTPUT.PUT_LINE(' Number of characters written to destination: ' ||
      (dst_offset-1) );
  if (warning = dbms_lob.warn_inconvertible_char)
  then
    DBMS_OUTPUT.PUT_LINE('Warning: Inconvertible character');
  end if;

  /* load the next 1KB of the BFILE into the dst_loc2 */

  DBMS_OUTPUT.PUT_LINE(' ----------------------------' ) ;
  DBMS_OUTPUT.PUT_LINE('   Second load  ' ) ;
  DBMS_OUTPUT.PUT_LINE(' ----------------------------' ) ;

  /* Notice we are using the src_offset and lang_ctx returned from the previous
   * load. We do not use value 1001 as the src_offset here because sometimes the
   * actual amount read may not be the same as the amount specified.
   */

  src_osin := src_offset;
  dst_offset := 1;
  DBMS_LOB.LOADCLOBFROMFILE(dst_loc2, src_loc, amt, dst_offset, src_offset,
      cs_id, lang_ctx, warning);
  DBMS_OUTPUT.PUT_LINE(' Number of bytes read from source: ' ||
      (src_offset-src_osin) );
  DBMS_OUTPUT.PUT_LINE(' Number of characters written to destination: ' ||
      (dst_offset-1) );
  if (warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR)
  then
    DBMS_OUTPUT.PUT_LINE('Warning: Inconvertible character');
  end if;
  DBMS_LOB.FILECLOSEALL() ;

END;
/