Skip Headers

Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)

Part Number A96591-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

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 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:

Table 12-1 Use Case Model: External LOBs (BFILEs)  
Use Case and Page P O B C V J

Creating a Table Containing One or More BFILE Columns

S

S

S

S

S

S

Creating a Table of an Object Type with a BFILE Attribute

S

S

S

S

S

S

Creating a Table with a Nested Table Containing a BFILE

S

S

S

S

S

S

INSERT a Row Using BFILENAME()

S

+

+

+

+

+

INSERT a BFILE Row by Selecting a BFILE From Another Table

S

S

S

S

S

S

Inserting a Row With BFILE by Initializing a BFILE Locator

+

+

+

+

+

+

Loading Data Into External LOB (BFILE)

S

S

S

S

S

S

Loading a LOB with BFILE Data

+

+

+

+

+

+

Loading a BLOB with BFILE Data

+

-

-

-

-

-

Loading a CLOB with BFILE Data

+

-

-

-

-

-

Ways to Open a BFILE

-

-

-

-

-

-

Opening a BFILE with FILEOPEN

+

+

-

-

-

+

Opening a BFILE with OPEN

+

+

+

+

+

+

Ways to See If a BFILE is Open

-

-

-

-

-

-

Checking If the BFILE is Open with FILEISOPEN

+

+

-

-

-

+

Checking If a BFILE is Open Using ISOPEN

+

+

+

+

+

+

Displaying BFILE Data n

+

+

+

+

+

+

Reading Data from a BFILE n

+

+

+

+

+

+

Reading a Portion of BFILE Data (substr)

+

-

+

+

+

+

Comparing All or Parts of Two BFILES

+

-

+

+

+

+

Checking If a Pattern Exists (instr) in the BFILE

+

-

+

+

-

+

Checking If the BFILE Exists

+

+

+

+

+

+

Getting the Length of a BFILE

+

+

+

+

+

+

Copying a LOB Locator for a BFILE

+

+

+

+

-

+

Determining If a LOB Locator for a BFILE Is Initialized

-

+

-

+

-

-

Determining If One LOB Locator for a BFILE Is Equal to Another

-

+

-

+

-

+

Getting DIRECTORY Alias and Filename n

+

+

+

+

+

+

Updating a BFILE Using BFILENAME()

S

S

S

S

S

S

Updating a BFILE by Selecting a BFILE From Another Table

S

S

S

S

S

S

Updating a BFILE by Initializing a BFILE Locator

+

+

+

+

+

+

Closing a BFILE with FILECLOSE n

+

+

-

-

+

+

Closing a BFILE with CLOSE

+

+

+

+

+

+

Closing All Open BFILEs with FILECLOSEALL

+

+

+

+

+

+

Deleting the Row of a Table Containing a BFILE

S

S

S

S

S

S

Accessing External LOBs (BFILEs)

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

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.


Note:

Oracle does not verify that the directory and path name you specify actually exist. You should take care to specify a valid directory in your operating system. If your operating system uses case-sensitive path names, be sure you specify the directory in the correct format. There is no need to specify a terminating slash (for example,/tmp/ is not necessary, simply use /tmp).


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:

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.


Note:

You may need to set up data structures similar to the following for certain examples to work:

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:

Advantages.

This has the following advantages:

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:

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:

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.


Note:

The READ permission is defined only on the DIRECTORY object, not on individual files. Hence there is no way to assign different privileges to files in the same directory.


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:

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.


WARNING:

Because CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges potentially expose the server file system to all database users, the DBA should be prudent in granting these privileges to normal database users to prevent security breach.


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:

SQL DML for BFILE Security

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

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:

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:

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

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:

Creating a Table Containing One or More BFILE Columns

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

Text description of adlob113.gif follows
Text description of the illustration adlob113.gif


See Also:

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:

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

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

Text description of adlob121.gif follows
Text description of the illustration adlob121.gif


See Also:

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:

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:

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

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

Text description of adlob120.gif follows
Text description of the illustration adlob120.gif


See Also:

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:

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

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

Text description of adlob115.gif follows
Text description of the illustration adlob115.gif


See Also:

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:

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

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

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.

See Also:

"DIRECTORY Name Specification".

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:

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

