12
External LOBs (BFILEs)

Use Case Model

In this chapter we discuss each operation on External LOBs (such as "Reading Data from a BFILE") in terms of a use case. Table 12-1, "Use Case Model: External LOBs (BFILEs)" lists all the use cases.

Individual Use Cases

Each External LOB (BFILE) use case is described as follows:

• Use case figure. A figure that depicts the use case. See Appendix A, "Unified Modeling Language Diagrams" for help in understanding these UML based diagrams.
• Purpose. The purpose of this use case with regards to LOBs.
• Usage Notes. Guidelines to assist your implementation of the LOB operation.
• Syntax. Pointers to the syntax in different programmatic environments that underlies the LOBs related activity for the use case.
• Scenario. Describes the implementation of the use case in terms of the sample schema used in the examples. For further details on sample schemas, refer to Oracle9i Sample Schemas.
• Examples. How to apply each use case based on the sample schema used.

Use Case Model: External LOBs (BFILEs)

Table 12-1, "Use Case Model: External LOBs (BFILEs)" indicates with + where examples are provided for specific use cases and in which programmatic environment (see Chapter 3, "LOB Support in Different Programmatic Environments" for a complete discussion and references to related manuals).

Programmatic environment abbreviations used in the following table, are as follows:

• P -- PL/SQL using the DBMS_LOB Package
• O -- C using OCI (Oracle Call Interface)
• B -- COBOL using Pro*COBOL precompiler
• C -- C/C++ using Pro*C/C++ precompiler
• V -- Visual Basic using OO4O (Oracle Objects for OLE)
• J -- Java using JDBC (Java Database Connectivity)
• S -- SQL

Table 12-1 Use Case Model: External LOBs (BFILEs)  

Accessing External LOBs (BFILEs)

To access external LOBs (BFILEs) use one of the following interfaces:

• Precompilers, such as Pro*C/C++ and Pro*COBOL
• OCI (Oracle Call Interface)
• PL/SQL (DBMS_LOB package)
• Java (JDBC)
• Oracle Objects for OLE (OO4O)

  See Also: Chapter 3, "LOB Support in Different Programmatic Environments" for information about the six interfaces used to access external LOBs (BFILEs) and their available functions.

Directory Object

The DIRECTORY object facilitates administering access and usage of BFILEs in an Oracle Server (see CREATE DIRECTORY in Oracle9i SQL Reference). A DIRECTORY specifies a logical alias name for a physical directory on the server's file system under which the file to be accessed is located. You can access a file in the server's file system only if granted the required access privilege on DIRECTORY object.

Initializing a BFILE Locator

DIRECTORY object also provides the flexibility to manage the locations of the files, instead of forcing you to hardcode the absolute path names of physical files in your applications. A DIRECTORY alias is used in conjunction with the BFILENAME() function, in SQL and PL/SQL, or the OCILobFileSetName(), in OCI for initializing a BFILE locator.

How to Associate Operating System Files with Database Records

To associate an operating system (OS) file to a BFILE, first create a DIRECTORY object which is an alias for the full path name to the operating system file.

To associate existing operating system files with relevant database records of a particular table use Oracle SQL DML (Data Manipulation Language). For example:

• Use INSERT to initialize a BFILE column to point to an existing file in the server's file system
• Use UPDATE to change the reference target of the BFILE
• Initialize a BFILE to NULL and then update it later to refer to an operating system file using the BFILENAME() function.
• OCI users can also use OCILobFileSetName() to initialize a BFILE locator variable that is then used in the VALUES clause of an INSERT statement.

Examples

The following statements associate the files Image1.gif and image2.gif with records having key_value of 21 and 22 respectively. 'IMG' is a DIRECTORY object that represents the physical directory under which Image1.gif and image2.gif are stored.

CREATE TABLE Lob_table (
   Key_value NUMBER NOT NULL,
   F_lob BFILE)

  INSERT INTO Lob_table VALUES
      (21,  BFILENAME('IMG', 'Image1.gif'));
    INSERT INTO Lob_table VALUES
      (22, BFILENAME('IMG', 'image2.gif'));

The following UPDATE statement changes the target file to image3.gif for the row with key_value 22.

   UPDATE Lob_table SET f_lob = BFILENAME('IMG', 'image3.gif') 
       WHERE Key_value = 22;

BFILENAME() and Initialization

BFILENAME() is a built-in function that is used to initialize the BFILE column to point to the external file.

Once physical files are associated with records using SQL DML, subsequent read operations on the BFILE can be performed using PL/SQL DBMS_LOB package and OCI. However, these files are read-only when accessed through BFILES, and so they cannot be updated or deleted through BFILES.

As a consequence of the reference-based semantics for BFILEs, 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;

