4.3 BFILE APIs

This section discusses the different operations supported through BFILEs.

Once you initialize a BFILE variable either by using the BFILENAME function or an equivalent API, or by using a SELECT operation on a BFILE column, you can perform read operations on the BFILE using APIs such as DBMS_LOB. Note that BFILE is a read-only data type. So, you cannot update or delete the operating system files, accessed using BFILEs, through the BFILE APIs.

The operations performed on BFILEs are divided into following categories:

Table 4-1 Operations on BFILEs

Category	Operation	Example function /procedure in DBMS_LOB package
Sanity Checking	Check if the BFILE exists on the server	FILEEXISITS
	Get the DIRECTORY object name and file name	FILEGETNAME
	Set the name of a BFILE in a locator without checking if the directory or file exists	BFILENAME
Open / Close	Open a file	OPEN
	Check if the file was opened using the input BFILE locators	ISOPEN
	Close the file	CLOSE
	Close all previously opened files	FILECLOSEALL
Read Operations	Get the length of the BFILE	GETLENGTH
	Read data from the BFILE starting at the specified offset	READ
	Return part of the BFILE value starting at the specified offset using SUBSTR	SUBSTR
	Return the matching position of a pattern in a BFILE using INSTR	INSTR
Operations involving multiple locators	Assign BFILE locator src to BFILE locator dst	dst := src
	Load BFILE data into a LOB	LOADCLOBFROMFILE, LOADBLOBFROMFILE
	Compare all or part of the value of two BFILEs	COMPARE

• Sanity Checking
  Sanity Checking functions on BFILEs enable you to retrieve information about the BFILEs.
• Opening and Closing a BFILE
  You must OPEN a BFILE before performing any operations on it, and CLOSE it before you terminate your program.
• Reading from a BFILE
  You can perform many different read operations on the BFILE data, including reading its length, reading part of the data, or reading the whole data.
• Working with Multiple BFILE Locators
  Some BFILE operations accept two locators, at least one of which is a BFILE locator. For the assignment and the comparison operations involving BFILES, both the locators must be of BFILE type.

4.3.1 Sanity Checking

Sanity Checking functions on BFILEs enable you to retrieve information about the BFILEs.

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.

4.3.2 Opening and Closing a BFILE

You must OPEN a BFILE before performing any operations on it, and CLOSE it before you terminate your program.

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().

4.3.3 Reading from a BFILE

You can perform many different read operations on the BFILE data, including reading its length, reading part of the data, or reading the whole data.

When reading from a large BFILE, you can use the streaming read mode in JDBC or OCI. In JDBC, you can achieve this by using the getBinaryStream() method. In OCI, you can achieve it in the way as described in the following section.

Streaming Read in OCI

The most efficient way to read large amounts of BFILE data is by using the OCILobRead2() function with the streaming mechanism enabled, and using polling or callback. To do so, specify the starting point of the read using the offset parameter as follows:

ub8  char_amt =  0;
ub8  byte_amt =  0;
ub4  offset = 1000;

OCILobRead2(svchp, errhp, locp, &byte_amt, &char_amt, offset, bufp, bufl,
            OCI_ONE_PIECE, 0, 0, 0, 0);

When using polling mode, be sure to look at the value of the byte_amt parameter after each OCILobRead2() call to see how many bytes were read into the buffer, because the buffer may not be entirely full.

When using callbacks, the lenp parameter, which is input to the callback, indicates how many bytes are filled in the buffer. Be sure to check the lenp parameter during your callback processing because the entire buffer may not be filled with data.

Amount Parameter

• When calling the DBMS_LOB.READ API, the size of the amount parameter can be larger than the size of the data. However, this parameter should be less than or equal to the size of the buffer. In PL/SQL, the buffer size is limited to 32K.
• When calling the OCILobRead2() function, you can pass a value of UB8MAXVAL for the byte_amt parameter to read to the end of the BFILE.

4.3.4 Working with Multiple BFILE Locators

Some BFILE operations accept two locators, at least one of which is a BFILE locator. For the assignment and the comparison operations involving BFILES, both the locators must be of BFILE type.

Loading a LOB with BFILE data involves special considerations that we will discuss in the following sections:

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:

Table 4-2 Maximum LOB Size for Load from File Operations

Environment	Function	To pass maximum LOB size, get value of:
DBMS_LOB	DBMS_LOB.LOADBLOBFROMFILE	DBMS_LOB.LOBMAXSIZE
DBMS_LOB	DBMS_LOB.LOADCLOBFROMFILE	DBMS_LOB.LOBMAXSIZE
OCI	OCILobLoadFromFile2()	UB8MAXVAL
OCI	OCILobLoadFromFile()(For LOBs less than 4 gigabytes in size.)	UB4MAXVAL

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.

The following example illustrates:

• How to use default csid(0).
• How to load the entire file without calling getlength for the BFILE.
• How to find out the actual amount loaded using return offsets.

This example assumes that ad_source is a BFILE in UTF8 character set format and the database character set is UTF8.

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;
/

The following example illustrates:

• How to get the character set ID from the character set name using the NLS_CHARSET_ID function.
• How to load a stream of data from a single BFILE into different LOBs using the returned offset value and the language context lang_ctx.
• How to read a warning message

This example assumes that ad_file_ext_01 is a BFILE in JA16TSTSET format and the database national character set is AL16UTF16.

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;
/