/* 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)

Text description of adlob114.gif follows
Text description of the illustration adlob114.gif


See Also:

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".


Note:

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


Syntax

See the following syntax reference:

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

 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

Text description of adlob116.gif follows
Text description of the illustration adlob116.gif


See Also:

Purpose

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

Usage Notes


Note:

You must initialize the BFILE locator bind variable to a directory alias and filename before issuing the insert statement.



Note:

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


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:

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

/* 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)

Text description of adlob150.gif follows
Text description of the illustration adlob150.gif


See Also:

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

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.


Note:

A particular file to be loaded as a BFILE does not have to actually exist at the time of loading.


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.

See Also:

Oracle9i Application Developer's Guide - Fundamentals for more information on BFILES.

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:

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.


Note:

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

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

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,


Note:

product_ID defaults to (255) if a size is not specified. It is mapped to the file names in the datafile. ADGRAPHIC_PHOTO is the directory where all files are stored. ADGRAPHIC_DIR is a DIRECTORY object created previously.


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,


Note:

DirName FILLER CHAR (30) is mapped to the datafile field containing the directory name corresponding to the file being loaded.


Loading a LOB with BFILE Data

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

Text description of adlob102.gif follows
Text description of the illustration adlob102.gif


See Also:

Purpose

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

Usage Notes


Note:

The LOADBLOBFROMFILE and LOADCLOBFROMFILE procedures implement the functionality of this procedure and provide improved features for loading binary data and character data. The improved procedures are available in the PL/SQL environment only. When possible, using one of the improved procedures is recommended. See "Loading a BLOB with BFILE Data" and "Loading a CLOB with BFILE Data" for more information.


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

See Also:

Oracle9i Database Globalization Support Guide for character set conversion issues.

Specify Amount Parameter to be Less than the Size 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:

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

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

Text description of adlob173.gif follows
Text description of the illustration adlob173.gif


See Also:

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:

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:

Loading a CLOB with BFILE Data

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

Text description of adlob174.gif follows
Text description of the illustration adlob174.gif


See Also:

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:

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

See Also:

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

The following example illustrates:

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

Text description of adlob126.gif follows
Text description of the illustration adlob126.gif


See Also:

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

Text description of adlob127.gif follows
Text description of the illustration adlob127.gif


See Also:

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:

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

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


Note:

At the present time, OO4O only offers BFILE opening with OPEN (see "Visual Basic (OO4O) Opening a BFILE with OPEN").


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

Text description of adlob128.gif follows
Text description of the illustration adlob128.gif


See Also:

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:

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

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

Text description of adlob105.gif follows
Text description of the illustration adlob105.gif


See Also:

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

Text description of adlob131.gif follows
Text description of the illustration adlob131.gif


See Also:

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:

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

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


Note:

At the present time, OO4O only offers ISOPEN to test whether or not a BFILE is open (see "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

Text description of adlob132.gif follows
Text description of the illustration adlob132.gif


See Also:

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:

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

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

Text description of adlob110.gif follows
Text description of the illustration adlob110.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

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:

Scenario

These examples open and display BFILE data.

Examples

Examples are provided in six programmatic environments:

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

Text description of adlob107.gif follows
Text description of the illustration adlob107.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

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) 


Note:

The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled using polling or a callback. See Also: Chapter 10, "Internal Persistent LOBs", "Loading a LOB with Data From a BFILE", Usage Notes.


The Amount Parameter

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:

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

/* 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)

Text description of adlob106.gif follows
Text description of the illustration adlob106.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

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:

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)

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

Text description of adlob108.gif follows
Text description of the illustration adlob108.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

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:

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.


Note:

LOBMAXSIZE is set at 4 Gb so that you do not have to find out the length of each BFILE before beginning the comparison.


Examples

Examples are provided in these five programmatic environments:

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

Text description of adlob109.gif follows
Text description of the illustration adlob109.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

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:

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

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

Text description of adlob104.gif follows
Text description of the illustration adlob104.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

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:

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

/* 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();
    }
  }
}

Getting the Length of a BFILE

Figure 12-23 Use Case Diagram: Getting the Length of a BFILE

Text description of adlob103.gif follows
Text description of the illustration adlob103.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

Purpose

This procedure describes how to get the length of a 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:

Scenario

This example gets the length of a BFILE that is associated with ad_graphic.

Examples

The examples are provided in six programmatic environments:

PL/SQL (DBMS_LOB Package): Getting the Length of a BFILE

/* Getting the length of a BFILE. [Example script: 4032.sql]
/* Procedure getLengthBFILE_proc is not part of DBMS_LOB package: */

CREATE OR REPLACE PROCEDURE getLengthBFILE_proc IS
   File_loc      BFILE;
   Length       INTEGER;
BEGIN
   /* Initialize the BFILE locator by selecting 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);
   /* Get the length of the LOB: */
   Length := DBMS_LOB.GETLENGTH(File_loc);
   IF Length IS NULL THEN
       DBMS_OUTPUT.PUT_LINE('BFILE is null.');
   ELSE
       DBMS_OUTPUT.PUT_LINE('The length is ' || length);
   END IF;
   /* Close the BFILE: */
   DBMS_LOB.CLOSE(File_loc);
END;

C (OCI): Getting the Length of a BFILE

/* Getting the length of a BFILE. [Example script: 4033.c] */
/* Select the lob/bfile from table Print_media */ 

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 BfileLength(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   ub4 len;
 
   /* 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, OCILobGetLength(svchp, errhp, bfile_loc, &len));

   printf("Length of bfile = %d\n", len);
 
   checkerr (errhp, OCILobFileClose(svchp, errhp, bfile_loc));

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

COBOL (Pro*COBOL): Getting the Length of a BFILE

     * Getting the length of a BFILE. [Example script: 4034.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-LENGTH.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  LEN            PIC S9(9) COMP.
       01  D-LEN          PIC 9(4).
       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-LENGTH.

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

      * Use LOB DESCRIBE to get length of lob: 
           EXEC SQL
                LOB DESCRIBE :BFILE1 GET LENGTH INTO :LEN END-EXEC.
           
           MOVE LEN TO D-LEN.
           DISPLAY "Length of BFILE is ", D-LEN.

       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 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++): Getting the Length of a BFILE

/* Getting the length of a BFILE. [Example script: 4035.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 getLengthBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  unsigned int Length = 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 = 3060 AND ad_id = 11001;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Get the Length: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  /* If the BFILE is NULL or unitialized, then Length is Undefined: */
  printf("Length is %d bytes\n", Length);
  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
} 

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

Visual Basic (OO4O): Getting the Length of a BFILE

'Getting the length of a BFILE. [Example script: 4037.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

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 "AdGraphic", 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.Size = 0 Then
    MsgBox "BFile size is 0"
Else
    MsgBox "BFile size is " & OraAdGraphic.Size
End If
OraDb.Connection.CommitTrans

Java (JDBC): Getting the Length of a BFILE

// Getting the length of a BFILE. [Example script: 4038.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();
    }
  }
}

Copying a LOB Locator for a BFILE

Figure 12-24 Use Case Diagram: Copying a LOB Locator for a BFILE

Text description of adlob133.gif follows
Text description of the illustration adlob133.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

Purpose

This procedure describes how to copy a LOB locator for a 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:

Scenario

This example assigns one BFILE locator to another related to ad_graphic.

Examples

The examples are provided in the following five programmatic environments:

PL/SQL: Copying a LOB Locator for a BFILE


Note:

Assigning one BFILE to another using PL/SQL entails using the "=" sign. This is discussed in more detail with regard to "Read Consistent Locators" in Chapter 5, "Large Objects: Advanced Topics".


/* Copying a LOB locator for a BFILE. [Example script: 4039.sql] */
/* Procedure BFILEAssign_proc is not part of DBMS_LOB package:   */