Think of BFILENAME() in terms of initialization -- it can initialize the value for the following:

• BFILE column
• BFILE (automatic) variable declared inside a PL/SQL module

Advantages.

This has the following advantages:

• If your need for a particular BFILE is temporary, and scoped just within the module on which you are working, you can utilize the BFILE related APIs on the variable without ever having to associate this with a column in the database.
• Since you are not forced to create a BFILE column in a server side table, initialize this column value, and then retrieve this column value using a SELECT, you save a round-trip to the server.

For more information, refer to the example given for DBMS_LOB.LOADFROMFILE (see "Loading a LOB with BFILE Data").

The OCI counterpart for BFILENAME() is OCILobFileSetName(), which can be used in a similar fashion.

DIRECTORY Name Specification

The naming convention for DIRECTORY objects is the same as that for tables and indexes. That is, normal identifiers are interpreted in uppercase, but delimited identifiers are interpreted as is. For example, the following statement:

CREATE DIRECTORY scott_dir AS '/usr/home/scott';

creates a directory object whose name is 'SCOTT_DIR' (in uppercase). But if a delimited identifier is used for the DIRECTORY name, as shown in the following statement

CREATE DIRECTORY "Mary_Dir" AS '/usr/home/mary';

the directory object's name is 'Mary_Dir'. Use 'SCOTT_DIR' and 'Mary_Dir' when calling BFILENAME(). For example:

BFILENAME('SCOTT_DIR', 'afile')
BFILENAME('Mary_Dir', 'afile')

On Windows Platforms

On Windows NT, for example, the directory names are case-insensitive. Therefore the following two statements refer to the same directory:

CREATE DIRECTORY "big_cap_dir" AS "g:\data\source";

CREATE DIRECTORY "small_cap_dir" AS "G:\DATA\SOURCE";

BFILE Security

This section introduces the BFILE security model and associated SQL statements. The main SQL statements associated with BFILE security are:

• SQL DDL: CREATE and REPLACE or ALTER a DIRECTORY object
• SQL DML: GRANT and REVOKE the READ system and object privileges on DIRECTORY objects

Ownership and Privileges

The DIRECTORY object is a system owned object. For more information on system owned objects, see Oracle9i SQL Reference. Oracle9i supports two new system privileges, which are granted only to DBA:

• CREATE ANY DIRECTORY -- for creating or altering the directory object creation
• DROP ANY DIRECTORY -- for deleting the directory object

Read Permission on Directory Object

READ permission on the DIRECTORY object allows you to read files located under that directory. The creator of the DIRECTORY object automatically earns the READ privilege.

If you have been granted the READ permission with GRANT option, you may in turn grant this privilege to other users/roles and add them to your privilege domains.

The physical directory that it represents may or may not have the corresponding operating system privileges (read in this case) for the Oracle Server process.

It is the DBA's responsibility to ensure the following:

• That the physical directory exists
• Read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it
• The directory remains available, and read permission remains enabled, for the entire duration of file access by database users

The privilege just implies that as far as the Oracle Server is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL DBMS_LOB package and OCI APIs at the time of the actual file operations.

SQL DDL for BFILE Security

Refer to the Oracle9i SQL Reference for information about the following SQL DDL statements that create, replace, and drop directory objects:

• CREATE DIRECTORY
• DROP DIRECTORY

SQL DML for BFILE Security

Refer to the Oracle9i SQL Reference for information about the following SQL DML statements that provide security for BFILEs:

• GRANT (system privilege)
• GRANT (object privilege)
• REVOKE (system privilege)
• REVOKE (object privilege)
• AUDIT (new statements)
• AUDIT (schema objects)

Catalog Views on Directories

Catalog views are provided for DIRECTORY objects to enable users to view object names and corresponding paths and privileges. Supported views are:

• ALL_DIRECTORIES (OWNER, DIRECTORY_NAME, DIRECTORY_PATH)

  This view describes all directories accessible to the user.

• DBA_DIRECTORIES(OWNER, DIRECTORY_NAME, DIRECTORY_PATH)

  This view describes all directories specified for the entire database.

Guidelines for DIRECTORY Usage

The main goal of the DIRECTORY feature is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server file system. But to realize this goal, it is very important that the DBA follow these guidelines when using DIRECTORY objects:

