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: */
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  EXEC SQL INSERT INTO Print_media (Product_ID, ad_graphic) VALUES (2056, :Lob_
loc);
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Inserting a Row Containing a BFILE by Initializing a BFILE Locator

' Inserting a row containing a BFILE by initializing a BFILE.
' [Example script: 3959.txt]

Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile 
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) 
Set OraMusic = OraDyn.Fields("ad_graphic").Value
 
'Edit the first row and initiliaze the "ad_graphic" column: 
OraDyn.Edit 
OraPhoto.DirectoryName = "ADGRAPHIC_DIR" 
OraPhoto.Filename = "mousepad_graphic_2056_12001" 
OraDyn.Update

Java (JDBC): Inserting a Row Containing a BFILE by Initializing a BFILE Locator

// Inserting a row containing a BFILE by initializing a BFILE.
// [Example script: 3960.java]

// Java IO classes: 
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_26
{
  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
    {
       BFILE src_lob = null;
       ResultSet rset = null;
       OracleCallableStatement cstmt = null;
       rset = stmt.executeQuery (
          "SELECT BFILENAME('ADGRAPHIC_DIR','monitor_graphic_3060_11001') FROM 
DUAL");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);
       }

       // Prepare a CallableStatement to OPEN the LOB for READWRITE: 
       cstmt = (OracleCallableStatement) conn.prepareCall (
          "INSERT INTO Print_media (product_id, ad_graphic) VALUES (3060, ?)");
       cstmt.setBFILE(1, src_lob);
       cstmt.execute();

   //Close the statements and commit the transaction:
   stmt.close();
   cstmt.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Loading Data Into External LOB (BFILE)

Figure 12-7 Use Case Diagram: Loading Initial Data into External LOB (BFILE)

Purpose

This procedure describes how to load initial data into a BFILE and the BFILE data into a table.

Usage Notes

The BFILE datatype stores unstructured binary data in operating-system files outside the database.

A BFILE column or attribute stores a file locator that points to a server-side external file containing the data.

The SQL*Loader assumes that the necessary DIRECTORY objects (a logical alias name for a physical directory on the server's file system) have already been created.

A control file field corresponding to a BFILE column consists of column name followed by the BFILE directive.

The BFILE directive takes as arguments a DIRECTORY object name followed by a BFILE name. Both of these can be provided as string constants, or they can be dynamically sourced through some other field.

Syntax

Use the following syntax references:

• SQL*Loader (Oracle9i Database Utilities)
• Chapter 4, "Managing LOBs", Using SQL*Loader to Load LOBs

Scenario

The following two examples illustrate the loading of BFILES. In the first example only the file name is specified dynamically. In the second example, the BFILE and the DIRECTORY object are specified dynamically.

CONNECT system/manager
GRANT CREATE ANY DIRECTORY to samp; 
CONNECT samp/samp
CREATE OR REPLACE DIRECTORY adgraphic_photo as '/tmp';
CREATE OR REPLACE DIRECTORY adgraphic_dir as '/tmp';

Examples

The following examples load data into BFILES:

• Loading Data Into BFILES: File Name Only is Specified Dynamically
• Loading Data into BFILES: File Name and DIRECTORY Object Dynamically Specified

Loading Data Into BFILES: File Name Only is Specified Dynamically

Control File

LOAD DATA
INFILE sample9.dat
INTO TABLE Print_media
FIELDS TERMINATED BY ','
(product_id  INTEGER EXTERNAL(6),
 FileName    FILLER CHAR(30),
 ad_graphic  BFILE(CONSTANT "modem_graphic_2268_21001", FileName))

Data file (sample9.dat)

007, modem_2268.jpg,
008, monitor_3060.jpg,
009, keyboard_2056.jpg,

Loading Data into BFILES: File Name and DIRECTORY Object
Dynamically Specified

Control File

LOAD DATA
INFILE sample10.dat
INTO TABLE Print_media
FIELDS TERMINATED BY ','
(
 product_id INTEGER EXTERNAL(6),
 ad_graphic BFILE (DirName, FileName),
 FileName  FILLER CHAR(30),
 DirName   FILLER CHAR(30)
)

Data file (sample10.dat)

007,monitor_3060.jpg,ADGRAPHIC_PHOTO,
008,modem_2268.jpg,ADGRAPHIC_PHOTO,
009,keyboard_2056.jpg,ADGRAPHIC_DIR,

Loading a LOB with BFILE Data

Figure 12-8 Use Case Diagram: Loading a LOB with BFILE Data

Purpose

This procedure describes how to load a LOB with BFILE data.

Usage Notes

Character Set Conversion

In using OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.

BFILE to CLOB or NCLOB: Converting From Binary Data to a Character Set

When you use the DBMS_LOB.LOADFROMFILE procedure to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. No implicit translation is performed from binary data to a character set. For this reason, you should use the LOADCLOBFROMFILE procedure when loading text (see Loading a CLOB with BFILE Data).

Specify Amount Parameter to be Less than the Size of the BFILE!

• DBMS_LOB.LOADFROMFILE: You cannot specify the amount parameter to be larger than the size of the BFILE.
• OCILobLoadFromFile: You cannot specify the amount parameter to be larger than the length of the BFILE.

Syntax

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

• PL/SQL (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- LOADFROMFILE
• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes and examples. Chapter 15, "Relational Functions" -- LOB Functions, OCILobLoadFromFile.
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, embedded SQL, and LOB LOAD precompiler directives.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements" "Embedded SQL Statements and Directives"-- LOB LOAD.
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBLOB.OraCLOB > METHODS > CopyFromBfile
• 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

These example procedures assume there is a directory object (ADGRAPHIC_DIR) that contains the LOB data to be loaded into the destination LOB.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Loading a LOB with BFILE Data
• C (OCI): Loading a LOB with BFILE Data
• COBOL (Pro*COBOL): Loading a LOB with BFILE Data
• C/C++ (Pro*C/C++): Loading a LOB with BFILE Data
• Visual Basic (OO4O): Loading a LOB with BFILE Data

PL/SQL (DBMS_LOB Package): Loading a LOB with BFILE Data

/* Loading a LOB with BFILE data. 
   Procedure loadLOBFromBFILE_proc is not part of DBMS_LOB package: */
   
CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('ADGRAPHIC_DIR',
                          'keyboard_graphic_3106_13001');
   Amount         INTEGER := 4000;
BEGIN
    SELECT ad_graphic INTO Dest_loc FROM Print_media 
       WHERE product_id = 3060 AND ad_id = 13001 FOR UPDATE;
    /* Opening the LOB is mandatory: */ 
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Opening the LOB is optional: */ 
   DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
   /* Closing the LOB is mandatory if you have opened it: */ 
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Src_loc);
   COMMIT;
END;

C (OCI): Loading a LOB with BFILE Data

/* Loading a LOB with BFILE data. 
   Select the lob/bfile from the Print_media table */ 
   
void selectLob(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc; 
OCIError *errhp; 
OCISvcCtx *svchp; 
OCIStmt *stmthp; 
{ 
     char  selstmt[150];
     OCIDefine *dfnhp, *dfnhp2;

     strcpy(selstmt, (char *)  "SELECT ad_photo FROM Print_media \
                  WHERE product_id=3106 AND ad_id = 13001 FOR UPDATE");

     /* Prepare the SQL select statement */ 
     checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt,  
                                     (ub4) strlen((char *) selstmt), 
                                     (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
     /* Define the column being selected */ 
     checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BLOB,
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)
       ||  OCIDefineByPos(stmthp, &dfnhp2, errhp, 2,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BLOB,
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)); 
     /* Execute the SQL select statement */ 
     checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0, 
                                     (ub4) OCI_DEFAULT)); 
} 

void loadLobFromBfile(envhp, errhp, svchp, stmthp)
OCIEnv  *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
{  

  OCILobLocator *dest_loc;
  OCILobLocator *src_loc;

  /* Allocate locators */
  (void) OCIDescriptorAlloc((dvoid *) envhp, 
                            (dvoid **) &dest_loc, (ub4)OCI_DTYPE_FILE,
                            (size_t) 0, (dvoid **) 0);

  (void) OCIDescriptorAlloc((dvoid *) envhp, 
                            (dvoid **) &src_loc, (ub4)OCI_DTYPE_FILE,
                            (size_t) 0, (dvoid **) 0);

  checkerr(errhp, OCILobFileSetName(envhp, errhp, &src_loc,
                    (text *) "ADPHOTO_DIR", (ub2) strlen("ADPHOTO_DIR"),
                    (text *) "keyboard_photo_3106_13001", 
                    (ub2) strlen(keyboard_photo_3106_13001")));

  selectLob(dest_loc, errhp, svchp, stmthp);

  checkerr(errhp, OCILobFileOpen(svchp, errhp, src_loc, 
                                 (ub1)OCI_FILE_READONLY));
  checkerr(errhp, OCILobOpen(svchp, errhp, dest_loc, (ub1)OCI_LOB_READWRITE));
  checkerr (errhp, OCILobLoadFromFile(svchp, errhp, dest_loc, src_loc, 
                                      (ub4)4000, (ub4)1, (ub4)1));
  checkerr(errhp, OCILobClose(svchp, errhp, dest_loc));
  checkerr(errhp, OCILobFileClose(svchp, errhp, src_loc));
}

COBOL (Pro*COBOL): Loading a LOB with BFILE Data

     * Loading a LOB with BFILE data. 
       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOAD-BFILE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  DEST-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.
       LOAD-BFILE.

      * Allocate and initialize the LOB locators: 
           EXEC SQL ALLOCATE :DEST-BLOB END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.

      * Set up the directory and file information: 
           MOVE "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
      * Populate the BFILE: 
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT AD_GRAPHIC INTO :SRC-BFILE
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001
                END-EXEC.
        
      * Open the source BFILE READ ONLY. 
      * Open the destination BLOB READ/WRITE: 
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :DEST-BLOB READ WRITE END-EXEC.
                
      * Load BFILE data into the BLOB: 
           EXEC SQL 
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :DEST-BLOB END-EXEC.

      * Close the LOBs: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :DEST-BLOB END-EXEC.

      * And free the LOB locators: 
       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :DEST-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE 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++): Loading a LOB with BFILE Data

/* Loading a LOB with BFILE data. */ 

#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 loadLOBFromBFILE_proc()
{
  OCIBlobLocator *Dest_loc;
  OCIBFileLocator *Src_loc;
  char *Dir = "ADGRAPHIC_DIR", *Name = "mousepad_graphic_2056_12001";
  int Amount = 4096;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();

  /* Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Src_loc;
  EXEC SQL LOB FILE SET :Src_loc DIRECTORY = :Dir, FILENAME = :Name;

  /* Initialize the BLOB Locator: */
  EXEC SQL ALLOCATE :Dest_loc;
  EXEC SQL SELECT ad_photo INTO :Dest_loc FROM Print_media
           WHERE Product_ID = 2056 AND AD_ID = 12001 FOR UPDATE;

  /* Opening the BFILE is Mandatory: */
  EXEC SQL LOB OPEN :Src_loc READ ONLY;

  /* Opening the BLOB is Optional: */
  EXEC SQL LOB OPEN :Dest_loc READ WRITE;
  EXEC SQL LOB LOAD :Amount FROM FILE :Src_loc INTO :Dest_loc;

  /* Closing LOBs and BFILEs is Mandatory if they have been OPENed: */
  EXEC SQL LOB CLOSE :Dest_loc;
  EXEC SQL LOB CLOSE :Src_loc;

  /* Release resources held by the Locators: */
  EXEC SQL FREE :Dest_loc;
  EXEC SQL FREE :Src_loc;
}

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

Visual Basic (OO4O): Loading a LOB with BFILE Data

'Loading a LOB with BFILE data 

Dim OraDyn as OraDynaset, OraDyn2 as OraDynaset, OraAdGraphic as OraBFile 
Dim OraAdPhoto as OraBlob

chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)

Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value
Set OraAdPhoto = OraDyn.Fields("ad_photo").Value

OraDyn.Edit
'Load LOB with data from BFILE: 
OraAdPhoto.CopyFromBFile (OraAdGraphic)
OraDyn.Update

Loading a BLOB with BFILE Data

Figure 12-9 Use Case Diagram: Loading a BLOB with Binary Data

Purpose

This procedure describes how to load a BLOB with binary data from a BFILE. This achieves the same outcome as LOADFROMFILE as well as returning the new offsets to the user.

Usage Notes

Use LOADBLOBFROMFILE for loading of binary data and LOADCLOBFROMFILE for text loading. Note that since this functionality does not support BFILE on the client side, the loading takes place on the server side only. The LOADCLOBFROMFILE API allows you to specify the character set id of the BFILE ensuring that the character set is properly converted from the BFILE data character set to the destination CLOB/NCLOB character set.

Syntax

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

• PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- LOADBLOBFROMFILE

Scenario

The example procedures in this section use the Print_media table of the Product Media sample schema and assume that there is an operating system source directory that contains the binary LOB data to be loaded into the target BLOB.

Examples

The example, "PL/SQL (DBMS_LOB Package): Loading an Internal Persistent BLOB with BFILE Data", illustrates LOADBLOBFROMFILE usage in the PL/SQL programmatic environment. (Other programmatic environments are not supported.)

PL/SQL (DBMS_LOB Package): Loading an Internal Persistent BLOB with BFILE Data

The following example illustrates:

• How to load an internal persistent BLOB with binary data from a BFILE.
• How to use LOADBLOBFROMFILE to load the entire file without getting its length first.
• How to use the return value of the offsets to calculate the actual amount loaded.

  DECLARE
    src_loc     BFILE := bfilename('ADVERT_DIR','display_ad_frame') ;
    dst_loc     BLOB;
    src_offset  NUMBER := 1;
    dst_offset  NUMBER := 1;
    src_osin    NUMBER;
    dst_osin    NUMBER;
    bytes_rd    NUMBER;
    bytes_wt    NUMBER; 
  BEGIN
    SELECT ad_composite INTO dst_loc FROM Print_media 
        WHERE product_id=3106 and ad_id=13001 FOR UPDATE;
  
    /* Opening the source BFILE is mandatory */
    dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
  
    /* Opening the LOB is optional */
    dbms_lob.OPEN(dst_loc, dbms_lob.lob_readwrite);
    /* Save the input source/destination offsets */
    src_osin := src_offset;
    dst_osin := dst_offset;
    /* Use LOBMAXSIZE to indicate loading the entire BFILE */
    dbms_lob.LOADBLOBFROMFILE(dst_loc,src_loc,dbms_lob.lobmaxsize,src_offset,dst_
  offset) ;
  
    /* Closing the LOB is mandatory if you have opened it */
    dbms_lob.close(dst_loc);
    dbms_lob.filecloseall();
    COMMIT;
  
    /* Use the src_offset returned to calculate the actual amount read from the 
  BFILE */
    bytes_rd := src_offset - src_osin;
    dbms_output.put_line(' Number of bytes read from the BFILE ' || bytes_rd ) ;
    /* Use the dst_offset returned to calculate the actual amount written to the 
  BLOB */
    bytes_wt := dst_offset - dst_osin;
    dbms_output.put_line(' Number of bytes written to the BLOB ' || bytes_wt ) ;
    /* If there is no exception the number of bytes read should equal to the 
  number of bytes written */
  
  END ;
  
  

Loading a CLOB with BFILE Data

Figure 12-10 Use Case Diagram: Loading a CLOB or NCLOB with Character Data

Purpose

This procedure describes how to load an internal persistent CLOB or NCLOB with character data from a BFILE.

Usage Notes

Use LOADBLOBFROMFILE for loading of binary data and LOADCLOBFROMFILE for loading of text as the latter method lets you specify the character set id of the BFILE. Note that since this functionality does not support BFILE on the client side, the loading takes place on the server side only. The LOADCLOBFROMFILE API allows you to specify the character set id of the BFILE ensuring that the character set is properly converted from the BFILE data character set to the destination CLOB/NCLOB character set.

Syntax

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

• PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- LOADCLOBFROMFILE

Scenario

The example procedures in this section use the Print_media table of the Product Media sample schema and assume that there is an operating system source directory that contains the character LOB data to be loaded into the target CLOB or NCLOB.

Examples

The example, "PL/SQL (DBMS_LOB Package): Loading a CLOB/NCLOB with BFILE Data", illustrates LOADCLOBFROMFILE usage in the PL/SQL programmatic environment. (Other programmatic environments are not supported.)

PL/SQL (DBMS_LOB Package): Loading a CLOB/NCLOB with BFILE Data

The following example illustrates:

• How to load an internal persistent CLOB or NCLOB with character data from a BFILE.
• 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 charset is UTF8.

DECLARE
  src_loc     bfile := bfilename('ADVERT_DIR','ad_source_1000') ;
  dst_loc     clob  ;
  amt         number := dbms_lob.lobmaxsize;
  src_offset  number := 1 ;
  dst_offset  number := 1 ;
  lang_ctx    number := dbms_lob.default_lang_ctx;
  warning     number;
BEGIN
  select ad_sourcetext into dst_loc from Print_media
     where product_id = 3000 and ad_id = 1000 for update ;
  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) ;

  commit;

  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 ;

Ways to Open a BFILE

Figure 12-11 Use Case Diagram: Ways to Open a BFILE

Recommendation: Use OPEN to Open BFILE

Opening a BFILE using FILEOPEN is still supported; however, using OPEN is recommended for extensibility in future releases.

Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES

A limited number of BFILEs can be open simultaneously in each session. The maximum number is specified by using the initialization parameter SESSION_MAX_OPEN_FILES.

SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session. The default value for this parameter is 10. That is, a maximum of 10 files can be opened simultaneously in each session if the default value is utilized. The database administrator can change the value of this parameter in the init.ora file. For example:

SESSION_MAX_OPEN_FILES=20

If the number of unclosed files exceeds the SESSION_MAX_OPEN_FILES value then you will not be able to open any more files in the session.

To close all open files, use the FILECLOSEALL call.

Close Files After Use

It is good practice to close files after use to keep the SESSION_MAX_OPEN_FILES value small. Choosing a larger value would entail a higher memory usage.

Opening a BFILE with FILEOPEN

Figure 12-12 Use Case Diagram: Opening a BFILE with FILEOPEN

Purpose

This procedure describes how to open a BFILE using FILEOPEN.

Usage Notes

While you can continue to use the older FILEOPEN form, we strongly recommend that you switch to using OPEN, because this facilitates future extensibility.

Syntax

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

• PL/SQL (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- FILEOPEN, FILECLOSE
• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations, for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileOpen, OCILobFileClose, OCILobFileSetName
• COBOL (Pro*COBOL): A syntax reference is not applicable in this release.
• C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.
• Visual Basic (OO4O): A syntax reference is not applicable in this release.
• 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

These examples open keyboard_photo3060 in operating system file ADPHOTO_DIR.

Examples

Examples are provided in the following four programmatic environments:

• PL/SQL: Opening a BFILE with FILEOPEN
• C (OCI): Opening a BFILE with FILEOPEN
• COBOL (Pro*COBOL): No example is provided with this release.
• C/C++ (Pro*C/C++): No example is provided with this release.
• Visual Basic (OO4O): Opening a BFILE with FILEOPEN
• Java (JDBC): Opening a BFILE with FILEOPEN

PL/SQL: Opening a BFILE with FILEOPEN

/* Opening a BFILE with FILEOPEN  [Example script: 3973.sql] */
/* Procedure openBFILE_procOne is not part of DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE openBFILE_procOne IS 
   File_loc    BFILE := BFILENAME('ADPHOTO_DIR', 'keyboard_photo3060'); 
BEGIN 
   /* Open the BFILE: */ 
   DBMS_LOB.FILEOPEN (File_loc, DBMS_LOB.FILE_READONLY);
   /* ... Do some processing. */ 
   DBMS_LOB.FILECLOSE(File_loc);
END;

C (OCI): Opening a BFILE with FILEOPEN

/* Opening a BFILE with FILEOPEN  */

void BfileOpen(envhp, errhp, svchp, stmthp)
OCIEnv  *envhp; 
OCIError *errhp; 
OCISvcCtx *svchp; 
OCIStmt *stmthp; 
{ 
     OCILobLocator *bfile_loc; 
 
     /* Allocate the locator descriptor */ 
     (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                               (ub4) OCI_DTYPE_FILE,  
                               (size_t) 0, (dvoid **) 0);
     
     /* Set the bfile locator information */ 
     checkerr(errhp, (OCILobFileSetName(envhp, errhp, &bfile_loc,  
                                        (OraText *)"ADGRAPHIC_DIR", 
                                        (ub2)strlen("ADGRAPHIC_DIR"),  
                                        (OraText *)"keyboard_graphic_3106_
13001",  
                                        (ub2)strlen("keyboard_graphic_3106_
13001")))); 
  checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, 
                                 (ub1)OCI_FILE_READONLY));
  /* ... Do some processing. */
  checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc));

  /* Free the locator descriptor */ 
  OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
} 

Visual Basic (OO4O): Opening a BFILE with FILEOPEN

Java (JDBC): Opening a BFILE with FILEOPEN

// Opening a BFILE with FILEOPEN 
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_38
{

  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
    {
       BFILE src_lob = null;
       ResultSet rset = null;

       rset = stmt.executeQuery (
       "SELECT BFILENAME('AD_GRAPHIC', 'monitor_3060') FROM DUAL");
       if (rset.next())
       {
           src_lob = ((OracleResultSet)rset).getBFILE (1);

           src_lob.openFile();
          System.out.println("The file is now open");
        }

        // Close the BFILE, statement and connection: 
        src_lob.closeFile();
        stmt.close();
        conn.commit();
        conn.close();
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
  }
}

Opening a BFILE with OPEN

Figure 12-13 Use Case Diagram: Opening a BFILE with OPEN

Purpose

This procedure describes how to open a BFILE with OPEN.

Usage Notes

Not applicable.

Syntax

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

• PL/SQL(DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- OPEN
• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobOpen, OCILobClose
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB OPEN.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB OPEN.
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > Open, and > OBJECTS > OraDynaset > METHODS > MoveFirst MoveLast MovePrevious MoveNext
• 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

These examples open an image in operating system file ADPHOTO_DIR.

Examples

Examples are provided in the following six programmatic environments:

• PL/SQL: Opening a BFILE with OPEN
• C (OCI): Opening a BFILE with OPEN
• C/C++ (Pro*C/C++): Opening a BFILE with OPEN
• COBOL (Pro*COBOL): Opening a BFILE with OPEN
• Visual Basic (OO4O) Opening a BFILE with OPEN
• Java (JDBC): Opening a BFILE with OPEN

PL/SQL: Opening a BFILE with OPEN

/* Opening a BFILE with OPEN.  */
/* Procedure openBFILE_procTwo is not part of DBMS_LOB package:  */
CREATE OR REPLACE PROCEDURE openBFILE_procTwo IS 
  File_loc    BFILE := BFILENAME('ADPHOTO_DIR', 'keyboard_photo_3060_11001'); 
BEGIN 
   /* Open the BFILE: */ 
   DBMS_LOB.OPEN (File_loc, DBMS_LOB.LOB_READONLY);
   /* ... Do some processing: */ 
   DBMS_LOB.CLOSE(File_loc);
END;

C (OCI): Opening a BFILE with OPEN

/* Opening a BFILE with OPEN.  */

void BfileFileOpen(envhp, errhp, svchp, stmthp)
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp;
{ 
      OCILobLocator *bfile_loc; 
 
     /* Allocate the locator descriptor */ 
     (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                 (ub4) OCI_DTYPE_FILE,  
                 (size_t) 0, (dvoid **) 0);
     
     /* Set the Bfile Locator Information */ 
     checkerr(errhp, (OCILobFileSetName(envhp, errhp, &bfile_loc,  
                           (OraText *)"ADGRAPHIC_DIR", (ub2)strlen("ADGRAPHIC_
DIR"),  
                           (OraText *)"keyboard_graphic_3106_13001",  
                           (ub2)strlen("keyboard_graphic_3106_13001"))));  
     checkerr(errhp, OCILobOpen(svchp, errhp, bfile_loc, 
                                (ub1)OCI_FILE_READONLY));
     /* ... Do some processing. */
     checkerr(errhp, OCILobClose(svchp, errhp, bfile_loc));
 
     /* Free the locator descriptor */ 
     OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Opening a BFILE with OPEN

     * Opening a BFILE with OPEN.  [Example script: 3978.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. OPEN-BFILE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       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.
       OPEN-BFILE.

           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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
      * Assign directory alias and file name to BFILE: 
           EXEC SQL 
                LOB FILE SET :SRC-BFILE 
                DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC.

      * Open the BFILE read only: 
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

      * Close the LOB: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.

      * And free the LOB locator: 
           EXEC SQL FREE :SRC-BFILE 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++): Opening a BFILE with OPEN

/* Opening a BFILE using OPEN. [Example script: 3979.pc]
   In Pro*C/C++ there is only one form of OPEN used for OPENing
   BFILEs. There is no FILE OPEN, only a simple OPEN statement: */

#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 openBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  char *Dir = "GRAPHIC_DIR", *Name = "mousepad_2056";

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Initialize the Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* ... Do some processing: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O) Opening a BFILE with OPEN

'Opening a BFILE using OPEN. [Example script: 3981.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
 
'Go to the last row and open the Bfile for reading: 
OraDyn.MoveLast 
OraAdGraphic.Open 'Open Bfile for reading 
'Do some processing:  
OraAdGraphic.Close 

Java (JDBC): Opening a BFILE with OPEN

// Opening a BFILE with OPEN.  [Example script: 3982.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_41
{
  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
    {
       BFILE src_lob = null;
       ResultSet rset = null;

       rset = stmt.executeQuery (
       "SELECT BFILENAME('ADGRAPHIC_DIR', 'monitor_graphic_3060_11001') FROM 
DUAL");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);

          OracleCallableStatement cstmt = (OracleCallableStatement)
          conn.prepareCall ("begin dbms_lob.open (?,dbms_lob.lob_readonly); 
end;");
          cstmt.registerOutParameter(1,OracleTypes.BFILE);
          cstmt.setBFILE (1, src_lob);
          cstmt.execute();
          src_lob = cstmt.getBFILE(1);
          System.out.println ("the file is now open");
       }

       // Close the BFILE,  statement and connection: 
       src_lob.closeFile();
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Ways to See If a BFILE is Open

Figure 12-14 Use Case Diagram: Two Ways to See If a BFILE is Open

Recommendation: Use OPEN to Open BFILE

As you can see by comparing the code, these alternative methods are very similar. While you can continue to use the older FILEISOPEN form, we strongly recommend that you switch to using ISOPEN, because this facilitates future extensibility.

Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES

A limited number of BFILEs can be open simultaneously in each session. The maximum number is specified by using the SESSION_MAX_OPEN_FILES initialization parameter.

SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session. The default value for this parameter is 10. That is, a maximum of 10 files can be opened simultaneously in each session if the default value is utilized. The database administrator can change the value of this parameter in the init.ora file. For example:

SESSION_MAX_OPEN_FILES=20

If the number of unclosed files exceeds the SESSION_MAX_OPEN_FILES value then you will not be able to open any more files in the session. To close all open files, use the FILECLOSEALL call.

Checking If the BFILE is Open with FILEISOPEN

Figure 12-15 Use Case Diagram: Checking If BFILE is Open Using FILEISOPEN

Purpose

This procedure describes how to see if a BFILE is OPEN with FILEISOPEN.

Usage Notes

While you can continue to use the older FILEISOPEN form, we strongly recommend that you switch to using ISOPEN, because this facilitates future extensibility.

Syntax

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

• PL/SQL(DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- FILEISOPEN
• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileIsOpen
• COBOL (Pro*COBOL): A syntax reference is not applicable in this release.
• C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.
• Visual Basic (OO4O): A syntax reference is not applicable in this release.
• 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

These examples query whether a BFILE associated with ad_graphic is open.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking If the BFILE is Open with FILEISOPEN
• C (OCI): Checking If the BFILE is Open with FILEISOPEN
• C/C++ (Pro*C/C++): No example is provided with this release.
• COBOL (Pro*COBOL): No example is provided with this release.
• Visual Basic (OO4O): No example is provided. See note
• Java (JDBC): Checking If the BFILE is Open with FILEISOPEN

PL/SQL (DBMS_LOB Package): Checking If the BFILE is Open with FILEISOPEN

/* Checking if the BFILE is OPEN with FILEISOPEN. [Example script: 3984.sql]
   Procedure seeIfOpenBFILE_procOne is not part of DBMS_LOB package: */

CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procOne IS
   File_loc      BFILE;
   RetVal       INTEGER;
BEGIN
   /* Select the LOB, initializing the BFILE locator: */
   SELECT ad_graphic INTO File_loc FROM Print_media
      WHERE product_ID = 3060 AND ad_id = 11001;
   RetVal := DBMS_LOB.FILEISOPEN(File_loc);
   IF (RetVal = 1)
      THEN
      DBMS_OUTPUT.PUT_LINE('File is open');
   ELSE
      DBMS_OUTPUT.PUT_LINE('File is not open');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Checking If the BFILE is Open with FILEISOPEN

/* Checking if the BFILE is open with FILEISOPEN. [Example script: 3985.c] */

/* Select the lob/bfile from the Print_media table */ 
void selectLob(Lob_loc, errhp, svchp, stmthp) 
OCILobLocator *Lob_loc; 
OCIError      *errhp; 
OCISvcCtx     *svchp; 
OCIStmt       *stmthp; 
{ 
     OCIDefine *dfnhp, *dfnhp2;
     text *selstmt =
             (text *) "SELECT ad_graphic FROM Print_media 
                      WHERE product_id=3106 AND ad_id = 13001";

     /* Prepare the SQL select statement */ 
     checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt,  
                                     (ub4) strlen((char *) selstmt), 
                                     (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
 
     /* Call define for the bfile column */ 
     checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)
     ||  OCIDefineByPos(stmthp, &dfnhp2, errhp, 2,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)); 
 
     /* Execute the SQL select statement */ 
     checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0, 
                                     (ub4) OCI_DEFAULT)); 
} 
void BfileFileIsOpen(envhp, errhp, svchp, stmthp) 
OCIEnv  *envhp; 
OCIError *errhp; 
OCISvcCtx *svchp; 
OCIStmt *stmthp; 
{ 

     OCILobLocator *bfile_loc; 
     boolean flag;
 
     /* Allocate the locator descriptor */ 
     (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                 (ub4) OCI_DTYPE_FILE,  
                 (size_t) 0, (dvoid **) 0);
 
     /* Select the bfile */ 
     selectLob(bfile_loc, errhp, svchp, stmthp); 

     checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, 
                                         (ub1)OCI_FILE_READONLY));

     checkerr(errhp, OCILobFileIsOpen(svchp, errhp, bfile_loc, &flag));

     if (flag == TRUE)
     {
       printf("File is open\n");
     }
     else
     {
       printf("File is not open\n");
     }

     checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc));

     /* Free the locator descriptor */ 
     OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
} 

Visual Basic (OO4O): Checking If the BFILE is Open with FILEISOPEN

Java (JDBC): Checking If the BFILE is Open with FILEISOPEN

// Checking if the BFEIL is open with FILEISOPEN.[Example script:3986.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_45
{

  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
    {
        BFILE src_lob = null;
        ResultSet rset = null;
        boolean result = false;

        rset = stmt.executeQuery (
           "SELECT BFILENAME('ADGRAPHIC_DIR', 'monitor_graphic_3060_11001') FROM 
DUAL");
        if (rset.next())
        {
           src_lob = ((OracleResultSet)rset).getBFILE (1);
        }

        result = src_lob.isFileOpen();
        System.out.println(
           "result of fileIsOpen() before opening file : " + result);
        if (!result) 
        src_lob.openFile();

        result = src_lob.isFileOpen();
        System.out.println(
           "result of fileIsOpen() after opening file : " + result);

        // Close the BFILE, statement and connection: 
        src_lob.closeFile();
        stmt.close();
        conn.commit();
        conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Checking If a BFILE is Open Using ISOPEN

Figure 12-16 Use Case Diagram: Checking If a BFILE is Open Using ISOPEN

Purpose

This procedure describes how to see if a BFILE is open using ISOPEN.

Usage Notes

Not applicable.

Syntax

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

• PL/SQL (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- ISOPEN
• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileIsOpen
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB DESCRIBE ... ISOPEN.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB DESCRIBE ... ISOPEN
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > IsOpen and > OBJECTS > OraDynaset
• 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

These examples query whether the a BFILE is open that is associated with ad_graphic.

Examples

Examples are provided in the following six programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking If the BFILE is Open with ISOPEN
• C (OCI): Checking If the BFILE is Open with ISOPEN
• COBOL (Pro*COBOL): Checking If the BFILE is Open with ISOPEN
• C/C++ (Pro*C/C++): Checking If the BFILE is Open with ISOPEN
• Visual Basic (OO4O): Checking If the BFILE is Open with ISOPEN
• Java (JDBC): Checking If the BFILE is Open with ISOPEN

PL/SQL (DBMS_LOB Package): Checking If the BFILE is Open with ISOPEN

/* Checking if the BFILE is open with ISOPEN. [Example script: 3987.sql] */
/* Procedure seeIfOpenBFILE_procTwo is not part of DBMS_LOB package: */

CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procTwo IS
   File_loc      BFILE;
   RetVal       INTEGER;
BEGIN
   /* Select the LOB, initializing the BFILE locator: */
   SELECT ad_graphic INTO File_loc FROM Print_media
      WHERE product_ID = 3060 AND ad_id = 11001;
   RetVal := DBMS_LOB.ISOPEN(File_loc);
   IF (RetVal = 1)
   THEN
      DBMS_OUTPUT.PUT_LINE('File is open');
   ELSE
      DBMS_OUTPUT.PUT_LINE('File is not open');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Checking If the BFILE is Open with ISOPEN

/* Checking if the BFILE is Open with ISOPEN. Example script:3988.c] */

/* Select the lob/bfile from the Print_media table */ 
void selectLob(Lob_loc, errhp, svchp, stmthp) 
OCILobLocator *Lob_loc; 
OCIError      *errhp; 
OCISvcCtx     *svchp; 
OCIStmt       *stmthp; 
{ 
     OCIDefine *dfnhp, *dfnhp2;
     text *selstmt =
             (text *) "SELECT ad_graphic FROM Print_media 
                         WHERE product_id=3106 AND ad_id = 13001";

     /* Prepare the SQL select statement */ 
     checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt,  
                                     (ub4) strlen((char *) selstmt), 
                                     (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
 
     /* Call define for the bfile column */ 
     checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)
     ||  OCIDefineByPos(stmthp, &dfnhp2, errhp, 2,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)); 
     /* Execute the SQL select statement */ 
     checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0, 
                                     (ub4) OCI_DEFAULT)); 
} 
void BfileIsOpen(envhp, errhp, svchp, stmthp) 
OCIEnv  *envhp; 
OCIError *errhp; 
OCISvcCtx *svchp; 
OCIStmt *stmthp; 
{ 

     OCILobLocator *bfile_loc; 
     boolean flag;
 
     /* Allocate the locator descriptor */ 
     (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                 (ub4) OCI_DTYPE_FILE,  
                 (size_t) 0, (dvoid **) 0);
 
     /* Select the bfile */ 
     selectLob(bfile_loc, errhp, svchp, stmthp); 

     checkerr(errhp, OCILobOpen(svchp, errhp, bfile_loc, 
                                         (ub1)OCI_FILE_READONLY));

     checkerr(errhp, OCILobIsOpen(svchp, errhp, bfile_loc, &flag));

     if (flag == TRUE)
     {
       printf("File is open\n");
     }
     else
     {
       printf("File is not open\n");
     }

     checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc));

     /* Free the locator descriptor */ 
     OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
} 