CREATE OR REPLACE PROCEDURE BFILEAssign_proc IS
   File_loc1    BFILE;
   File_loc2    BFILE;
BEGIN
   SELECT Photo INTO File_loc1 FROM print_media 
      WHERE Product_ID = 3060 AND ad_id = 11001 FOR UPDATE;
   /* Assign File_loc1 to File_loc2 so that they both refer to the same 
operating
      system file: */
   File_loc2 := File_loc1;
   /* Now you can read the bfile from either File_loc1 or File_loc2. */
END;

C (OCI): Copying a LOB Locator for a BFILE

/* Copying a LOB locator for a BFILE. [Example script: 4040.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 BfileAssign(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *src_loc; 
   OCILobLocator *dest_loc; 
 
   /* Allocate the locator descriptors */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &src_loc,
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);

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

   /* Select the bfile */ 
   selectLob(src_loc, errhp, svchp, stmthp); 

   checkerr(errhp, OCILobLocatorAssign(svchp, errhp, src_loc, &dest_loc));

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

COBOL (Pro*COBOL): Copying a LOB Locator for a BFILE

     * Copying a LOB locator for a BFILE. [Example script: 4041.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-COPY-LOCATOR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  BFILE2         SQL-BFILE.
       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.
       BILFE-COPY-LOCATOR.

           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 ALLOCATE :BFILE2 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 SQLLOB ASSIGN :BFILE1 TO :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++): Copying a LOB Locator for a BFILE

/* Copying a LOB locator for a BFILE. [Example script: 4042.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 BFILEAssign_proc()
{
  OCIBFileLocator *Lob_loc1, *Lob_loc2;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT ad_graphic INTO :Lob_loc1
           FROM Print_media WHERE product_id = 2056 AND ad_id = 12001;
  /* Assign Lob_loc1 to Lob_loc2 so that they both refer to the same
     operating system file:  */
  EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2;
  /* Now you can read the BFILE from either Lob_loc1 or Lob_loc2 */
}

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

Java (JDBC): Copying a LOB Locator for a BFILE

// Copying a LOB locator for a BFILE. [Example script: 4044.java]

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_81
{
  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 = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media 
                WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          lob_loc1 = ((OracleResultSet)rset).getBFILE (1);
       }

       // Assign lob_loc1 to lob_loc2 so that they both refer 
       // to the same operating system file.
       // Now the BFILE can be read through either of the locators: 
       lob_loc2 = lob_loc1;
       stmt.close();
       conn.commit();
       conn.close();
  }
    //catch (SQLException e)
    catch (Exception e)
    {
        e.printStackTrace();
    }
  }
}

Determining If a LOB Locator for a BFILE Is Initialized

Figure 12-25 Use Case Diagram: Determining If a LOB Locator Is Initialized

Text description of adlob135.gif follows
Text description of the illustration adlob135.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

Purpose

This procedure describes how to determine if a BFILE LOB locator is initialized.

Usage Notes

On the client side, before you call any OCILob* interfaces (such as OCILobWrite), or any programmatic environments that use OCILob* interfaces, first initialize the LOB locator, using a SELECT, for example.

If your application requires a locator to be passed from one function to another, you may want to verify that the locator has already been initialized. If the locator is not initialized, you could design your application either to return an error or to perform the SELECT before calling the OCILob* interface.

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:

Scenario

Not applicable.

Examples

The examples are provided in the following programmatic environments:

C (OCI): Determining If a LOB Locator for a BFILE Is Initialized

/* Determining if a LOB locator  for a BFILE is initialized. 
   [Example script: 4045.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 BfileIsInit(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   boolean is_init;
 
   /* Allocate the locator descriptors */ 
   (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, OCILobLocatorIsInit(envhp, errhp, bfile_loc, &is_init));

   if (is_init == TRUE)
   {
     printf("Locator is initialized\n");
   }
   else
   {
     printf("Locator is not initialized\n");
   }
   /* Free the locator descriptor */ 
   OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

C/C++ (Pro*C/C++): Determining If a LOB Locator for a BFILE Is Initialized

/* Determining if a LOB locator  for a BFILE is initialized.
   [Example script: 4046.pc]
   Pro*C/C++ has no form of embedded SQL statement to determine if a BFILE
   locator is initialized. Locators in Pro*C/C++ are initialized when they
   are allocated with the EXEC SQL ALLOCATE statement. However, an example
   can be written that uses embedded SQL and the OCI as shown here: */

#include <sql2oci.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 BFILELocatorIsInit_proc()
{
  OCIBFileLocator *Lob_loc;
  OCIEnv *oeh;
  OCIError *err;
  boolean isInitialized = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Mtab.ad_graphic INTO :Lob_loc
           FROM Print_media PMtab 
           WHERE PMtab.product_id = 2056 AND ad_id = 12001;
  /* Get the OCI Environment Handle using a SQLLIB Routine: */
  (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh);
  /* Allocate the OCI Error Handle: */
  (void) OCIHandleAlloc((dvoid *)oeh, (dvoid **)&err,
                        (ub4)OCI_HTYPE_ERROR, (ub4)0, (dvoid **)0);
  /* Use the OCI to determine if the locator is Initialized: */
  (void) OCILobLocatorIsInit(oeh, err, Lob_loc, &isInitialized);
  if (isInitialized)
    printf("Locator is initialized\n");
  else
    printf("Locator is not initialized\n");
  /* Note that in this example, the locator is initialized: */
  /* Deallocate the OCI Error Handle: */
  (void) OCIHandleFree(err, OCI_HTYPE_ERROR);
  /* Release resources held by the locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Determining If One LOB Locator for a BFILE Is Equal to Another

Figure 12-26 Use Case Diagram: Determining If One LOB Locator for a BFILE Is Equal to Another

Text description of adlob134.gif follows
Text description of the illustration adlob134.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

Purpose

This procedure describes how to see if one BFILE LOB locator is equal to another.

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:

Scenario

If two locators are equal, this means that they refer to the same version of the LOB data (see "Read Consistent Locators" in Chapter 5, "Large Objects: Advanced Topics").

Examples

The examples are provided in the following three programmatic environments:

C (OCI): Determining If One LOB Locator for a BFILE Is Equal to Another

/* Determining if one LOB locator for a BFILE is equal to another */
/* [Example script: 4047.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 BfileIsEqual(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc1; 
   OCILobLocator *bfile_loc2; 
   boolean  is_equal;
 
   /* Allocate the locator descriptors */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc1,
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);

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

   /* Select the bfile */ 
   selectLob(bfile_loc1, errhp, svchp, stmthp); 

   checkerr(errhp,
            OCILobLocatorAssign(svchp, errhp, bfile_loc1, &bfile_loc2));

   checkerr(errhp, OCILobIsEqual(envhp, bfile_loc1, bfile_loc2, &is_equal));

   if (is_equal == TRUE)
   {
     printf("Locators are equal\n");
   }
   else
   {
     printf("Locators are not equal\n");
   }

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

