BFILE Locators

4.2 BFILE Locators

For BFILEs, the value is stored in a server-side operating system file, in other words, BFILEs are external to the database. The BFILE locator that refers to the file is stored in the database row.

To associate an operating system file to a BFILE, first create a DIRECTORY object that is an alias for the full path name to the operating system file. Then, you can initialize an instance of BFILE type, using the BFILENAME function in SQL or PL/SQL, or OCILobFileSetName()in OCI. You can use this BFILE instance in one of the following ways:

If your need for a particular BFILE is temporary and limited within the module on which you are working, then you can assign this BFILE instance to a PL/SQL or OCI local variable of type BFILE. Subsequently, you can use the BFILE related APIs on this variable without having to associate this with a column in the database. The BFILE API operations on a temporary instance are executed on the client side, without any round-trips to the server.
You can insert a persistent reference to a BFILE in the BFILE column using an INSERT or UPDATE statement. Before using SQL to insert or update a row with a BFILE, you must initialize the BFILE variable to either NULL or a DIRECTORY object name and file name.

Note:
The OCISetAttr() function does not allow you to set a BFILE locator to NULL. To insert a NULL BFILE in OCI, you must set the bind value to NULL.

It is possible to have multiple BFILE columns in the same record or different records referring to the same file. For example, the following UPDATE statements set the BFILE column of the row with key_value = 21 in lob_table to point to the same file as the row with key_value = 22.

UPDATE lob_table SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22) WHERE
      key_value = 21;

See Also:

Loading BFILEs with SQL*Loader

BFILEs in Objects

If you are using BFILEs in objects, you must first set the BFILE value, and then flush the object to the database. So, you must first call the OCIObjectNew() function, followed by the OCILobFileSetName() function and the OCIObjectFlush() function respectively.

BFILEs in Shared Server (Multithreaded Server) Mode

The database does not support session migration for BFILE data types in shared server (multithreaded server) mode. This implies that in shared server sessions, BFILE operations are bound to one shared server, they cannot migrate from one server to another, and open BFILE instances can persist beyond the end of a call to a shared server.

Examples of Creating Directory Objects and BFILE Locators

Many examples in the following sections use the print_media table. Following is the structure of the table:

Figure 4-1 print_media table

Example 4-1 Inserting BFILEs in SQL and PL/SQL

conn system/manager

 -- The DBA creates DIRECTORY object and grants READ to the user
create or replace directory MYDIR as '/your/directory/path/here';
GRANT read ON DIRECTORY MYDIR TO pm; 

conn pm/pm

 -- Use BFILENAME to create a BFILE locator for INSERT
INSERT INTO print_media
(product_id, ad_id, ad_composite, ad_sourcetext, ad_graphic)
VALUES
(1, 1, empty_blob(), empty_clob(), BFILENAME('MYDIR','file1.txt')); 

-- After this statement, 2 rows point to the same BFILE
INSERT INTO print_media 
(product_id, ad_id, ad_composite, ad_sourcetext, ad_graphic)  
    select 2, ad_id, ad_composite, ad_sourcetext, ad_graphic from print_media; 

-- Update the 2nd row to point to a different file
UPDATE print_media SET ad_graphic = BFILENAME('MYDIR','file2.txt')  WHERE product_id =2; 

-- Insert a 3rd row with invalid file name
INSERT INTO print_media
(product_id, ad_id, ad_composite, ad_sourcetext, ad_graphic)
VALUES
(3, 3, empty_blob(), empty_clob(), BFILENAME('MYDIR','file_does_not_exist.txt')); 

-- Insert a NULL for BFILE
INSERT INTO print_media
(product_id, ad_id, ad_composite, ad_sourcetext, ad_graphic)
VALUES
(4, 4, empty_blob(), empty_clob(), NULL); 

-- Inserting in PLSQL using a BFILE variable
DECLARE  
    f BFILE;
BEGIN  
    f := BFILENAME('MYDIR','file5.txt');  
    INSERT INTO print_media (product_id, ad_id, ad_composite, ad_sourcetext, ad_graphic)  
    VALUES (5, 5, NULL, NULL, f);
END;
/ 
SELECT product_id, ad_id, ad_graphic FROM print_media ORDER BY 1,2;

Example 4-2 Inserting BFILEs in OCI

STATIC TEXT *insstmt = "INSERT INTO print_media (product_id, ad_id, ad_graphic) VALUES (:1, :1, :2)";
sword insert_bfile()
{
  OCILobLocator *f = (OCILobLocator *)0;
 
  OCIStmt       *stmthp;
  OCIBind       *bndp1  = (OCIBind *)   0;
  OCIBind       *bndp2  = (OCIBind *)   0;
 
  ub4            id;
 
  CHECK_ERROR (OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
                              OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
 
  /*************** Allocate descriptor ***********************/
  CHECK_ERROR (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &f,
                                  (ub4)OCI_DTYPE_FILE, (size_t) 0,
                                  (dvoid **) 0));
 
 
  /********** Execute insstmt to insert f ********************/
  id = 6;
  CHECK_ERROR (OCILobFileSetName(envhp, errhp, &f,
                                 (text*)"MYDIR", sizeof("MYDIR") -1,
                                 (text*)"file6.txt",
                                 sizeof("file6.txt") -1));
 
  CHECK_ERROR (OCIStmtPrepare(stmthp, errhp, insstmt,
                              (ub4) strlen((char *) insstmt),
                              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  CHECK_ERROR (OCIBindByPos(stmthp, &bndp1, errhp, (ub4) 1, (dvoid *) &id,
                              (sb4) sizeof(id), SQLT_INT, (dvoid *) 0, (ub2 *) 0,
                              (ub2 *)0, (ub4) 0, (ub4*) 0, (ub4) OCI_DEFAULT));
 
  CHECK_ERROR (OCIBindByPos(stmthp, &bndp2, errhp, (ub4) 2, (dvoid *) &f4,
                              (sb4) -1, SQLT_BFILE, (dvoid *) 0, (ub2 *) 0,
                              (ub2 *)0, (ub4) 0, (ub4*) 0, (ub4) OCI_DEFAULT));
 
  CHECK_ERROR (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                              (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
                              OCI_DEFAULT));
 
  /********** Execute insstmt to insert NULL ********************/
  id = 7;
  CHECK_ERROR (OCIStmtPrepare(stmthp, errhp, insstmt,
                              (ub4) strlen((char *) insstmt),
                              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  CHECK_ERROR (OCIBindByPos(stmthp, &bndp1, errhp, (ub4) 1, (dvoid *) &id,
                              (sb4) sizeof(id), SQLT_INT, (dvoid *) 0, (ub2 *) 0,
                              (ub2 *)0, (ub4) 0, (ub4*) 0, (ub4) OCI_DEFAULT));
 
  CHECK_ERROR (OCIBindByPos(stmthp, &bndp2, errhp, (ub4) 2, (dvoid *) NULL,
                              (sb4) -1, SQLT_BFILE, (dvoid *) 0, (ub2 *) 0,
                              (ub2 *)0, (ub4) 0, (ub4*) 0, (ub4) OCI_DEFAULT));
 
  CHECK_ERROR (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                              (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
                              OCI_DEFAULT));
 
}