COBOL (Pro*COBOL): Checking If the BFILE is Open with ISOPEN

     * Checking if BFILE is open with ISOPEN. [Example script: 3989.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. OPEN-BFILE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       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.
       OPEN-BFILE.

           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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3060_11001" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
      * Assign directory alias and file name to BFILE: 
           EXEC SQL 
                LOB FILE SET :SRC-BFILE 
                DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME
           END-EXEC.

      * Open the BFILE read only: 
           EXEC SQL
                LOB OPEN :SRC-BFILE READ ONLY
           END-EXEC.

      * Close the LOB: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.

      * And free the LOB locator: 
           EXEC SQL FREE :SRC-BFILE 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++): Checking If the BFILE is Open with ISOPEN

/* Checking if the BFILE is open with ISOPEN. [Example script: 3990.pc]
   In Pro*C/C++, there is only one form of ISOPEN to determine whether
   or not a BFILE is OPEN. There is no FILEISOPEN, only a simple ISOPEN.
   This is an attribute used in the DESCRIBE statement: */

#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 seeIfOpenBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  int isOpen;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  /* Select the BFILE into the locator: */
  EXEC SQL SELECT ad_graphic INTO :Lob_loc FROM Print_media
           WHERE product_id = 2056 AND ad_id = 12001;
  /* Determine if the BFILE is OPEN or not: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET ISOPEN into :isOpen;
  if (isOpen)
    printf("BFILE is open\n");
  else
    printf("BFILE is not open\n");
  /* Note that in this example, the BFILE is not open: */
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Checking If the BFILE is Open with ISOPEN