C/C++ (Pro*C/C++): Determining If One BFILE LOB Locator Is Equal to Another

/* Determining if one LOB locator for a BFILE is equal to another */
   [Example script: 4048.pc]                                       
   Pro*C/C++ does not provide a mechanism to test the equality of two
   locators However, by using the OCI directly, two locators can be
   compared to determine whether or not they are equal as this example
   demonstrates: */

#include <sql2oci.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 BFILELocatorIsEqual_proc()
{
  OCIBFileLocator *Lob_loc1, *Lob_loc2;
  OCIEnv *oeh;
  boolean isEqual = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT ad_graphic INTO :Lob_loc1
           FROM Print_media WHERE product_id = 2056 AND ad_id = 12001;
  EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2;
  /* Now you can read the BFILE from either Lob_loc1 or Lob_loc2 */
  /* Get the OCI Environment Handle using a SQLLIB Routine: */
  (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh);
  /* Call OCI to see if the two locators are Equal: */
  (void) OCILobIsEqual(oeh, Lob_loc1, Lob_loc2, &isEqual);
  if (isEqual)
    printf("Locators are equal\n");
  else
    printf("Locators are not equal\n");
  /* Note that in this example, the LOB locators will be Equal: */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
}

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

Java (JDBC): Determining If One LOB Locator for a BFILE Is Equal to Another

// Determining if one LOB locator for a BFILE is equal to another 
// [Example script: 4050.java]                                    

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

  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 = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media 
                  WHERE product_id = 3106 AND ad_id = 13001");
      if (rset.next())
      {
          lob_loc1 = ((OracleResultSet)rset).getBFILE (1);
      }

      // Set both LOBS to reference the same BFILE: 
      lob_loc2 = lob_loc1;

      // Note that in this example, the Locators will be equal: 
      if (lob_loc1.equals(lob_loc2))  
      {
         // The Locators are equal: 
         System.out.println("The BFILEs are equal");
      }
      else 
      {
         // The Locators are different: 
         System.out.println("The BFILEs are NOT equal");
      }

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

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

Getting DIRECTORY Alias and Filename

Figure 12-27 Use Case Diagram: Get DIRECTORY Alias and Filename

Text description of adlob136.gif follows
Text description of the illustration adlob136.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

Purpose

This procedure describes how to get DIRECTORY alias and filename.

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:

Scenario

This example retrieves the DIRECTORY alias and filename related to the BFILE, ad_graphic.

Examples

The examples are provided in the following six programmatic environments:

PL/SQL (DBMS_LOB Package): Getting Directory Alias and Filename

/* Getting the directory alias and filename [Example script: 4051.sql] */

CREATE OR REPLACE PROCEDURE getNameBFILE_proc IS
   File_loc         BFILE;
   DirAlias_name   VARCHAR2(30);
   File_name       VARCHAR2(40);
BEGIN
   SELECT ad_graphic INTO File_loc FROM Print_media 
         WHERE product_id = 3060 AND ad_id = 11001;
   DBMS_LOB.FILEGETNAME(File_loc, DirAlias_name, File_name);
   /* do some processing based on the directory alias and file names */
END;

C (OCI): Getting Directory Alias and Filename

/* Getting the directory alias and filename [Example script: 4052.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";

     /* 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 BfileGetDirFile(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   OraText dir_alias[32] = NULL;
   OraText filename[256]  = NULL;
   ub2 d_length = 32;
   ub2 f_length = 256;
 
   /* Allocate the locator descriptors */ 
   (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, OCILobFileGetName(envhp, errhp, bfile_loc,
                       dir_alias, &d_length, filename, &f_length));

   printf("Directory Alias : [%s]\n", dir_alias);
   printf("File name : [%s]\n", filename);

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

COBOL (Pro*COBOL): Getting Directory Alias and Filename

d     * Getting the directory alias and filename [Example script: 4053.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-DIR-ALIAS.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) 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-DIR-ALIAS.

           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.

      * Populate the BFILE locator: 
           EXEC SQL 
                SELECT AD_GRAPHIC INTO :BFILE1
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001
           END-EXEC.

      * Use the LOB DESCRIBE functionality to get 
      * the directory alias and the filename: 
           EXEC SQL LOB DESCRIBE :BFILE1 
                GET DIRECTORY, FILENAME INTO :DIR-ALIAS, :FNAME END-EXEC.
      
           DISPLAY "DIRECTORY: ", DIR-ALIAS-ARR, "FNAME: ", FNAME-ARR.
       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 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++): Getting Directory Alias and Filename