• Do Not Map DIRECTORY to Directories of Data Files, And So On. A DIRECTORY should not be mapped to physical directories that contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could corrupt the database or the server operating system.
• Only the DBA Should Have System Privileges. The system privileges such as CREATE ANY DIRECTORY (granted to the DBA initially) should be used carefully and not granted to other users indiscriminately. In most cases, only the database administrator should have these privileges.
• Use Caution When Granting DIRECTORY Object Privilege. Privileges on DIRECTORY objects should be granted to different users carefully. The same holds for the use of the WITH GRANT OPTION clause when granting privileges to users.
• Do not Drop or Replace DIRECTORY Objects When Database is in Operation. DIRECTORY objects should not be arbitrarily dropped or replaced when the database is in operation. If this were to happen, operations from all sessions on all files associated with this directory object will fail. Further, if a DROP or REPLACE command is executed before these files could be successfully closed, the references to these files will be lost in the programs, and system resources associated with these files will not be released until the session(s) is shut down.

  The only recourse left to PL/SQL users, for example, will be to either execute a program block that calls DBMS_LOB.FILECLOSEALL() and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.

• Caution When Revoking User's Privilege on DIRECTORY Objects. Revoking a user's privilege on a DIRECTORY object using the REVOKE statement causes all subsequent operations on dependent files from the user's session to fail. Either you must re-acquire the privileges to close the file, or execute a FILECLOSEALL() in the session and restart the file operations.

In general, using DIRECTORY objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have READ privileges for the Oracle process.

DIRECTORY objects can be created with READ privileges that map to these physical directories, and specific database users granted access to these directories.

BFILEs in Shared Server (Multi-Threaded Server -- MTS) Mode

Oracle9i does not support session migration for BFILEs in Shared Server (Multi-Threaded Server -- MTS) mode. This implies that operations on open BFILEs can persist beyond the end of a call to a shared server.

In shared server sessions, BFILE operations will be bound to one shared server, they cannot migrate from one server to another. This restriction will be removed in a forthcoming release.

External LOB (BFILE) Locators

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

When Two Rows in a BFILE Table Refer to the Same File

If a BFILE locator variable that is used in a DBMS_LOB.FILEOPEN() (for example L1) is assigned to another locator variable, (for example L2), both L1 and L2 point to the same file. This means that two rows in a table with a BFILE column can refer to the same file or to two distinct files -- a fact that the canny developer might turn to advantage, but which could well be a pitfall for the unwary.

BFILE Locator Variable

A BFILE locator variable behaves like any other automatic variable. With respect to file operations, it behaves like a file descriptor available as part of the standard I/O 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 this file must be done from within the same program block using this locator or local copies of this locator.

Guidelines

• Open and Close a File From Same Program Block at Same Nesting Level. The BFILE locator variable can be used, just as any scalar, 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.
• Set the BFILE Value Before Flushing Object to Database. If the object contains a BFILE, you must set the BFILE value before flushing the object to the database, thereby inserting a new row. In other words, you must call OCILobFileSetName() after OCIObjectNew() and before OCIObjectFlush().
• Indicate Directory Alias and Filename Before INSERT or UPDATE of BFILE. It is an error to INSERT or UPDATE a BFILE without indicating a directory alias and filename.

  This rule also applies to users using an OCI bind variable for a BFILE in an insert/update statement. The OCI bind variable must be initialized with a directory alias and filename before issuing the insert or update statement.

• Initialize BFILE Before INSERT or UPDATE

  Note: OCISetAttr() does not allow the user to set a BFILE locator to NULL.

General Rule

Before using SQL to insert or update a row with a BFILE, the user must initialize the BFILE to one of the following:

• NULL (not possible if using an OCI bind variable)
• A directory alias and filename

Creating a Table Containing One or More BFILE Columns

Figure 12-1 Use Case Diagram: Creating a Table Containing One or More BFILE Columns

Purpose

This procedure describes how to create a table containing one or more BFILE columns.

Usage Notes

SQL Data Definition Language (DDL) is used to define BFILE columns in a table and BFILE attributes in an object type.

Syntax

Use the following syntax references:

• SQL (Oracle9i SQL Reference), Chapter 7, "SQL Statements" -- CREATE TABLE

Scenario

The heart of our hypothetical application is the table Print_media. The varied types that make up the columns of this table make it possible to collect together the many different kinds of elements used in printed media.

Examples

The following example is provided in SQL and applies to all programmatic environments:

• SQL: Creating a Table Containing One or More BFILE Columns

SQL: Creating a Table Containing One or More BFILE Columns

You may need to set up the following data structures for certain examples in this chapter to work:

/*  Setup script for creating Print_media, Online_media and associated 
structures */

Rem  The HR and OE Schema need to be created before you create the PM Schema 
Rem  For a detailed listing of the pm_drop.sql and pm_main.sql scripts see
Rem  the manual, Oracle9i Sample Schemas. 