' Checking if the BFILE is open with ISOPEN. [Example script: 3992.txt]
Dim OraDyn as OraDynaset, OraAdGraphic as OraBFile, amount_read%, chunksize%, 
chunk 
 
chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) 
Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value 
 
If OraAdGraphic.IsOpen then 
  'Process, if the file is already open: 
Else 
   'Process, if the file is not open, and return an error: 
End If 

Java (JDBC): Checking If the BFILE is Open with ISOPEN

// Checking if the BFILE is open with ISOPEN. [Example script: 3993.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_48
{

  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
    {
       BFILE src_lob = null;
       ResultSet rset = null;
       Boolean result = null;
       rset = stmt.executeQuery (
          "SELECT BFILENAME('ADGRAPHIC_DIR', 'monitor_graphic_3060_11001') FROM 
DUAL");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);
       }
       result = new Boolean(src_lob.isFileOpen());
       System.out.println(
          "result of fileIsOpen() before opening file : " + result.toString());
       src_lob.openFile();
       result = new Boolean(src_lob.isFileOpen());
       System.out.println(
          "result of fileIsOpen() after opening file : " + result.toString());

       // Close the BFILE,  statement and connection: 
       src_lob.closeFile();
       
       int i = cstmt.getInt(1);
       System.out.println("The result is: " + Integer.toString(i));

       OracleCallableStatement cstmt2 = (OracleCallableStatement) 
       conn.prepareCall (
       "BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READONLY); END;");
       cstmt2.setBFILE(1, bfile);
       cstmt2.execute();

       System.out.println("The BFILE has been opened with a call to "
      +"DBMS_LOB.OPEN()");

       // Use the existing cstmt handle to re-query the status of the locator: 
       cstmt.setBFILE(2, bfile);
       cstmt.execute();
       i = cstmt.getInt(1);
       System.out.println("This result is: " + Integer.toString(i));

       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Displaying BFILE Data

Figure 12-17 Use Case Diagram: Displaying BFILE Data

Purpose

This procedure describes how to display BFILE data.

Usage Notes

Not applicable.

Syntax

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

• PL/SQL (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- READ. Chapter 29, "DBMS_OUTPUT" - PUT_LINE
• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileOpen, OCILobRead
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB READ, DISPLAY.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements" -- READ
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Read, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
• 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

These examples open and display BFILE data.

Examples

Examples are provided in six programmatic environments:

• PL/SQL: Displaying BFILE Data
• C (OCI): Displaying BFILE Data
• COBOL (Pro*COBOL): Displaying BFILE Data
• C/C++ (Pro*C/C++): Displaying BFILE Data
• Visual Basic (OO4O): Displaying BFILE Data
• Java (JDBC): Displaying BFILE Data

PL/SQL: Displaying BFILE Data

/* Displaying BFILE data. [Example script: 3994.sql] */
/* Procedure displayBFILE_proc is not part of DBMS_LOB package: */ 

CREATE OR REPLACE PROCEDURE displayBFILE_proc IS 
   File_loc  BFILE; 
   Buffer   RAW(1024); 
   Amount   BINARY_INTEGER := 1024; 
   Position INTEGER        := 1; 
BEGIN 
   /* Select the LOB: */ 
   SELECT ad_graphic INTO File_loc 
       FROM print_media WHERE Product_ID = 3060 AND ad_id = 11001; 
   /* Opening the BFILE: */ 
   DBMS_LOB.OPEN (File_loc, DBMS_LOB.LOB_READONLY); 
   LOOP 
      DBMS_LOB.READ (File_loc, Amount, Position, Buffer); 
      /* Display the buffer contents: */ 
      DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer)); 
      Position := Position + Amount; 
   END LOOP; 
   /* Closing the BFILE: */ 
   DBMS_LOB.CLOSE (File_loc); 
   EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('End of data'); 
END;

C (OCI): Displaying BFILE Data

/* Displaying BFILE data. [Example script: 3995.c] */
/* Select the lob/bfile from the Print_media table */ 

void selectLob(Lob_loc, errhp, svchp, stmthp) 
OCILobLocator *Lob_loc;
OCIError      *errhp; 
OCISvcCtx     *svchp; 
OCIStmt       *stmthp; 
{ 
     OCIDefine *dfnhp, *dfnhp2;
     text *selstmt =
             (text *) "SELECT ad_graphic FROM Print_media 
                          WHERE product_id=3106 AND ad_id = 13001";

     /* Prepare the SQL select statement */ 
     checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt,  
                                     (ub4) strlen((char *) selstmt), 
                                     (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
 
     /* Call define for the bfile column */ 
     checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)
     || OCIDefineByPos(stmthp, &dfnhp2, errhp, 2,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)); 
 
     /* Execute the SQL select statement */ 
     checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0, 
                                     (ub4) OCI_DEFAULT)); 
} 