/* Getting the directory alias and filename [Example script: 4054.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 getBFILEDirectoryAndFilename_proc()
{
  OCIBFileLocator *Lob_loc;
  char Directory[31], Filename[255];
  /* Datatype Equivalencing is Optional: */
  EXEC SQL VAR Directory IS STRING;
  EXEC SQL VAR Filename IS STRING;
  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;
  /* Get the Directory Alias and Filename: */
  EXEC SQL LOB DESCRIBE :Lob_loc
     GET DIRECTORY, FILENAME INTO :Directory, :Filename;

  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  printf("Directory Alias: %s\n", Directory);
  printf("Filename: %s\n", Filename);
  /* Release resources held by the locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Getting Directory Alias and Filename

'Getting the directory alias and filename [Example script: 4056.txt]
'The PL/SQL packages and tables mentioned here are not part of the
'standard OO4O installation:

Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraAdGraphic1 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 OraAdGraphic1 = OraParameters("MyAdGraphic").Value
'Get Directory alias and filename: 
MsgBox " Directory alias is " & OraAdGraphic1.DirectoryName & 
   " Filename is " & OraAdGraphic1.filename

OraDb.Connection.CommitTrans

Java (JDBC): Getting Directory Alias and Filename

// Getting the directory alias and filename [Example script: 4057.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();
    }
  }
}

Updating a BFILE Using BFILENAME()

Figure 12-28 Use Case Diagram: Updating a BFILE Using BFILENAME()

Text description of adlob118.gif follows
Text description of the illustration adlob118.gif


See Also:

Usage Notes

BFILENAME() Function

The BFILENAME() function can be called as part of SQL INSERT or UPDATE to initialize a BFILE column or attribute for a particular row by associating it with a physical file in the server's file system.

The DIRECTORY object represented by the directory_alias parameter to this function need not already be defined using SQL DDL before the BFILENAME() function is called in SQL DML or a PL/SQL program. However, the directory object and operating system file must exist by the time you actually use the BFILE locator (for example, as having been used as a parameter to an operation such as OCILobFileOpen(), DBMS_LOB.FILEOPEN(), OCILobOpen(), or DBMS_LOB.OPEN()).

Note that 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 the BFILENAME() function.

You can use BFILENAME() as part of a SQL INSERT and UPDATE statement to initialize a BFILE column. You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias or filename does not exist, then PL/SQL DBMS_LOB 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

FUNCTION BFILENAME(directory_alias IN VARCHAR2,
                  filename IN VARCHAR2)
RETURN BFILE;

See Also:

"DIRECTORY Name Specification" for information about the use of uppercase letters in the directory name, and OCILobFileSetName() in Oracle Call Interface Programmer's Guide for an equivalent OCI based routine.

Use the following syntax references:

Scenario

This example updates the Print_media table by means of the BFILENAME function.

Examples

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

SQL: Updating a BFILE by means of BFILENAME()

/* Updating a BFILE using BFILENAME()  [Example script: 4059.sql] */

UPDATE Print_media 
SET ad_graphic = BFILENAME('ADGRAPHIC_DIR', 'keyboard_graphic_3106_13001') 
    WHERE product_id = 3106 AND ad_id = 13001;


Updating a BFILE by Selecting a BFILE From Another Table

Figure 12-29 Use Case Diagram: Updating a BFILE by Selecting a BFILE From Another Table

Text description of adlob117.gif follows
Text description of the illustration adlob117.gif


See Also:

Purpose

This procedure describes how to UPDATE a BFILE by selecting a BFILE from another table.

Usage Notes

There is no copy function for BFILEs, so you have to use UPDATE as SELECT if you want to copy a BFILE from one location to another. Because BFILEs use reference semantics instead of copy semantics, only the BFILE locator is copied from one row to another row. This means that you cannot make a copy of an external LOB value without issuing an operating system command to copy the operating system file.

Syntax

Use the following syntax references:

Scenario

This example updates the table, Voiceover_tab by selecting from the archival storage table, VoiceoverLib_tab./

Examples

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

SQL: Updating a BFILE by Selecting a BFILE From Another Table

UPDATE Adheader_tab
    SET (header_name, creation_date, header_text, logo) =
       (SELECT * FROM AdheaderLib_tab AHtab 
        WHERE AHtab.creation_date = '08/08/2001');

Updating a BFILE by Initializing a BFILE Locator

Figure 12-30 Use Case Diagram: Updating a BFILE by Initializing a BFILE Locator

Text description of adlob119.gif follows
Text description of the illustration adlob119.gif


See Also:

Purpose

This procedure describes how to UPDATE a BFILE by initializing a BFILE locator.

Usage Notes

You must initialize the BFILE locator bind variable to a directory alias and filename before issuing the update statement.

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:

Scenario

Not applicable.

Examples

The examples are provided in six programmatic environments:

PL/SQL: Updating a BFILE by Initializing a BFILE Locator

/* Updating a BFILE by initializing a BFILE locator. [Example script:4061.sql]
   Procedure updateUseBindVariable_proc is not part of DBMS_LOB package: */
   
CREATE OR REPLACE PROCEDURE updateUseBindVariable_proc (File_loc BFILE) IS
BEGIN
   UPDATE Print_media SET ad_graphic = File_loc 
         WHERE product_id = 3060 AND ad_id = 11001;
END;

DECLARE
   File_loc  BFILE;
BEGIN
   SELECT ad_graphic INTO File_loc
      FROM Print_media
         WHERE product_id = 3060 AND ad_id = 11001;
   updateUseBindVariable_proc (File_loc);
   COMMIT;
END;

C (OCI): Updating a BFILE by Initializing a BFILE Locator

