External LOBs (BFILEs), 11 of 41

INSERT a Row Using BFILENAME()

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

Text description of adl12b32.gif follows

Text description of the illustration adl12b32.gif

See Also:

"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES).

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

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

OCILobFileOpen()
DBMS_LOB.FILEOPEN()
OCILobOpen()

DBMS_LOB.OPEN()






Note:



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

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

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

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

You can use BFILENAME() to initialize a BFILE locator variable in one of the programmatic interface programs, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then for example, 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. Use the following syntax references for each programmatic environment:

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

Scenario

Examples are provided in the following six programmatic environments:

Examples

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

SQL: Inserting a Row by means of BFILENAME()

/* 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 Multimedia_tab VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), 
  FILENAME('PHOTO_DIR', 'LINCOLN_PHOTO'),
  EMPTY_BLOB(), EMPTY_BLOB(),
  VOICED_TYP('Abraham Lincoln', EMPTY_CLOB(),'James Earl Jones', 1, NULL),        
        NULL, BFILENAME('AUDIO_DIR','LINCOLN_AUDIO'),
        MAP_TYP('Gettysburg', 23, 34, 45, 56,EMPTY_BLOB(), NULL));

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

/* Insert a row using BFILENAME: */
void insertUsingBfilename(svchp, stmthp, errhp)
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  text  *insstmt = 
     (text *) "INSERT INTO Multimedia_tab VALUES (3, EMPTY_CLOB(),  \
         EMPTY_CLOB(), BFILENAME('PHOTO_DIR', 'Lincoln_photo'),   \
         EMPTY_BLOB(), EMPTY_BLOB(), NULL,             \
         NULL, BFILENAME('AUDIO_DIR', 'Lincoln_audio'),   \
         MAP_TYP('Gettysburg', 23, 34, 45, 56, EMPTY_BLOB(), NULL))";
   
  /* 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()

This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/finsertn

       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 MULTIMEDIA_TAB (CLIP_ID, PHOTO)
                 VALUES (1, BFILENAME('PHOTO_DIR', 'LINCOLN_PHOTO'))
           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()

This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/proc/finsertn

#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 Multimedia_tab WHERE Clip_ID = 1;
  /* Insert a new row using the BFILENAME() function for BFILEs: */
  EXEC SQL INSERT INTO Multimedia_tab
    VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), 
            BFILENAME('PHOTO_DIR', 'Lincoln_photo'),
            EMPTY_BLOB(), EMPTY_BLOB(), NULL,
            InSeg_tab(InSeg_typ(1, NULL, 'Ted Koppell', 'Abraham Lincoln',
                      BFILENAME('AUDIO_DIR', 'Lincoln_audio'),
                      EMPTY_CLOB())),
            BFILENAME('AUDIO_DIR', 'Lincoln_audio'),
            Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(),
                    BFILENAME('PHOTO_DIR', 'Lincoln_photo')));
  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()

Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile

Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT)
Set OraMusic = OraDyn.Fields("Music").Value
Set OraPhoto = OraDyn.Fields("Photo").Value
OraDyn.AddNew
OraDyn.Fields("Clip_ID").value = 1
OraDyn.Fields("Story").value = Empty 'This is equivalent to EMPTY_BLOB() in SQL
OraDyn.Fields("FLSub").value = Empty
'Initialize BFile Data: 
OraPhoto.DirectoryName = "PHOTO_DIR"
OraPhoto.FileName = "LINCOLN_PHOTO"
OraDyn.Fields("Frame").Value = Empty
OraDyn.Fields("Sound").Value = Empty
'Initialize BFile Data: 
OraMusic.DirectoryName = "AUDIO_DIR"
OraMusic.FileName = "LINCOLN_AUDIO"
OraDyn.Update  
'Add the row to the table

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

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 multimedia_tab "
          +"VALUES (99, EMPTY_CLOB(), EMPTY_CLOB(), "
          +"BFILENAME ('PHOTO_DIR','Lincoln_photo'), "
          +"EMPTY_BLOB(), EMPTY_BLOB(), "
          +"(SELECT REF(Vref) FROM Voiceover_tab Vref "
           +"  WHERE Actor = 'James Earl Jones'), NULL, "
          +"BFILENAME('AUDIO_DIR', 'Lincoln_audio'), "
          +"MAP_TYP('Gettysburg', 23, 34, 45, 56, EMPTY_BLOB(), NULL))");
   // Commit the transaction: 
   conn.commit();
   stmt.close();
   conn.close();
    }
    catch (SQLException e)
    {
   e.printStackTrace();
    }
  }
}