#define MAXBUFLEN 32767

void BfileDisplay(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 
   /* Assume all handles passed as input to this routine have been 
      allocated and initialized */ 
   OCILobLocator *bfile_loc; 
   ub1 bufp[MAXBUFLEN];
   ub4 buflen, amt, offset;
   boolean done;
   ub4 retval;
 
   /* Allocate the locator descriptor */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);
     /* Select the bfile */ 
    selectLob(bfile_loc, errhp, svchp, stmthp); 

    checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, 
                                   OCI_FILE_READONLY));
    /* This example will READ the entire contents of a BFILE piecewise into a
       buffer using a standard polling method, processing each buffer piece
       after every READ operation until the entire BFILE has been read. */
    /* Setting amt = 0 will read till the end of LOB*/
    amt = 0;
    buflen = sizeof(bufp);
    /* Process the data in pieces */
    offset = 1;
    memset(bufp, '\0', MAXBUFLEN);
    done = FALSE;
    while (!done)
    {   
        retval = OCILobRead(svchp, errhp, bfile_loc, 
                            &amt, offset, (dvoid *) bufp,
                            buflen, (dvoid *)0,
                            (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                            (ub2) 0, (ub1) SQLCS_IMPLICIT);
        switch (retval)
        {
            case OCI_SUCCESS:             /* Only one piece or last piece*/
              /* process the data in bufp. amt will give the amount of data 
                just read in bufp. This is in bytes for BLOBs and in characters 
                for fixed  width CLOBS and in bytes for variable width CLOBs*/
              done = TRUE;       
              break;
           case OCI_ERROR:
              /*  report_error();         this function is not shown here */
              done = TRUE;
              break;
           case OCI_NEED_DATA:           /* There are 2 or more pieces */
              /* process the data in bufp. amt will give the amount of
                 data just read in bufp. This is in bytes for BFILEs and i
                 characters for fixed width CLOBS and in bytes for variable 
                 width CLOBs */
              break;
           default:
             (void) printf("Unexpected ERROR: OCILobRead() LOB.\n");
             done = TRUE;
             break;
          }  /* switch */
      } /* while */

      /* Closing the BFILE is mandatory if you have opened it */
      checkerr (errhp, OCILobFileClose(svchp, errhp, bfile_loc));
 
      /* Free the locator descriptor */ 
      OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Displaying BFILE Data

     * Displaying BFILE data. [Example script: 3996.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID.  DISPLAY-BFILE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(9) VALUES "SAMP/SAMP".
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  DEST-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  BUFFER         PIC X(5) VARYING.
       01  OFFSET    PIC S9(9) COMP VALUE 1.
       01  AMT            PIC S9(9) COMP.
       01  ORASLNRD       PIC  9(4).
           EXEC SQL END DECLARE SECTION END-EXEC.
       01  D-AMT       PIC 99,999,99.
           EXEC SQL VAR BUFFER IS LONG RAW (100) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       DISPLAY-BFILE-DATA.

      * Connect to ORACLE
           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.

      * Select the BFILE
           EXEC SQL SELECT AD_GRAPHIC INTO :SRC-BFILE
              FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 
           END-EXEC.
           
      * Open the BFILE
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
      
      * Set the amount = 0 will initiate the polling method
           MOVE 0 TO AMT;
           EXEC SQL LOB READ :AMT FROM :SRC-BFILE INTO :BUFFER END-EXEC.

      *    DISPLAY "BFILE DATA".
      *    MOVE AMT TO D-AMT.
      *    DISPLAY "First READ (", D-AMT, "): " BUFFER.

      * Do READ-LOOP until the whole BFILE is read.
           EXEC SQL WHENEVER NOT FOUND GO TO END-LOOP END-EXEC.
            
       READ-LOOP.
           EXEC SQL LOB READ :AMT FROM :SRC-BFILE INTO :BUFFER END-EXEC.

      *    MOVE AMT TO D-AMT.
      *    DISPLAY "Next READ (", D-AMT, "): " BUFFER.

           GO TO READ-LOOP.

       END-LOOP.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.

      * Close the LOB
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.

      * And free the LOB locator
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           EXEC SQL ROLLBACK 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++): Displaying BFILE Data

/* Displaying BFILE data. [Example script: 3997.pc]
   This example reads the entire contents of a BFILE piecewise into a
   buffer using a streaming mechanism via standard polling, displaying each
   buffer piece after every READ operation until the entire BFILE has been
   read: */

#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);
}