/* Updating a BFILE by initializing a BFILE locator. [Example script: 4062.c] */
void BfileUpdate(envhp, errhp, svchp, stmthp) 
OCIEnv    *envhp; 
OCIError  *errhp; 
OCISvcCtx *svchp; 
OCIStmt   *stmthp; 
{ 
  OCILobLocator *Lob_loc; 
  OCIBind *bndhp, *bndhp2; 

  text  *updstmt =  
    (text *) "UPDATE Print_media SET ad_graphic = :Lob_loc 
              WHERE product_id = 3106 AND ad_id = 13001"; 

  OraText *Dir = (OraText *)"ADGRAPHIC_DIR", 
          *Name = (OraText *)"keyboard_graphic_3106_13001"; 
 
  /* Prepare the SQL statement: */ 
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, updstmt,  (ub4)  
                                  strlen((char *) updstmt), 
                                  (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)
     || OCIBindByPos(stmthp, &bndhp2, errhp, (ub4) 2, 
                                (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): Updating a BFILE by Initializing a BFILE Locator

     * Updating a BFILE by initializing a BFILE locator. [Example script: 
4063.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-UPDATE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  BFILE-IND      PIC S9(4) COMP.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) 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-UPDATE.

           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.

      * Populate the BFILE: 
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC ORACLE OPTION (SELECT_ERROR=NO) END-EXEC.
           EXEC SQL 
                SELECT AD_GRAPHIC INTO :BFILE1:BFILE-IND
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060 
                AND AD_ID = 13001 END-EXEC.

      * Make graphic associated with product_id=3106 same as product_id=3060
      * and ad_id = 13001: 
           EXEC SQL 
                UPDATE PRINT_MEDIA SET AD_GRAPHIC = :BFILE1:BFILE-IND
                WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC.
        
      * Free the BFILE: 
       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++): Updating a BFILE by Initializing a BFILE Locator

/* Updating a BFILE by initializing a BFILE locator. [Example script: 4064.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 updateUseBindVariable_proc(Lob_loc)
  OCIBFileLocator *Lob_loc;
{
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL UPDATE Print_media SET ad_graphic = :Lob_loc 
       WHERE product_ID = 2056 AND ad_id = 12001;
}

void updateBFILE_proc()
{
  OCIBFileLocator *Lob_loc;

  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT ad_graphic INTO :Lob_loc
           FROM Print_media WHERE product_id = 2056 AND ad_id 12001;
  updateUseBindVariable_proc(Lob_loc);
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Updating a BFILE by Initializing a BFILE Locator

'Updating a BFILE by initializing a BFILE locator. [Example script:4066.txt]

Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraParameters As OraParameters, OraAdGraphic As OraBfile

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

OraDb.Connection.BeginTrans

Set OraParameters = OraDb.Parameters

'Define in out parameter of BFILE type: 
OraParameters.Add "MyAdGraphic", Null, ORAPARM_BOTH, ORATYPE_BFILE

'Define out parameter of BFILE type: 
OraDb.ExecuteSQL (
"BEGIN SELECT ad_graphic INTO :MyAdGraphic FROM Print_media 
     WHERE product_id = 2056 AND ad_id = 12001; 
      END;")
       
'Update the ad_graphic BFile for product_id=2056 AND ad_id = 12001 
      to product_id=2268 AND ad_id = 21001: 
OraDb.ExecuteSQL (
   "UPDATE Print_media SET ad_graphic = :MyAdGraphic 
      WHERE product_id = 2268 AND ad_id = 21001")

'Get Directory alias and filename
'MsgBox " Directory alias is " & OraAdGraphic1.DirectoryName & " Filename is " & 
OraAdGraphic1.filename

OraDb.Connection.CommitTrans

Java (JDBC): Updating a BFILE by Initializing a BFILE Locator

// Updating a BFILE by initializing a BFILE locator. [Example script: 4067.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_100
{

  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;
        OraclePreparedStatement pstmt = null;

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

        // Prepare a CallableStatement to OPEN the LOB for READWRITE: 
        pstmt = (OraclePreparedStatement) conn.prepareStatement (
            "UPDATE Print_media SET ad_graphic = ? 
                WHERE product_id = 3060 AND ad_id = 11001");
        pstmt.setBFILE(1, src_lob);
        pstmt.execute();

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

Closing a BFILE with FILECLOSE

Figure 12-31 Use Case Diagram: Closing a BFILE with FILECLOSE

Text description of adlob129.gif follows
Text description of the illustration adlob129.gif


See Also:

Purpose

This procedure describes how to close a BFILE with FILECLOSE.

Usage Notes

Although closing a BFILE with FILECLOSE is still supported, we strongly recommend that you use CLOSE instead. Doing so 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:

Scenario

This example closes a BFILE in ADPHOTO_DIR.

Examples

PL/SQL (DBMS_LOB Package): Closing a BFile with FILECLOSE

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

C (OCI): Closing a BFile with FILECLOSE

/* Closing a BFILE with FILECLOSE.  [Example script: 4069.c] */

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

   OCILobLocator *bfile_loc; 
 
   /* Allocate the locator descriptors */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);

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

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

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

Visual Basic (OO4O): Closing a BFile with FILECLOSE


Note:

At the present time, OO4O only offers BFILE closing with CLOSE.



Java (JDBC): Closing a BFile with FILECLOSE

// Closing a BFILE with FILECLOSE.  [Example script: 4071.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','keyboard_graphic_3106_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);

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

Closing a BFILE with CLOSE

Figure 12-32 Use Case Diagram: Closing an Open BFILE with CLOSE

Text description of adlob130.gif follows
Text description of the illustration adlob130.gif


See Also:

Purpose

This procedure describes how to close a BFILE with CLOSE.

Usage Notes

Use CLOSE in conjunction with OPEN.

See:

Opening a BFILE with OPEN

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:

Scenario

These examples close a BFILE in ADGRAPHIC_DIR.

Examples

PL/SQL (DBMS_LOB Package): Closing a BFile with CLOSE

/* Closing a BFILE with CLOSE.  [Example script: 4072.sql]
   Procedure closeBFILE_procTwo is not part of DBMS_LOB package: */
   
CREATE OR REPLACE PROCEDURE closeBFILE_procTwo IS
   File_loc  BFILE := BFILENAME('ADGRAPHIC_DIR','keyboard_graphic_3060_11001');
BEGIN
   DBMS_LOB.OPEN(File_loc, DBMS_LOB.LOB_READONLY);
   /* ...Do some processing. */
   DBMS_LOB.CLOSE(File_loc);
END;

C (OCI): Closing a BFile with CLOSE