DROP USER pm CASCADE;
DROP DIRECTORY ADPHOTO_DIR;
DROP DIRECTORY ADCOMPOSITE_DIR;
DROP DIRECTORY ADGRAPHIC_DIR;
DROP INDEX onlinemedia CASCADE CONSTRAINTS;
DROP INDEX printmedia CASCADE CONSTRAINTS;
DROP TABLE online_media CASCADE CONSTRAINTS;
DROP TABLE print_media CASCADE CONSTRAINTS;
DROP TYPE textdoc_typ;
DROP TYPE textdoc_tab;
DROP TYPE adheader_typ;
DROP TABLE adheader_typ;
CREATE USER pm;
GRANT CONNECT, RESOURCE to pm;

CREATE DIRECTORY ADPHOTO_DIR AS '/tmp/';
CREATE DIRECTORY ADCOMPOSITE_DIR AS '/tmp/';
CREATE DIRECTORY ADGRAPHIC_DIR AS '/tmp/';
CREATE DIRECTORY media_dir AS '/tmp/';
GRANT READ ON DIRECTORY ADPHOTO_DIR to pm;
GRANT READ ON DIRECTORY ADCOMPOSITE_DIR to pm;
GRANT READ ON DIRECTORY ADGRAPHIC_DIR to pm;
GRANT READ ON DIRECTORY media_dir to pm;

CONNECT pm/pm  (or &pass);
COMMIT;

CREATE TABLE a_table (blob_col BLOB); 

CREATE TYPE adheader_typ AS OBJECT ( 
   header_name    VARCHAR2(256),  
   creation_date  DATE,  
   header_text    VARCHAR(1024), 
   logo           BLOB );

CREATE TYPE textdoc_typ AS OBJECT ( 
   document_typ   VARCHAR2(32), 
   formatted_doc  BLOB);

CREATE TYPE Textdoc_ntab AS TABLE of textdoc_typ;

CREATE TABLE Adheader_tab of adheader_typ (
   header_name    VARCHAR2(256) CONSTRAINT hname CHECK (hname IS NOT NULL),  
   creation_date  DATE DEFAULT NULL,  
   logo           DEFAULT EMPTY_BLOB() 
   );
   
CREATE TABLE online_media
( product_id  NUMBER(6),
product_photo ORDSYS.ORDImage,
product_photo_signature ORDSYS.ORDImageSignature,
product_thumbnail ORDSYS.ORDImage, 
product_video ORDSYS.ORDVideo,
product_audio ORDSYS.ORDAudio, 
product_text CLOB,
product_testimonials ORDSYS.ORDDoc);

CREATE UNIQUE INDEX onlinemedia_pk
  ON online_media (product_id);
  
ALTER TABLE online_media
ADD (CONSTRAINT onlinemedia_pk
PRIMARY KEY (product_id), CONSTRAINT loc_c_id_fk
FOREIGN KEY (product_id)  REFERENCES oe.product_information(product_id)
);

CREATE TABLE print_media
(product_id NUMBER(6), 
ad_id NUMBER(6),
ad_composite BLOB,
ad_sourcetext CLOB, 
ad_finaltext CLOB,
ad_fktextn NCLOB,
ad_testdocs_ntab textdoc_tab,
ad_photo BLOB, 
ad_graphic BFILE,
ad_header adheader_typ,
press_release LONG) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;

CREATE UNIQUE INDEX printmedia_pk
  ON print_media (product_id, ad_id);

ALTER TABLE print_media
ADD (CONSTRAINT printmedia_pk
PRIMARY KEY (product_id, ad_id), 
CONSTRAINT printmedia_fk FOREIGN KEY (product_id) 
REFERENCES oe.product_information(product_id)
);

Creating a Table of an Object Type with a BFILE Attribute

Figure 12-2 Use Case Diagram: Creating a Table Containing a BFILE

Purpose

This procedure describes how to create a table of an object type with a BFILE attribute.

Usage Notes

As shown in the diagram, you must create the object type that contains the BFILE attributes before you can proceed to create a table that makes use of that object type. SQL Data Definition Language (DDL) is used to define BFILE columns in a table and BFILE attributes in an object type.

Syntax

Use the following syntax references:

• SQL (Oracle9i SQL Reference), Chapter 7, "SQL Statements" -- CREATE TABLE, CREATE TYPE

Note that NCLOBs cannot be attributes of an object type.

Scenario

Our example application contains examples of two different ways in which object types can contain BFILEs:

• Multimedia_tab contains a column Voiced_ref that references row objects in the table VoiceOver_tab which is based on the type Voiced_typ. This type contains two kinds of LOBs -- a CLOB to store the script that's read by the actor, and a BFILE to hold the audio recording.
• Multimedia_tab contains column Map_obj that contains column objects of the type Map_typ. This type utilizes the BFILE datatype for storing aerial pictures of the region.

Examples

The following example is provided in SQL and applies to all programmatic environments:

• SQL: Creating a Table of an Object Type with a BFILE Attribute