#define BufferLength 1024

void displayBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  int Amount;
  struct {
    short Length;
    char Data[BufferLength];
  } Buffer;
  /* Datatype Equivalencing is Mandatory for this Datatype: */
  EXEC SQL VAR Buffer is VARRAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  /* Select the BFILE: */
  EXEC SQL SELECT ad_graphic INTO :Lob_loc
           FROM Print_media WHERE Product_ID = 2056 AND ad_id = 12001;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Setting Amount = 0 will initiate the polling method: */
  Amount = 0;
  /* Set the maximum size of the Buffer: */
  Buffer.Length = BufferLength;
  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      /* Read a piece of the BFILE into the Buffer: */
      EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer;
      printf("Display %d bytes\n", Buffer.Length);
    }
  printf("Display %d bytes\n", Amount);
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Displaying BFILE Data

' Displaying BFILE data.  [Example script: 3999.txt]
Dim MySession As OraSession
Dim OraDb As OraDatabase

Dim OraDyn As OraDynaset, OraAdGraphio As OraBfile, amount_read%, chunksize%, 
chunk As Variant

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&)

chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value

OraAdGraphic.offset = 1
OraAdGraphic.PollingAmount = OraAdGraphic.Size 'Read entire BFILE contents

'Open the Bfile for reading: 
OraAdGraphic.Open
amount_read = OraAdGraphic.Read(chunk, chunksize)

While OraAdGraphic.Status = ORALOB_NEED_DATA
    amount_read = OraAdGraphic.Read(chunk, chunksize)
Wend
OraAdGraphic.Close

Java (JDBC): Displaying BFILE Data

// Displaying BFILE data. [Example script: 4000.java]

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_53
{

  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
    {
       BFILE src_lob = null;
       ResultSet rset = null;
       Boolean result = null;
       InputStream in = null;
       byte buf[] = new byte[1000];
       int length = 0;
       boolean alreadyDisplayed = false;
       rset = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media 
              WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);
       }

       // Open the BFILE: 
       src_lob.openFile();

       // Get a handle to stream the data from the BFILE: 
       in = src_lob.getBinaryStream();

       // This loop fills the buf iteratively, retrieving data 
       // from the InputStream: 
       while ((in != null) && ((length = in.read(buf)) != -1)) 
       {
          // the data has already been read into buf

         // We will only display the first CHUNK in this example: 
         if (! alreadyDisplayed) 
         {
            System.out.println("Bytes read in: " + Integer.toString(length));
            System.out.println(new String(buf));
            alreadyDisplayed = true;
         }
      }

      // Close the stream, BFILE, statement and connection: 
      in.close();
      src_lob.closeFile();
      stmt.close();
      conn.commit();
      conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Reading Data from a BFILE

Figure 12-18 Use Case Diagram: Reading Data from a BFILE

Purpose

This procedure describes how to read data from a BFILE.

Usage Notes

Always Specify 4 GByte - 1 Regardless of LOB Size

When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can specify an input amount of 4 GByte -1 regardless of the starting offset and the amount of data in the LOB. Hence, you do not need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.

Example

For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here is the OCI read call, excluding the initialization of all parameters:

#define MAX_LOB_SIZE 4294967295 
ub4  amount =  MAX_LOB_SIZE; 
ub4  offset = 1000; 
OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0) 

The Amount Parameter

• In DBMS_LOB.READ, the amount parameter can be larger than the size of the data. In PL/SQL, the amount parameter should be less than or equal to the size of the buffer, and the buffer size is limited to 32K.
• In OCILobRead, you can specify amount = 4 Gb - 1, and it will read to the end of the LOB.

Syntax

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

• PL/SQL (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- READ
• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobRead
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB READ.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB READ
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Read, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
• 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 read a photograph into ad_graphic from a BFILE in 'ADPHOTO_DIR'.

Examples

Examples are provided in these six programmatic environments:

• PL/SQL (DBMS_LOB Package): Reading Data from a BFILE
• C (OCI): Reading Data from a BFILE
• COBOL (Pro*COBOL): Reading Data from a BFILE
• C/C++ (Pro*C/C++): Reading Data from a BFILE
• Visual Basic (OO4O): Reading Data from a BFILE
• Java (JDBC): Reading Data from a BFILE

PL/SQL (DBMS_LOB Package): Reading Data from a BFILE

/* Reading data from a BFILE. [Example script: 4002.sql]     */
/* Procedure readBFILE_proc is not part of DBMS_LOB package: */

CREATE OR REPLACE PROCEDURE readBFILE_proc IS
   File_loc       BFILE := BFILENAME('ADPHOTO_DIR', 
       'keyboard_photo_3060_11001');
   Amount        INTEGER := 32767;
   Position      INTEGER := 1;
   Buffer        RAW(32767);
BEGIN
   /* Select the LOB: */  
   SELECT ad_graphic INTO File_loc FROM print_media 
      WHERE Product_ID = 3060 AND ad-Id = 11001;
   /* Open the BFILE: */  
   DBMS_LOB.OPEN(File_loc, DBMS_LOB.LOB_READONLY);
   /* Read data: */  
   DBMS_LOB.READ(File_loc, Amount, Position, Buffer);
   /* Close the BFILE: */  
   DBMS_LOB.CLOSE(File_loc);
END;

C (OCI): Reading Data from a BFILE

/* Reading data from a BFILE. [Example script: 4003.c] */

/* Select the lob/bfile from the Print_media table */ 
void selectLob(Lob_loc, errhp, svchp, stmthp) 
OCILobLocator *Lob_loc;
OCIError      *errhp; 
OCISvcCtx     *svchp; 
OCIStmt       *stmthp; 
{ 
     OCIDefine *dfnhp, *dfnhp2;
     text *selstmt =
             (text *) "SELECT ad_graphic FROM Print_media 
                       WHERE product_id=3106 AND ad_id = 13001";

     /* Prepare the SQL select statement */ 
     checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt,  
                                     (ub4) strlen((char *) selstmt), 
                                     (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
 
     /* Call define for the bfile column */ 
     checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)
     || OCIDefineByPos(stmthp, &dfnhp2, errhp, 2,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)); 
 
     /* Execute the SQL select statement */ 
     checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0, 
                                     (ub4) OCI_DEFAULT)); 
} 