/* Closing a BFILE with CLOSE.  [Example script: 4073.c] */

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

   OCILobLocator *bfile_loc; 
 
   /* Allocate the locator descriptors */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,
                    (ub4) OCI_DTYPE_FILE,  
                    (size_t) 0, (dvoid **) 0);

   checkerr(errhp, OCILobFileSetName(envhp, errhp, &bfile_loc,
                    (OraText *) "ADGRAPHIC_DIR", (ub2) strlen("ADGRAPHIC_DIR"),
                    (OraText *) "keyboard_3106",
                    (ub2) strlen("keyboard_3106")));

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

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

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

COBOL (Pro*COBOL): Closing a BFILE with CLOSE

     * Closing a BFILE with CLOSE.  [Example script: 4074.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-CLOSE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

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

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

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

           EXEC SQL
                LOB OPEN :BFILE1 READ ONLY END-EXEC.

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

      * And free the LOB locator: 
           EXEC SQL FREE :BFILE1 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++): Closing a BFile with CLOSE

/* Closing a BFILE with CLOSE.  [Example script: 4075.pc]
   Pro*C/C++ has only one form of CLOSE for BFILEs.  Pro*C/C++ has no
   FILECLOSE statement. A simple CLOSE statement is used instead: */

#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 closeBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  char *Dir = "ADGRAPHIC_DIR", *Name = "mousepad_graphic_2056_12001";

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  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;
  closeBFILE_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Closing a BFile with CLOSE

'Closing a BFILE with CLOSE.  [Example script: 4076.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&)

chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT)
Set OraAdGraphic = OraDyn.Fields("ad_graphic").Value
 
If OraAdGraphic.IsOpen Then
   'Process because the file is already open
   OraAdGraphic.Close
End If

Java (JDBC): Closing a BFile with CLOSE

// Closing a BFILE with CLOSE.  [Example script: 4077.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;

       rset = stmt.executeQuery (
       "SELECT BFILENAME('ADGRAPHIC_DIR', 'keyboard_graphic_3106_13001') FROM 
DUAL");
       OracleCallableStatement cstmt = null;
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);
     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: 
       cstmt = (OracleCallableStatement)
       conn.prepareCall ("begin dbms_lob.close(?); end;");
       cstmt.setBFILE(1,src_lob);
       cstmt.execute();
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}

Closing All Open BFILEs with FILECLOSEALL

Figure 12-33 Use Case Diagram: Closing All Open BFILEs

Text description of adlob112.gif follows
Text description of the illustration adlob112.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

It is the user's responsibility to close any opened file(s) after normal or abnormal termination of a PL/SQL program block or OCI program. So, for instance, for every DBMS_LOB.FILEOPEN() or DBMS_LOB.OPEN() call on a BFILE, there must be a matching DBMS_LOB.FILECLOSE() or DBMS_LOB.CLOSE() call. You should close open files before the termination of a PL/SQL block or OCI program, and also in situations that have raised errors. The exception handler should make provision to close any files that were opened before the occurrence of the exception or abnormal termination.

If this is not done, Oracle considers these files unclosed.

See Also:

"Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES"

Purpose

This procedure describes how to close all 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:

Scenario

Not applicable.

Examples

PL/SQL (DBMS_LOB Package): Closing All Open BFiles

/* Closing all open BFILEs.  [Example script: 4078.sql]
   Procedure closeAllOpenFilesBFILE_proc is not part of DBMS_LOB package: */
   
CREATE OR REPLACE PROCEDURE closeAllOpenFilesBFILE_proc IS
BEGIN
   /* Close all open BFILEs: */
   DBMS_LOB.FILECLOSEALL;
END;

C (OCI): Closing All Open BFiles

/* Closing all open BFILEs.  [Example script: 4079.c] */

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

   OCILobLocator *bfile_loc1;
   OCILobLocator *bfile_loc2;
 
   /* Allocate the locator descriptors */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc1,
                   (ub4) OCI_DTYPE_FILE,  
                   (size_t) 0, (dvoid **) 0);

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

   checkerr(errhp, OCILobFileSetName(envhp, errhp, &bfile_loc1,
                   (OraText *) "ADGRAPHIC_DIR", (ub2) strlen("ADGRAPHIC_DIR"),
                   (OraText *) "keyboard_graphic_3106_13001",
                   (ub2) strlen("keyboard_graphic_3106_13001")));

   checkerr(errhp, OCILobFileSetName(envhp, errhp, &bfile_loc2,
                   (OraText *) "ADGRAPHIC_DIR", (ub2) strlen("ADGRAPHIC_DIR"),
                   (OraText *) "monitor_graphic_3060_11001",
                   (ub2) strlen("monitor_graphic_306_11001")));

   checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc1,
                   (ub1) OCI_LOB_READONLY));

   checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc2,
                   (ub1) OCI_LOB_READONLY));

   checkerr(errhp, OCILobFileCloseAll(svchp, errhp));

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

COBOL (Pro*COBOL): Closing All Open BFiles

     * Closing all open BFILEs.  [Example script: 4080.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-CLOSE-ALL.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  BFILE2         SQL-BFILE.
       01  DIR-ALIAS1     PIC X(30) VARYING.
       01  FNAME1         PIC X(20) VARYING.
       01  DIR-ALIAS2     PIC X(30) VARYING.
       01  FNAME2         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-CLOSE-ALL.

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

      * Allocate the BFILEs: 
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.
           EXEC SQL ALLOCATE :BFILE2 END-EXEC.

      * Set up the directory and file information: 
           MOVE "ADGRAPHIC_DIR" TO DIR-ALIAS1-ARR.
           MOVE 9 TO DIR-ALIAS1-LEN.
           MOVE "keyboard_graphic_3106_13001" TO FNAME1-ARR.
           MOVE 16 TO FNAME1-LEN.
 
           EXEC SQL 
                LOB FILE SET :BFILE1
                DIRECTORY = :DIR-ALIAS1, FILENAME = :FNAME1 END-EXEC.
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.

      * Set up the directory and file information: 
           MOVE "ADGRAPHIC_DIR" TO DIR-ALIAS2-ARR.
           MOVE 9 TO DIR-ALIAS2-LEN.
           MOVE "mousepad_graphic_2056_12001" TO FNAME2-ARR.
           MOVE 13 TO FNAME2-LEN.
           EXEC SQL LOB FILE SET :BFILE2
                DIRECTORY = :DIR-ALIAS2, FILENAME = :FNAME2 END-EXEC.
           EXEC SQL LOB OPEN :BFILE2 READ ONLY END-EXEC.

      * Close both BFILE1 and BFILE2: 
           EXEC SQL LOB FILE CLOSE ALL 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++): Closing All Open BFiles

