Loading a LOB with BFILE Data

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

Text description of the illustration adl12bf4.gif

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

Purpose

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

Usage Notes

Character Set Conversion

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

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

When you use the DBMS_LOB.LOADFROMFILE procedure to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. No implicit translation is performed from binary data to a character set.

Hence, when loading data into a CLOB or NCLOB from a BFILE ensure the following for the BFILE data before you use loadfromfile:

• BFILE data is in the same character set as the CLOB or NCLOB data already in the database, in other words, it is in the char/nchar character set

• BFILE data is encoded in the correct endian format of the server machine

  Note: If the CLOB or NCLOB database char/nchar character set is varying-width, then the data in the BFILE must contain ucs-2 character data because we store CLOB and NCLOB data in ucs-2 format when the database char/nchar char set is varying-width.

  See Also: Oracle9i Globalization and National Language Support Guide, for character set conversion issues.

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

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

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

Syntax

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

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

• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes and examples. Chapter 15, "Relational Functions" -- LOB Functions, OCILobLoadFromFile

• COBOL (Pro*COBOL) (Pro*COBOL Precompiler Programmer's Guide): Chapter 13, "Large Objects (LOBs)", "LOB Statements" for usage notes. Appendix F, "Embedded SQL and Precompiler Directives" -- LOB LOAD

• 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" -- LOB LOAD

• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > CopyFromBfile

• Java (JDBC) Oracle9i JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Further extensions are available in ( Oracle9i SQLJ Developer's Guide and Reference): Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

These example procedures assume there is a directory object (AUDIO_DIR) that contains the LOB data to be loaded into the target LOB (Music). Examples are provided in the following six programmatic environments:

Examples

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

• C (OCI): Loading a LOB with BFILE Data

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

• C/C++ (Pro*C/C++): Loading a LOB with BFILE Data

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

• Java (JDBC): Loading a LOB with BFILE Data

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

/* Note that the example procedure loadLOBFromBFILE_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('FRAME_DIR', 'Washington_frame');
   Amount         INTEGER := 4000;
BEGIN
    SELECT Frame INTO Dest_loc FROM Multimedia_tab 
       WHERE Clip_ID = 3 
          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

/* Insert a row using BFILENAME: */
/* Select the lob/bfile from the Multimedia table */ 
void selectLob(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc; 
OCIError *errhp; 
OCISvcCtx *svchp; 
OCIStmt *stmthp; 
{ 
     char  selstmt[150];
     OCIDefine *dfnhp;

     strcpy(selstmt, (char *)  "SELECT FRAME FROM MULTIMEDIA_TAB \
                                    WHERE CLIP_ID=3 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)); 
     /* 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 *) "PHOTO_DIR", (ub2) strlen("PHOTO_DIR"),
                    (text *) "Lincoln_photo", (ub2) strlen("Lincoln_photo")));

  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

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

       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 "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" 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 PHOTO INTO :SRC-BFILE
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 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

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

#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 = "AUDIO_DIR", *Name = "Washington_audio";
  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 Sound INTO :Dest_loc FROM Multimedia_tab
           WHERE Clip_ID = 3 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

Dim OraDyn as OraDynaset, OraDyn2 as OraDynaset, OraPhoto as OraBFile 
Dim OraImage as OraBlob

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

Set OraPhoto = OraDyn.Fields("Photo").Value
Set OraFrame = OraDyn.Fields("Frame").Value

OraDyn.Edit
'Load LOB with data from BFILE: 
OraFrame.CopyFromBFile (OraPhoto)
OraDyn.Update

Java (JDBC): Loading a LOB with BFILE Data

usage: head [-n #] [-#] [filename...]
public class

      // Commit the transaction: 
      conn.commit();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}