#define MAXBUFLEN 32767

void BfileRead(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   ub1 bufp[MAXBUFLEN];
   ub4 buflen, amt, offset;
   ub4 retval;
 
   /* Allocate the locator descriptor */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);
     /* Select the bfile */ 
    selectLob(bfile_loc, errhp, svchp, stmthp); 

    checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, 
                                   OCI_FILE_READONLY));

    /* This example will READ the entire contents of a BFILE piecewise into a
       buffer using a standard polling method, processing each buffer piece
       after every READ operation until the entire BFILE has been read. */
    /* Setting amt = 0 will read till the end of LOB*/
    amt = 0;
    buflen = sizeof(bufp);
    /* Process the data in pieces */
    offset = 1;
    memset(bufp, '\0', MAXBUFLEN);

    retval = OCILobRead(svchp, errhp, bfile_loc, 
                        &amt, offset, (dvoid *) bufp,
                        buflen, (dvoid *)0,
                        (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                        (ub2) 0, (ub1) SQLCS_IMPLICIT);

    /* Closing the BFILE is mandatory if you have opened it */
    checkerr (errhp, OCILobFileClose(svchp, errhp, bfile_loc));
 
    /* Free the locator descriptor */ 
    OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Reading Data from a BFILE

     * Reading data from a BFILE.  [Example script: 4004.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. READ-BFILE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  BFILE1         SQL-BFILE.
       01  BUFFER2        PIC X(5) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
         
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL VAR BUFFER2 IS LONG RAW(5) END-EXEC.

       PROCEDURE DIVISION.
       READ-BFILE.

      * Allocate and initialize the CLOB locator
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT AD_GRAPHIC INTO :BFILE1
                FROM PRINT_MEDIA M WHERE M.PRODUCT_ID = 3106 AND AD_ID = 13001
           END-EXEC.
      * Open the BFILE
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.
 
      * Initiate polling read
           MOVE 0 TO AMT.

           EXEC SQL LOB READ :AMT FROM :BFILE1
                INTO :BUFFER2 END-EXEC.
      *
      *     Display the data here.
      * 
  
      * Close and free the locator
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.

       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 END-EXEC.

C/C++ (Pro*C/C++): Reading Data from a BFILE

/* Reading data from BFILE. [Example script: 4005.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);
}

#define BufferLength 4096

void readBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  /* Amount and BufferLength are equal so only one READ is necessary: */
  int Amount = BufferLength;
  char Buffer[BufferLength];
  /* Datatype Equivalencing is Mandatory for this Datatype: */
  EXEC SQL VAR Buffer IS RAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_graphic INTO :Lob_loc
           FROM Print_media WHERE Product_ID = 2056;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  /* Read data: */
  EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer;
  printf("Read %d bytes\n", Amount);
  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Reading Data from a BFILE

' Reading data from a BFILE [Example script: 4007.txt]

Dim MySession As OraSession
Dim OraDb As OraDatabase

Dim OraDyn As OraDynaset, OraAdGraphic As OraBfile, amount_read%, chunksize%, 
chunk As Variant

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&)

chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value

OraAdGraphic.offset = 1
OraAdGraphic.PollingAmount = OraAdGraphic.Size 'Read entire BFILE contents

'Open the Bfile for reading: 
OraAdGraphic.Open
amount_read = OraAdGraphic.Read(chunk, chunksize)
While OraAdGraphic.Status = ORALOB_NEED_DATA
    amount_read = OraAdGraphic.Read(chunk, chunksize)
Wend
OraAdGraphic.Close

Java (JDBC): Reading Data from a BFILE

// Reading data from a BFILE.  [Example script: 4008.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_53
{
  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
    {
       BFILE src_lob = null;
       ResultSet rset = null;
       Boolean result = null;
       InputStream in = null;
       byte buf[] = new byte[1000];
       int length = 0;
       boolean alreadyDisplayed = false;
       rset = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media 
               WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);
       }

       // Open the BFILE: 
       src_lob.openFile();

       // Get a handle to stream the data from the BFILE: 
       in = src_lob.getBinaryStream();

       // This loop fills the buf iteratively, retrieving data
       // from the InputStream: 
       while ((in != null) && ((length = in.read(buf)) != -1)) 
       {
          // the data has already been read into buf

          // We will only display the first CHUNK in this example: 
          if (! alreadyDisplayed) 
          {
             System.out.println("Bytes read in: " + Integer.toString(length));
             System.out.println(new String(buf));
             alreadyDisplayed = true;
          }
       }

       // Close the stream, BFILE, statement and connection: 
       in.close();
       src_lob.closeFile();
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
  }
}

Reading a Portion of BFILE Data (substr)

Figure 12-19 Use Case Diagram: Reading a Portion of BFILE Data (substr)

Purpose

This procedure describes how to read portion of BFILE data (substr).

Usage Notes

Not applicable.

Syntax

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

• PL/SQL (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- SUBSTR
• C (OCI): A syntax reference is not applicable in this release.
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB OPEN, LOB CLOSE. See PL/SQL DBMS_LOB.SUBSTR.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB OPEN. See also PL/SQL DBMS_LOB.SUBSTR
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Open, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
• 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 read a graphic image into ad_graphic from BFILE 'ADPHOTO_DIR'.

Examples

Examples are provided in these five programmatic environments:

• PL/SQL (DBMS_LOB Package): Reading a Portion of BFILE Data (substr)
• C (OCI): No example is provided with this release.
• COBOL (Pro*COBOL): Reading a Portion of BFILE Data (substr)
• C/C++ (Pro*C/C++): Reading a Portion of BFILE Data (substr)
• Visual Basic (OO4O): Reading a Portion of BFILE Data (substr)
• Java (JDBC): Reading a Portion of BFILE Data (substr)

PL/SQL (DBMS_LOB Package): Reading a Portion of BFILE Data (substr)

/* Reading portion of a BFILE data using substr. [Example script: 4009.sql] */
/* Procedure substringBFILE_proc is not part of DBMS_LOB package:  */

CREATE OR REPLACE PROCEDURE substringBFILE_proc IS
   File_loc        BFILE;
   Position        INTEGER := 1;
   Buffer          RAW(32767);
BEGIN
   /* Select the LOB: */  
   SELECT PMtab.ad_graphic INTO File_loc FROM Print_media PMtab
      WHERE PMtab.product_id = 3060 AND PMtab.ad_id = 11001;
   /* Open the BFILE: */  
   DBMS_LOB.OPEN(File_loc, DBMS_LOB.LOB_READONLY);
   Buffer := DBMS_LOB.SUBSTR(File_loc, 255, Position);
   /* Close the BFILE: */  
   DBMS_LOB.CLOSE(File_loc);
END;

COBOL (Pro*COBOL): Reading a Portion of BFILE Data (substr)

     * Reading portion of a BFILE data using substr. [Example script: 4010.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-SUBSTR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  BFILE1         SQL-BFILE.
       01  BUFFER2        PIC X(32767) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  POS            PIC S9(9) COMP VALUE 1024.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
          
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL VAR BUFFER2 IS VARRAW(32767) END-EXEC.

       PROCEDURE DIVISION.
       BFILE-SUBSTR.

      * Allocate and initialize the CLOB locator: 
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT PTAB.AD_GRAPHIC INTO :BFILE1
                FROM PRINT_MEDIA PTAB WHERE PTAB.PRODUCT_ID = 3106 AND PTAB.AD_
ID = 13001
           END-EXEC.
 
      * Open the BFILE for READ ONLY: 
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.

      * Execute PL/SQL to use its SUBSTR functionality: 
           MOVE 32767 TO AMT.
           EXEC SQL EXECUTE
             BEGIN 
               :BUFFER2 := DBMS_LOB.SUBSTR(:BFILE1,:AMT,:POS);
             END;
           END-EXEC.

      * Close and free the locators: 
           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.

       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 END-EXEC.

C/C++ (Pro*C/C++): Reading a Portion of BFILE Data (substr)

/* Reading portion of a BFILE data using substr. [Example script: 4011.pc]
   Pro*C/C++ lacks an equivalent embedded SQL form for the DBMS_LOB.SUBSTR()
   function. However, Pro*C/C++ can interoperate with PL/SQL using anonymous
   PL/SQL blocks embedded in a Pro*C/C++ program as this example shows: */

#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);
}

#define BufferLength 256
void substringBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  int Position = 1;
  char Buffer[BufferLength];
  EXEC SQL VAR Buffer IS RAW(BufferLength);
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT PMtab.ad_graphic INTO :Lob_loc
           FROM Print_media PMtab WHERE PMtab.product_id = 2056 AND PMTab.ad_id 
= 12001;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Invoke SUBSTR() from within an anonymous PL/SQL block:  */
  EXEC SQL EXECUTE
    BEGIN
      :Buffer := DBMS_LOB.SUBSTR(:Lob_loc, 256, :Position);
    END;
  END-EXEC;
  /* Close the BFILE:  */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Reading a Portion of BFILE Data (substr)

' Reading portion of a BFILE data using substr. [Example script: 4013.txt]
Dim MySession As OraSession
Dim OraDb As OraDatabase

Dim OraDyn As OraDynaset, OraAdGraphic As OraBfile, amount_read%, chunksize%, 
chunk

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&)

chunk_size = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value
OraMusic.PollingAmount = OraAdGraphic.Size 'Read entire BFILE contents
OraAdGraphic.offset = 255 'Read from the 255th position
'Open the Bfile for reading: 
OraAdGraphic.Open
amount_read = OraAdGraphic.Read(chunk, chunk_size) 'chunk returned is a variant 
of type byte array
 If amount_read <> chunk_size Then
    'Do error processing
 Else
     'Process the data
 End If

Java (JDBC): Reading a Portion of BFILE Data (substr)

// Reading portion of a BFILE data using substr. [Example script: 4014.java]

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_62
{

  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
    {
   BFILE src_lob = null;
       ResultSet rset = null;
   InputStream in = null;
   byte buf[] = new byte[1000];
   int length = 0;
   rset = stmt.executeQuery (
      "SELECT ad_graphic FROM Print_media WHERE product_id = 3106 AND ad_id = 
13001");
   if (rset.next())
   {
     src_lob = ((OracleResultSet)rset).getBFILE (1);
   }

   // Open the BFILE: 
   src_lob.openFile();

   // Get a handle to stream the data from the BFILE
   in = src_lob.getBinaryStream();

   if (in != null) 
   {
      // request 255 bytes into buf, starting from offset 1.  
      // length = # bytes actually returned from stream: 
      length = in.read(buf, 1, 255);
      System.out.println("Bytes read in: " + Integer.toString(length));

      // Process the buf: 
      System.out.println(new String(buf));
   }

   // Close the stream, BFILE, statement and connection: 
   in.close();
   src_lob.closeFile();
   stmt.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Comparing All or Parts of Two BFILES

Figure 12-20 Use Case Diagram: Comparing All or Parts of Two BFILES

Purpose

This procedure describes how to compare all or parts of two BFILES.

Usage Notes

Not applicable.

Syntax

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

• PL/SQL(DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- COMPARE
• C (OCI): A syntax reference is not applicable in this release.
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB OPEN. See PL/SQL DBMS_LOB.COMPARE.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB OPEN. See PL/SQL DBMS_LOB.COMPARE.
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Open, Compare, and OO4O Automation Server > OBJECTS > OraDatabase > PROPERTIES > Parameters. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
• 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 determine whether a photograph in file, 'ADPHOTO_DIR', has already been used as a specific ad_graphic by comparing each data entity bit by bit.

Examples

Examples are provided in these five programmatic environments:

• PL/SQL (DBMS_LOB Package): Comparing All or Parts of Two BFILES
• C (OCI): No example is provided with this release.
• COBOL (Pro*COBOL): Comparing All or Parts of Two BFILES
• C/C++ (Pro*C/C++): Comparing All or Parts of Two BFILES
• Visual Basic (OO4O): Comparing All or Parts of Two BFILES
• Java (JDBC): Comparing All or Parts of Two BFILES

PL/SQL (DBMS_LOB Package): Comparing All or Parts of Two BFILES

/* Comparing all or parts of two BFILES. [Example script: 4015.sql] */
/* Procedure instringBFILE_proc is not part of DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE instringBFILE_proc IS
   File_loc        BFILE;
   Pattern        RAW(32767);
   Position       INTEGER;
BEGIN
   /* Select the LOB: */
   SELECT PMtab.ad_graphic INTO File_loc
      FROM THE(SELECT PMtab.textdoc_ntab FROM Print_media PMtab
         WHERE Product_ID = 3060 AND ad_id = 11001) PMtab
            WHERE Segment = 1;
   /* Open the BFILE: */
   DBMS_LOB.OPEN(File_loc, DBMS_LOB.LOB_READONLY);
   /*  Initialize the pattern for which to search, find the 2nd occurrence of
       the pattern starting from the beginning of the BFILE: */
   Position := DBMS_LOB.INSTR(File_loc, Pattern, 1, 2);
   /* Close the BFILE: */
   DBMS_LOB.CLOSE(File_loc);
END;

COBOL (Pro*COBOL): Comparing All or Parts of Two BFILES

     * Comparing all or parts of two BFILES. [Example script: 4016.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-COMPARE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID          PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1          SQL-BFILE.
       01  BFILE2          SQL-BFILE.
       01  RET             PIC S9(9) COMP.
       01  AMT             PIC S9(9) COMP.
       01  DIR-ALIAS       PIC X(30) VARYING.
       01  FNAME           PIC X(20) VARYING.
       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-COMPARE.

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

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.
           EXEC SQL ALLOCATE :BFILE2 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 17 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT AD_GRAPHIC INTO :BFILE2
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND ad_id = 13001
           END-EXEC.
 
      * Open the BLOBs for READ ONLY: 
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :BFILE2 READ ONLY END-EXEC.

      * Execute PL/SQL to get COMPARE functionality: 
           MOVE 5 TO AMT.
           EXEC SQL EXECUTE
             BEGIN 
               :RET := DBMS_LOB.COMPARE(:BFILE1,:BFILE2,
                                        :AMT,1,1);
             END;
           END-EXEC.
           
           IF RET = 0
      *        Logic for equal BFILEs goes here
               DISPLAY "BFILES are equal"
           ELSE
      *        Logic for unequal BFILEs goes here
               DISPLAY "BFILEs are not equal"
           END-IF.

           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.
           EXEC SQL LOB CLOSE :BFILE2 END-EXEC.
       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 END-EXEC.
           EXEC SQL FREE :BFILE2 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++): Comparing All or Parts of Two BFILES

/* Comparing all or parts of two BFILES. [Example script: 4017.pc]
   Pro*C/C++ lacks an equivalent embedded SQL form for the
   DBMS_LOB.COMPARE() function. Like the DBMS_LOB.SUBSTR() function,
   however, Pro*C/C++ can invoke DBMS_LOB.COMPARE() in an anonymous PL/SQL
   block as shown here:  */

#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 compareBFILEs_proc()
{
  OCIBFileLocator *Lob_loc1, *Lob_loc2;
  int Retval = 1;
  char *Dir1 = "GRAPHIC_DIR", *Name1 = "mousepad_2056";

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL LOB FILE SET :Lob_loc1 DIRECTORY = :Dir1, FILENAME = :Name1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT Photo INTO :Lob_loc2 FROM Print_media
           WHERE Product_ID = 2056;
  /* Open the BFILEs: */
  EXEC SQL LOB OPEN :Lob_loc1 READ ONLY;
  EXEC SQL LOB OPEN :Lob_loc2 READ ONLY;
  /* Compare the BFILEs in PL/SQL using DBMS_LOB.COMPARE() */
  EXEC SQL EXECUTE
    BEGIN
      :Retval := DBMS_LOB.COMPARE(
                   :Lob_loc2, :Lob_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1);
    END;
  END-EXEC;
  /* Close the BFILEs:  */
  EXEC SQL LOB CLOSE :Lob_loc1;
  EXEC SQL LOB CLOSE :Lob_loc2;
  if (0 == Retval)
    printf("BFILEs are the same\n");
  else
    printf("BFILEs are not the same\n");
  /* Release resources used by the locators: */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
}

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

Visual Basic (OO4O): Comparing All or Parts of Two BFILES

'Comparing all or parts of two BFILES. [Example script: 4018.txt]
'The PL/SQL packages and the tables mentioned here are not part of the
'standard OO4O installation: 

Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraAdGraphic As OraBfile, OraMyAdGraphic As OraBfile, 
OraSql As OraSqlStmt

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&)

OraDb.Connection.BeginTrans

Set OraParameters = OraDb.Parameters

OraParameters.Add "id", 3106, ORAPARM_INPUT

'Define out parameter of BFILE type: 
OraParameters.Add "MyAdGraphic", Null, ORAPARM_OUTPUT
OraParameters("MyAdGraphic").ServerType = ORATYPE_BFILE

Set OraSql = 
   OraDb.CreateSql(
      "BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE product_
id = :id; 
         END;", ORASQL_FAILEXEC)

Set OraMyAdGraphic = OraParameters("MyAdGraphic").Value

'Create dynaset: 
Set OraDyn = 
   OraDb.CreateDynaset(
      "SELECT * FROM Print_media WHERE product_id = 3106", ORADYN_DEFAULT)
Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value

'Open the Bfile for reading: 
OraAdGraphic.Open
OraMyAdGraphic.Open

If OraAdGraphic.Compare(OraMyAdGraphic) Then
    'Process the data
Else
   'Do error processing
End If
OraDb.Connection.CommitTrans

Java (JDBC): Comparing All or Parts of Two BFILES

// Comparing all or parts of two BFILES. [Example script: 4019.java]

import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
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_66
{

  static final int MAXBUFSIZE = 32767;

  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");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();

    try
    {
       BFILE lob_loc1 = null;
       BFILE lob_loc2 = null;
       ResultSet rset = null;

       rset = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media WHERE product_id = 3106");
       if (rset.next())
       {
          lob_loc1 = ((OracleResultSet)rset).getBFILE (1);
       }

       rset = stmt.executeQuery (
          "SELECT BFILENAME('AD_GRAPHIC', 'keyboard_3106') FROM DUAL");
       if (rset.next())
       {
          lob_loc2 = ((OracleResultSet)rset).getBFILE (1);
       }

       lob_loc1.openFile ();
       lob_loc2.openFile ();

       if (lob_loc1.length() > lob_loc2.length()) 
       System.out.println("Looking for LOB2 inside LOB1.  result = " +
          lob_loc1.position(lob_loc2, 1));
   else
     System.out.println("Looking for LOB1 inside LOB2.  result = " +
          lob_loc2.position(lob_loc1, 1));

   stmt.close();
   conn.commit();
   conn.close();

    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
  }
}

Checking If a Pattern Exists (instr) in the BFILE

Figure 12-21 Use Case Diagram: Checking If a Pattern Exists in the BFILE

Purpose

This procedure describes how to see if a pattern exists (instr) in the BFILE.

Usage Notes

Not applicable.

Syntax

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

• PL/SQL (DBMS_LOB) (Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- INSTR
• C (OCI): A syntax reference is not applicable in this release.
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB OPEN. See PL/SQL DBMS_LOB.INSTR.
• C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB OPEN. See PL/SQL DBMS_LOB.INSTR.
• Visual Basic (OO4O): A syntax reference is not applicable in this release.
• 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 search for the occurrence of a pattern in an ad_graphic image.

Examples

These examples are provided in the following four programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking If a Pattern Exists (instr) in the BFILE
• C (OCI): No example is provided with this release.
• COBOL (Pro*COBOL): Checking If a Pattern Exists (instr) in the BFILE
• C/C++ (Pro*C/C++): Checking If a Pattern Exists (instr) in the BFILE
• Visual Basic (OO4O): No example is provided with this release.
• Java (JDBC): Checking If a Pattern Exists (instr) in the BFILE

PL/SQL (DBMS_LOB Package): Checking If a Pattern Exists (instr) in the BFILE

/* Checking if a pattern exists in a BFILE using instr [Example script: 
4030.sql]
/* Procedure compareBFILEs_proc is not part of DBMS_LOB package: */

CREATE OR REPLACE PROCEDURE compareBFILEs_proc IS
   /* Initialize the BFILE locator: */
   File_loc1       BFILE := BFILENAME('ADPHOTO_DIR', 'keyboard_photo_3060_
11001');
   File_loc2       BFILE;
   Retval         INTEGER;
BEGIN
   /* Select the LOB: */
   SELECT ad_graphic INTO File_loc2 FROM print_media
      WHERE Product_ID = 3060 AND ad_id = 11001;
   /* Open the BFILEs: */
   DBMS_LOB.OPEN(File_loc1, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.OPEN(File_loc2, DBMS_LOB.LOB_READONLY);
   Retval := DBMS_LOB.COMPARE(File_loc2, File_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1);
   /* Close the BFILEs: */
   DBMS_LOB.CLOSE(File_loc1);
   DBMS_LOB.CLOSE(File_loc2);
END;

COBOL (Pro*COBOL): Checking If a Pattern Exists (instr) in the BFILE

     * Checking if a pattern exists in a BFILE using instr [Example script: 
4021.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-INSTR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.

      * The length of pattern was chosen arbitrarily: 
       01  PATTERN        PIC X(4) VALUE "2424".
           EXEC SQL VAR PATTERN IS RAW(4) END-EXEC.
       01  POS            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-INSTR.

           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 :BFILE1 END-EXEC.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT AD_GRAPHIC INTO :BFILE1
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001
           END-EXEC.
      
      * Open the CLOB for READ ONLY: 
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.

      * Execute PL/SQL to get INSTR functionality: 
           EXEC SQL EXECUTE
             BEGIN 
               :POS := DBMS_LOB.INSTR(:BFILE1,:PATTERN, 1, 2); END; END-EXEC.
           
           IF POS = 0
      *        Logic for pattern not found here
               DISPLAY "Pattern is not found."
           ELSE
      *        Pos contains position where pattern is found
               DISPLAY "Pattern is found."
           END-IF.

      * Close and free the LOB: 
           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.

       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 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++): Checking If a Pattern Exists (instr) in the BFILE

/* Checking if a pattern exists in a BFILE using instr [Example script: 4022.pc]
   Pro*C lacks an equivalent embedded SQL form of the DBMS_LOB.INSTR()
   function. However, like SUBSTR() and COMPARE(), Pro*C/C++ can call
   DBMS_LOB.INSTR() from within an anonymous PL/SQL block as shown here: */

#include <sql2oci.h>
#include <stdio.h>
#include <string.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);
}

#define PatternSize 5

void instringBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  unsigned int Position = 0;
  int Product_id = 2056, Segment = 1;
  char Pattern[PatternSize];
  /* Datatype Equivalencing is Mandatory for this Datatype:  */
  EXEC SQL VAR Pattern IS RAW(PatternSize);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  /* Use Dynamic SQL to retrieve the BFILE Locator:  */
  EXEC SQL PREPARE S FROM
    'SELECT Intab.ad_graphic \
       FROM TABLE(SELECT PMtab.textdoc_ntab FROM Print_media PMtab \
          WHERE product_id = :cid) PMtab \
             WHERE PMtab.Segment = :seg';
  EXEC SQL DECLARE C CURSOR FOR S;
  EXEC SQL OPEN C USING :Product_ID, :Segment;
  EXEC SQL FETCH C INTO :Lob_loc;
  EXEC SQL CLOSE C;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  memset((void *)Pattern, 0, PatternSize);  
  /* Find the first occurrance of the pattern starting from the
     beginning of the BFILE using PL/SQL: */
  EXEC SQL EXECUTE
    BEGIN
      :Position := DBMS_LOB.INSTR(:Lob_loc, :Pattern, 1, 1);
    END;
  END-EXEC;
  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  if (0 == Position)
    printf("Pattern not found\n");
  else
    printf("The pattern occurs at %d\n", Position);
  EXEC SQL FREE :Lob_loc;
}

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

Java (JDBC): Checking If a Pattern Exists (instr) in the BFILE

// Checking if a pattern exists in a BFILE using instr [Example script: 
4024.java]

import java.io.OutputStream;
// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
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_70
{

  static final int MAXBUFSIZE = 32767;

  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");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();

    try
    {
       BFILE lob_loc = null;
       // Pattern to look for within the BFILE: 
       String pattern = new String("children"); 

       ResultSet rset = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media 
              WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          lob_loc = ((OracleResultSet)rset).getBFILE (1);
       }

       // Open the LOB: 
       lob_loc.openFile();
       // Search for the location of pattern string in the BFILE, 
       // starting at offset 1: 
      long result = lob_loc.position(pattern.getBytes(), 1);
      System.out.println(
         "Results of Pattern Comparison : " + Long.toString(result));

      // Close the LOB: 
      lob_loc.closeFile();

      stmt.close();
      conn.commit();
      conn.close();

    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Checking If the BFILE Exists

Figure 12-22 Use Case Diagram: Checking If the BFILE Exists

Purpose

This procedure describes how to see if a BFILE exists.

Usage Notes

Not applicable.

Syntax

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

• PL/SQL (DBMS_LOB) Oracle9i Supplied PL/SQL Packages and Types Reference): Chapter 18, "DBMS_LOB" -- FILEEXISTS
• C (OCI) Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileExists
• COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB DESCRIBE ... FILEEXISTS.
• C/C++ (Pro*C/C++) Pro*C/C++ Precompiler Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" -- LOB DESCRIBE ...GET FILEEXISTS
• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > Exists, and OO4O Automation Server > OBJECTS > OraDatabase > PROPERTIES > Parameters. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples
• 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

This example queries whether a BFILE that is associated with Recording.

Examples

The examples are provided in the following six programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking If the BFILE Exists
• C (OCI): Checking If the BFILE Exists
• COBOL (Pro*COBOL): Checking If the BFILE Exists
• C/C++ (Pro*C/C++): Checking If the BFILE Exists
• Visual Basic (OO4O): Checking If the BFILE Exists
• Java (JDBC): Checking If the BFILE Exists

PL/SQL (DBMS_LOB Package): Checking If the BFILE Exists

/* Checking if a BFILE exists [Example script: 4025.sql] */
/* Procedure seeIfExistsBFILE_proc is not part of DBMS_LOB package:  */

CREATE OR REPLACE PROCEDURE seeIfExistsBFILE_proc IS
   File_loc      BFILE;
BEGIN
   /* Select the LOB: */
   SELECT Intab.ad_graphic INTO File_loc
      FROM THE(SELECT PMtab.textdoc_ntab FROM Print_media PMtab
         WHERE PMtab.product_id = 3060 AND ad_id = 11001) PMtab
            WHERE PMtab.Segment = 1;
   /* See If the BFILE exists: */
   IF (DBMS_LOB.FILEEXISTS(File_loc) != 0)
   THEN
      DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE exists');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE does not exist');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Checking If the BFILE Exists

/* Checking if a BFILE exists [Example script: 4026.c] */

/* Select the lob/bfile from the Print_media table */ 
void selectLob(Lob_loc, errhp, svchp, stmthp) 
OCILobLocator *Lob_loc;
OCIError      *errhp; 
OCISvcCtx     *svchp; 
OCIStmt       *stmthp; 
{ 
     OCIDefine *dfnhp, *dfnhp2;
     text *selstmt = (text *) "SELECT ad_graphic FROM Print_media \
                                  WHERE product_id = 3106 AND ad_id = 13001";

     /* Prepare the SQL select statement */ 
     checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt,  
                                     (ub4) strlen((char *) selstmt), 
                                     (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
 
     /* Call define for the bfile column */ 
     checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)
     || OCIDefineByPos(stmthp, &dfnhp2, errhp, 2,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)); 
 
     /* Execute the SQL select statement */ 
     checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0, 
                                     (ub4) OCI_DEFAULT)); 
} 

void BfileExists(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   boolean is_exist;
 
   /* Allocate the locator descriptor */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);
   /* Select the bfile */ 
   selectLob(bfile_loc, errhp, svchp, stmthp); 

   checkerr (errhp, OCILobFileExists(svchp, errhp, bfile_loc, &is_exist));

   if (is_exist == TRUE)
   {
     printf("File exists\n");
   }
   else
   {
     printf("File does not exist\n");
   }
 
   /* Free the locator descriptor */ 
   OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Checking If the BFILE Exists

     * Checking if a BFILE exists.  [Example script: 4027.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-EXISTS.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  FEXISTS        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-EXISTS.

           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 :BFILE1 END-EXEC.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT AD_GRAPHIC INTO :BFILE1
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001
           END-EXEC.

           EXEC SQL
                LOB DESCRIBE :BFILE1 GET FILEEXISTS INTO :FEXISTS
           END-EXEC.
           
           IF FEXISTS = 1
      *        Logic for file exists here
               DISPLAY "File exists"
           ELSE
      *        Logic for file does not exist here
               DISPLAY "File does not exist"
           END-IF.

       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 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++): Checking If the BFILE Exists

/* Checking if a BFILE exists.  [Example script: 4028.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 seeIfBFILEExists_proc()
{
  OCIBFileLocator *Lob_loc;
  unsigned int Exists = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT PMtab.ad_graphic INTO :Lob_loc
           FROM Print_media PMtab WHERE PMtab.Product_ID = 2056 AND PMtab.ad_id 
= 12001;
  /* See if the BFILE Exists:  */
  EXEC SQL LOB DESCRIBE :Lob_loc GET FILEEXISTS INTO :Exists;
  printf("BFILE %s exist\n", Exists ? "does" : "does not");
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Checking If the BFILE Exists

'Checking if a BFILE exists.  [Example script: 4030.txt]
'The PL/SQL packages and the tables mentioned here are not part of the
'standard OO4O installation: 

Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraAdGraphic As OraBfile, OraSql As OraSqlStmt

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("pmschema", "pm/pm", 0&)

OraDb.Connection.BeginTrans

Set OraParameters = OraDb.Parameters

OraParameters.Add "id", 2056, ORAPARM_INPUT

'Define out parameter of BFILE type: 
OraParameters.Add "MyAdGraphic", Null, ORAPARM_OUTPUT
OraParameters("MyAdGraphic").ServerType = ORATYPE_BFILE

Set OraSql = 
   OraDb.CreateSql(
      "BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media WHERE 
           product_id = :id; 
           END;", ORASQL_FAILEXEC)

Set OraAdGraphic = OraParameters("MyAdGraphic").Value

If OraAdGraphic.Exists Then
    'Process the data
Else
   'Do error processing
End If
OraDb.Connection.CommitTrans

Java (JDBC): Checking If the BFILE Exists

// Checking if a BFILE exists.  [Example script:  4031.java]

import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
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_74
{

  static final int MAXBUFSIZE = 32767;

  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");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();
    try
    {
       BFILE lob_loc = null;
       ResultSet rset = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media 
              WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          lob_loc = ((OracleResultSet)rset).getBFILE (1);
       }

       // See if the BFILE exists:  
       System.out.println("Result from fileExists(): " + lob_loc.fileExists());

       // Return the length of the BFILE: 
       long length = lob_loc.length();
       System.out.println("Length of BFILE: " + length);

       // Get the directory alias for this BFILE: 
       System.out.println("Directory alias: " + lob_loc.getDirAlias());

       // Get the file name for this BFILE: 
       System.out.println("File name: " + lob_loc.getName());
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}