SQL: Creating a Table of an Object Type with a BFILE Attribute

/* Create type Voiced_typ as a basis for tables that can contain recordings of 
   voice-over readings using SQL DDL: */
CREATE TYPE Voiced_typ AS OBJECT
(  Originator      VARCHAR2(30), 
   Script          CLOB, 
   Actor           VARCHAR2(30),  
   Take            NUMBER,
   Recording       BFILE 
);

/* Create table Voiceover_tab Using SQL DDL: */
CREATE TABLE Voiceover_tab OF Voiced_typ
(   Script DEFAULT EMPTY_CLOB(), 
    CONSTRAINT Take CHECK (Take IS NOT NULL),
    Recording DEFAULT NULL
);

/* Create Type Map_typ using SQL DDL as a basis for the table that will contain 
   the column object: */
CREATE TYPE Map_typ AS OBJECT
(  Region          VARCHAR2(30), 
   NW              NUMBER,
   NE              NUMBER,
   SW              NUMBER,
   SE              NUMBER,
   Drawing         BLOB,
   Aerial          BFILE
);

/* Create support table MapLib_tab as an archive of maps using SQL DDL: */
CREATE TABLE Map_tab of MapLib_typ;

Creating a Table with a Nested Table Containing a BFILE

Figure 12-3 Use Case Diagram: Creating a Table with a Nested Table Containing a BFILE

Purpose

This procedure describes how to create a table with nested table containing a BFILE.

Usage Notes

As shown in the use case diagram, you must create the object type that contains BFILE attributes before you create a nested table that uses that object type. SQL Data Definition Language (DDL) is used to define BFILE columns in a table and BFILE attributes in an object type.

Syntax

Use the following syntax references:

• SQL (Oracle9i SQL Reference), Chapter 7, "SQL Statements" -- CREATE TABLE, CREATE TYPE

Scenario

In our example, Print_media table contains a nested table ad_textdoc_ntab that includes type textdoc_typ. This type makes use of two LOB datatypes -- a BFILE for graphic images of products, and a BLOB for formatted documents.

We have already described how to create a table with BFILE columns (see "Creating a Table Containing One or More BFILE Columns"), so here we only describe the SQL syntax for creating the underlying object type.

Examples

The following example is provided in SQL and applies to all programmatic environments:

• SQL: Creating a Table with a Nested Table Containing a BFILE

SQL: Creating a Table with a Nested Table Containing a BFILE

Because you use SQL DDL directly to create a table, the DBMS_LOB package is not relevant.

CREATE TYPE textdoc_typ AS OBJECT ( 
   document_typ   VARCHAR2(32), 
   formatted_doc  BLOB);

CREATE TYPE Textdoc_ntab AS TABLE of textdoc_typ;

/* Embedding the nested table is accomplished when the structure
   of the containing table is defined. Using the PM sample schema, 
   this is done by adding the following clause to the end of the CREATE 
   Print_media statement: */

NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;

INSERT a Row Using BFILENAME()

Figure 12-4 Use Case Diagram: INSERT a Row Using BILENAME()

Purpose

This procedure describes how to insert a row using BFILENAME().

Usage Notes

Call BFILENAME() function as part of an INSERT to initialize a BFILE column or attribute for a particular row, by associating it with a physical file in the server's file system.

Although DIRECTORY object, represented by the directory_alias parameter to BFILENAME(), need not already be defined before BFILENAME() is called by a SQL or PL/SQL program, the DIRECTORY object and operating system file must exist by the time you actually use the BFILE locator. For example, when used as a parameter to one of the following operations:

• OCILobFileOpen()
• DBMS_LOB.FILEOPEN()
• OCILobOpen()
• DBMS_LOB.OPEN()

  Note: BFILENAME() does not validate privileges on this DIRECTORY object, or check if the physical directory that the DIRECTORY object represents actually exists. These checks are performed only during file access using the BFILE locator that was initialized by BFILENAME().

  Note: Before you insert, you must initialize the BFILE either to NULL or to a directory alias and filename.

Ways BFILENAME() is Used to Initialize BFILE Column or Locator Variable

You can use BFILENAME() in the following ways to initialize a BFILE column:

• As part of an SQL INSERT statement
• As part of an UPDATE statement

You can use BFILENAME() to initialize a BFILE locator variable in one of the programmatic interface programs, and use that locator for file operations. However, if the corresponding directory alias or filename does not exist then PL/SQL DBMS_LOB or other relevant routines that use this variable, will generate errors.

The directory_alias parameter in the BFILENAME() function must be specified taking case-sensitivity of the directory name into consideration.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. See the following syntax references for each programmatic environment:

• SQL Oracle9i SQL Reference, Chapter 7, "SQL Statements" -- INSERT
• C (OCI) Oracle Call Interface Programmer's Guide: Chapter 7, "LOB and File Operations", for usage notes. Chapter 15, "Relational Functions" -- LOB Functions
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, embedded SQL, and precompiler directives. See also Oracle9i SQL Reference, for related information on the SQL INSERT statement.
• C/C++ (Pro*C/C++) Pro*C/C++ Precompiler Programmer's Guide: "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives". See Oracle9i SQL Reference, Chapter 7, "SQL Statements" -- INSERT
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraDynaset > METHODS > AddNew
• Java (JDBC) Oracle9i JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Further extensions are available in Oracle9i SQLJ Developer's Guide and Reference: Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The following examples illustrate how to insert a row using BFILENAME().

Examples

Examples are provided in the following six programmatic environments:

• SQL: Inserting a Row by means of BFILENAME()
• C (OCI): Inserting a Row by means of BFILENAME()
• COBOL (Pro*COBOL): Inserting a Row by means of BFILENAME()
• C/C++ (Pro*C/C++): Inserting a Row by means of BFILENAME() C/C++ (Pro*C/C++): Inserting a Row by means of BFILENAME()
• Visual Basic (OO4O): Inserting a Row by means of BFILENAME() Visual Basic (OO4O): Inserting a Row by means of BFILENAME()
• Java (JDBC): Inserting a Row by means of BFILENAME()

SQL: Inserting a Row by means of BFILENAME()

/* Inserting a row using BFILENAME().  [Example script: 3945.sql]
   Note that this is the same INSERT statement as applied to internal 
   persistent LOBs but with the BFILENAME() function added to initialize
   the BFILE columns: */

INSERT INTO Print_media VALUES (3106, 13001, EMPTY_BLOB(), 
    EMPTY_CLOB(), EMPTY_CLOB(), EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), BFILENAME('AD_GRAPHIC_DIR', '3106_keyboard'), 
    NULL, "Your press release text goes here");

C (OCI): Inserting a Row by means of BFILENAME()

/* Inserting a row using BFILENAME.  [Example script: 3946.c] */

void insertUsingBfilename(svchp, stmthp, errhp)
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  text  *insstmt = 
      (text *) "INSERT INTO Print_media VALUES (3060, 11001, EMPTY_BLOB(), \
         EMPTY_CLOB(), EMPTY_CLOB(), EMPTY_CLOB(), \
        (SELECT REF(ad) FROM Textdoc_ntab ad WHERE document_typ = `PDF`), \
         EMPTY_BLOB(), BFILENAME ('ADGRAPHIC_DIR','monitor_3060_11001'), \
        (SELECT REF(adhead) FROM Adheader_typ Adhead \
           WHERE creation_date = `1-20-2001'), \
          "PRESS RELEASE \
Date of Press Release: January 11, 2001 \
Contact Information:  Shelley and Co., Oracle Corporation, 500 Oracle Parkway, \
Redwood City, CA 94065 \"
Disclaimer: This product, product name, and information is fictitious and has \
been composed to illustrate the functionality of Oracle products. \
Any similarity to existing products or product names is coincidental. \
TIGER2 3060 Monitor ..... an Exceptional Visual Experience! \
Oracle announces its return to manufacturing hardware and computer peripherals! 
\
The first model to have completed rigorous usability and stress tests is the \
TIGER2 +3060 17-Inch CRT MONITOR with its cousin the TIGER2 3060a 17-inch \ 
Flatscreen." \
)";
   
  /* Prepare the SQL statement */
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) 
                                  strlen((char *) insstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
  /* Execute the SQL statement */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
}

COBOL (Pro*COBOL): Inserting a Row by means of BFILENAME()

     * Inserting a row using BFILENAME()  [Example script: 3947.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-INSERT.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       BFILE-INSERT.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

           EXEC SQL
                INSERT INTO PRINT_MEDIA (PRODUCT_ID, AD_GRAPHIC)
                 VALUES (1, BFILENAME('ADGRAPHIC_DIR', 'KEYBOARD_310_13001'))
           END-EXEC.
           EXEC SQL
                ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Inserting a Row by means of BFILENAME()

/* Inserting a row using BFILENAME().  [Example script: 3948.pc] */

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void BFILENAMEInsert_proc()
{
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

  /* Delete any existing row: */
  EXEC SQL DELETE FROM Print_media WHERE product_id = 2056 AND ad_id = 12001;

  /* Insert a new row using the BFILENAME() function for BFILEs: */
  EXEC SQL INSERT INTO Print_media
    VALUES (2056, 12001, EMPTY_BLOB(), EMPTY_CLOB(), EMPTY_CLOB(), EMPTY_CLOB(),
       ad_textdocs(textdoc_typ(PDF, EMPTY_BLOB())), 
       EMPTY_BLOB(), 
       BFILENAME('ADGRAPHIC_DIR', 'mousepad_2056_12001'),
       NULL,
       `You Can't Beat this Mousepad for Ergonomic Value!!')
    ;
  printf("Inserted %d row\n", sqlca.sqlerrd[2]);
}

void main()
{
   char *samp = "samp/samp";
   EXEC SQL CONNECT :samp;
   BFILENAMEInsert_proc();
   EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Inserting a Row by means of BFILENAME()

'Inserting a row using BFILENAME(). [Example script: 3949.txt]

Dim OraDyn as OraDynaset, OraAdGraphic as OraBFile

Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value
OraDyn.AddNew
OraDyn.Fields("product_id").value = 3060
OraDyn.Fields("ad_sourcetext").value = Empty 'This is equivalent to EMPTY_CLOB() 
in SQL
OraDyn.Fields("fltextn").value = Empty
'Initialize BFile Data: 
OraAdGraphic.DirectoryName = "ADGRAPHIC_DIR"
OraAdGraphic.FileName = "monitor_graphic_3060_11001"
OraDyn.Fields("ad_composite").Value = Empty
OraDyn.Fields("ad_photo").Value = Empty
OraDyn.Update  
'Add the row to the table

Java (JDBC): Inserting a Row by means of BFILENAME()

// Inserting a row using BFILENAME().  [Example script: 3951.java]

import java.io.InputStream;
import java.io.OutputStream;
// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class Ex4_21
{
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
    DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp");
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
       stmt.execute("INSERT INTO Print_media "
          +"VALUES (3060, 11001, EMPTY_BLOB(), EMPTY_CLOB(), "
          +" EMPTY_CLOB(), EMPTY_CLOB(),"
          +"(SELECT REF(ad) FROM Textdoc_ntab ad"
          +"    WHERE document_typ = `PDF`),"
          +"EMPTY_BLOB(), BFILENAME ('AD_GRAPHIC','monitor_3060'), "
          +"(SELECT REF(adhead) FROM Adheader_typ Adhead"
          +"    WHERE creation_date = `1-20-2001'), "
          +"PRESS RELEASE \"
+"Date of Press Release: January 11, 2001 \"
+"Contact Information:  Any name,Oracle Corporation, 500 Oracle Parkway," 
+"Redwood City, CA 94065 \"
+"Disclaimer: This product, product name, and information is fictitious and has" 
been" +"composed to illustrate the functionality of Oracle products. \"
+"Any similarity to existing products or product names is coincidental. \"
+"TIGER2 3060 Monitor ..... an Exceptional Visual Experience! \"
+"Oracle announces its return to manufacturing hardware and computer 
peripherals! \"
+"The first model to have completed rigorous usability and stress tests is the" 
+"TIGER2 +3060 17-Inch CRT MONITOR with its cousin the TIGER2 3060a 17-inch" 
+"Flatscreen. \"
+"Its initial offering is for $150 and its suggested retail value is $299. \"
);
   // Commit the transaction: 
   conn.commit();
   stmt.close();
   conn.close();
    }
    catch (SQLException e)
    {
   e.printStackTrace();
    }
  }
}

INSERT a BFILE Row by Selecting a BFILE From Another Table

Figure 12-5 Use Case Diagram: INSERT a Row Containing a BFILE by Selecting a BFILE From Another Table (INSERT ... AS ... SELECT)

Purpose

This procedure describes how to INSERT a row containing a BFILE by selecting a BFILE from another table.

Usage Notes

With regard to LOBs, one of the advantages of utilizing an object-relational approach is that you can define a type as a common template for related tables. For instance, it makes sense that both the tables that store archival material and the working tables that use those libraries share a common structure. See the following "Scenario".

Syntax

See the following syntax reference:

• SQL (Oracle9i SQL Reference): Chapter 7, "SQL Statements" -- INSERT

Scenario

The following code fragment is based on the fact that a library table VoiceoverLib_tab is of the same type (Voiced_typ) as Voiceover_tab referenced by column Voiced_ref of Multimedia_tab table.

It inserts values from the library table into Multimedia_tab by means of a SELECT.

Examples

The example is provided in SQL and applies to all programmatic environments:

• SQL: Inserting a Row Containing a BFILE by Selecting a BFILE From Another Table

SQL: Inserting a Row Containing a BFILE by Selecting a BFILE From Another Table

 INSERT INTO Voiceover_tab 
   (SELECT * from VoiceoverLib_tab 
       WHERE Take = 12345);

Inserting a Row With BFILE by Initializing a BFILE Locator

Figure 12-6 Use Case Diagram: Inserting a Row by Initializing a BFILE Locator

Purpose

This procedure describes how to INSERT a row containing a BFILE by initializing a BFILE locator.

Usage Notes

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. See the following syntax references for each programmatic environment:

• SQL(Oracle9i SQL Reference, Chapter 7 "SQL Statements" -- INSERT
• C (OCI) Oracle Call Interface Programmer's Guide: Chapter 7, "LOB and File Operations". Chapter 15, "Relational Functions" -- LOB Functions.
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, embedded SQL, and precompiler directives. See also Oracle9i SQL Reference, for related information on the SQL INSERT statement.
• C/C++ (Pro*C/C++) Pro*C/C++ Precompiler Programmer's Guide: "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB FILE SET. See also (Oracle9i SQL Reference), Chapter 7 "SQL Statements" -- INSERT
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > DirectoryName, FileName; and > OBJECTS > OraDynaset > METHODS > Update
• Java (JDBC) Oracle9i JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Further extensions are available in Oracle9i SQLJ Developer's Guide and Reference: Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

In these examples we insert an ad_graphic from an operating system source file (ADGRAPHIC_DIR).

Examples

Examples in the following programmatic environments are provided:

• PL/SQL: Inserting a Row Containing a BFILE by Initializing a BFILE Locator
• C (OCI): Inserting a Row Containing a BFILE by Initializing a BFILE Locator
• COBOL (Pro*COBOL): Inserting a Row Containing a BFILE by Initializing a BFILE Locator
• C/C++ (Pro*C/C++): Inserting a Row Containing a BFILE by Initializing a BFILE Locator
• Visual Basic (OO4O): Inserting a Row Containing a BFILE by Initializing a BFILE Locator
• Java (JDBC): Inserting a Row Containing a BFILE by Initializing a BFILE Locator

PL/SQL: Inserting a Row Containing a BFILE by Initializing a BFILE Locator

/* Inserting  row containing a BFILE by initializing a BFILE locator
   [Example script: 3953.sql]  */
   
DECLARE
  /* Initialize the BFILE locator: */ 
  Lob_loc  BFILE := BFILENAME('ADGRAPHIC_DIR', 'keyboard_graphic_3106_13001');
BEGIN
    INSERT INTO Print_media 
    (product_id, ad_id, ad_graphic) VALUES (3106, 13001, Lob_loc);
    COMMIT;
END;

C (OCI): Inserting a Row Containing a BFILE by Initializing a BFILE Locator

/* Inserting a row by initializing a BFILE Locator. [Example script: 3954.c] */

void insertUsingBfileLocator(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  text  *insstmt = 
     (text *) "INSERT INTO Print_media (product_id, ad_graphic) \
         VALUES (2056, :Lob_loc)";
  OCIBind *bndhp;
  OCILobLocator *Lob_loc;
  OraText *Dir = (OraText *)"ADGRAPHIC_DIR", *Name = (OraText *)"mousepad_2056_
12001";
   
  /* Prepare the SQL statement: */
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt,  (ub4) 
                                  strlen((char *) insstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
  /* Allocate Locator resources: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, 
                            (ub4)OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0);
  checkerr (errhp, OCILobFileSetName(envhp, errhp, &Lob_loc,
                                     Dir, (ub2)strlen((char *)Dir),
                                     Name,(ub2)strlen((char *)Name)));
  checkerr (errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1,
                                (dvoid *) &Lob_loc, (sb4) 0,  SQLT_BFILE,
                                (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));
  /* Execute the SQL statement: */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
  /* Free LOB resources: */
  OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_FILE);
}

COBOL (Pro*COBOL): Inserting a Row Containing a BFILE by Initializing a BFILE Locator

     * Inserting a row containing a BFILE by initializing a BFILE
     * [Example script: 3955.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-INSERT-INIT.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
    
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP.
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       BFILE-INSERT-INIT.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the BFILE locator:
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.

      * Set up the directory and file information:
           MOVE "ADGRAPHIC_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_graphic_3106_13001" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
      * Set the directory alias and filename in locator:
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME END-EXEC.
           
           EXEC SQL
                INSERT INTO PRINT_MEDIA (PRODUCT_ID, AD_GRAPHIC)
                VALUES (3106, :SRC-BFILE)END-EXEC.
           EXEC SQL ROLLBACK WORK END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Inserting a Row Containing a BFILE by Initializing a BFILE Locator

/* Inserting a row containing a BFILE by initializing a BFILE */
/* [Example script: 3958.pc] */
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>
void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void insertBFILELocator_proc()
{
  OCIBFileLocator *Lob_loc;
  char *Dir = "ADGRAPHIC_DIR", *Name = "mousepad_graphic_2056_12001";
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the input Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  /* Set the Directory and Filename in the Allocated (Initialized) Locator: */