/* Closing all open BFILEs.  [Example script: 4081.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 closeAllOpenBFILEs_proc()
{
  OCIBFileLocator *Lob_loc1, *Lob_loc2;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  /* Populate the Locators: */
  EXEC SQL SELECT ad_graphic INTO :Lob_loc1
              FROM Print_media 
              WHERE product_id = 2056 AND ad_id = 12001;
  EXEC SQL SELECT Mtab.ad_graphic INTO Lob_loc2
              FROM Print_media PMtab 
              WHERE PMtab.product_id = 3060 AND ad_id = 11001;
  /* Open both BFILEs: */
  EXEC SQL LOB OPEN :Lob_loc1 READ ONLY;
  EXEC SQL LOB OPEN :Lob_loc2 READ ONLY;
  /* Close all open BFILEs: */
  EXEC SQL LOB FILE CLOSE ALL;
  /* Free resources held by the Locators: */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
}

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

Visual Basic (OO4O): Closing All Open BFiles

'Closing all open BFILEs.  [Example script: 4083.txt]

Dim OraParameters as OraParameters, OraAdGraphic as OraBFile 
OraConnection.BeginTrans 
 
Set OraParameters = OraDatabase.Parameters 
 
'Define in out parameter of BFILE type: 
OraParameters.Add "MyAdGraphic", Null,ORAPARAM_BOTH,ORATYPE_BFILE 
 
'Select the ad graphic BFile for product_id 2268: 
OraDatabase.ExecuteSQL("Begin SELECT ad_graphic INTO :MyAdGraphic FROM 
Print_media WHERE product_id = 2268 AND ad_id = 21001; END; " )  
 
'Get the BFile ad_graphic column: 
set OraAdGraphic = OraParameters("MyAdGraphic").Value 
 
'Open the OraAdGraphic: 
OraAdGraphic.Open 
 
'Do some processing on OraAdGraphic 
 
'Close all the BFILEs associated with OraAdGraphic: 
OraAdGraphic.CloseAll 

Java (JDBC): Closing All Open BFiles with FILECLOSEALL

// Closing all open BFILEs.  [Example script: 4084.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;
       OracleCallableStatement cstmt = null;
       rset = stmt.executeQuery (
          "SELECT ad_graphic FROM Print_media 
              WHERE product_id = 3106 AND ad_id = 13001");
       if (rset.next())
       {
          lob_loc1 = ((OracleResultSet)rset).getBFILE (1);
       }

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

       cstmt = (OracleCallableStatement) conn.prepareCall (
          "BEGIN DBMS_LOB.FILEOPEN(?,DBMS_LOB.LOB_READONLY); END;");
       // Open the first LOB: 
       cstmt.setBFILE(1, lob_loc1);
       cstmt.execute();

       cstmt = (OracleCallableStatement) conn.prepareCall (
          "BEGIN DBMS_LOB.FILEOPEN(?,DBMS_LOB.LOB_READONLY); END;");
       // Use the same CallableStatement to open the second LOB: 
       cstmt.setBFILE(1, lob_loc2);
       cstmt.execute();

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

       // Compare MAXBUFSIZE bytes starting at the first byte of 
       // both lob_loc1 and lob_loc2: 
       cstmt = (OracleCallableStatement) conn.prepareCall (
          "BEGIN ? := DBMS_LOB.COMPARE(?, ?, ?, 1, 1); END;");
       cstmt.registerOutParameter (1, Types.NUMERIC);
       cstmt.setBFILE(2, lob_loc1);
       cstmt.setBFILE(3, lob_loc2);
       cstmt.setInt(4, MAXBUFSIZE);
       cstmt.execute();
       int result = cstmt.getInt(1);
       System.out.println("Comparison result: " + Integer.toString(result));

       // Close all BFILEs:
       stmt.execute("BEGIN DBMS_LOB.FILECLOSEALL; END;");

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

Deleting the Row of a Table Containing a BFILE

Figure 12-34 Use Case Diagram: Deleting the Row of a Table Containing a BFILE

Text description of adlob021.gif follows
Text description of the illustration adlob021.gif


See Also:

Table 12-1, "Use Case Model: External LOBs (BFILEs)", for all basic operations of Internal Temporary LOBs

Purpose

This procedure describes how to DELETE the row of a table containing a BFILE.

Usage Notes

Unlike internal persistent LOBs, the LOB value in a BFILE does not get deleted by using SQL DDL or SQL DML commands -- only the BFILE locator is deleted. Deletion of a record containing a BFILE column amounts to de-linking that record from an existing file, not deleting the physical operating system file itself. An SQL DELETE statement on a particular row deletes the BFILE locator for the particular row, thereby removing the reference to the operating system file.

Syntax

See the following syntax reference:

Scenario

The following DELETE, DROP TABLE, or TRUNCATE TABLE statements delete the row, and hence the BFILE locator that refers to the advertisement graphic image for the product with product_id 3106 and ad_id 13001, but do not delete the operating system file for the graphic image.

Examples

The following examples are provided in SQL and apply to all programmatic environments:

SQL: Deleting a Row from a Table

/* Deleting the row of a table containing a BFILE  [Example script: 4085.sql] */

DELETE FROM Print_media 
   WHERE product_id = 3106 AND ad_id = 13001;


DROP TABLE Multimedia_tab;


TRUNCATE TABLE Multimedia_tab;



Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback