Internal Persistent LOBs, 13 of 42

Load a LOB with Data from a BFILE

Figure 9-16 Use Case Diagram: Load a LOB with Data from a BFILE

See:
"Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs.

Purpose

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

Usage Notes

Binary Data to Character Set Conversion is Needed on BFILE Data

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. However, no implicit translation is ever performed from binary data to a character set.

When you use the LOADFROMFILE procedure to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing LOADFROMFILE.

Specify Amount to be Less than the Size of BFILE!

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

Syntax

See Chapter 3, "LOB 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 Package): Oracle8i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- LOADFROMFILE.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OciLobLoadFromFile.
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Precompiler Directives" -- LOB LOAD, LOB OPEN, CLOB CLOSE
C/C++ (Pro*C): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOAD
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > CopyFromBFILE and select OO4O Automation Server > OBJECTS > OraDynaset, OraDatabase, OraConnection
Java (JDBC): Oracle8i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle8i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The examples assume that there is an operating system source file (Washington_audio) that contains LOB data to be loaded into the target LOB (Music). The examples also assume that directory object AUDIO_DIR already exists and is mapped to the location of the source file.

Examples

Examples are provided in the following programmatic environments:

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

/* 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 source BFILE 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): Load a LOB with Data from a BFILE

/* This example illustrates how to select a BLOB from Multimedia_tab and load it with data from a BFILE */

sb4 select_lock_frame_locator_3(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
     (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=3 FOR UPDATE";
  OCIDefine *defnp1;
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
    checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));
  /* Execute the select and fetch one row */
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT));
    return 0;
}

void LoadLobDataFromBFile(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *bfile;
  OCILobLocator *blob;
  ub4            amount= 4000;

  /* Allocate the Source (bfile) & destination (blob) locators desriptors*/
  OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&bfile, 
                     (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid **)0);
  OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&blob,
                     (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);

  /* Select a frame locator for update */
  printf (" select the frame locator...\n");
  select_lock_frame_locator_3(blob, errhp, svchp, stmthp);
  
  /* Set the Directory Alias and File Name of the frame file */
  printf ("  set the file name in bfile\n");
  checkerr (errhp, OCILobFileSetName(envhp, errhp, &bfile, (text*)"FRAME_DIR",
                                     (ub2)strlen("FRAME_DIR"),
                                     (text*)"Washington_frame",
                                     (ub2)strlen("Washington_frame")));
    printf (" open the bfile\n");
  /* Opening the BFILE locator is Mandatory */
  checkerr (errhp, (OCILobOpen(svchp, errhp, bfile, OCI_LOB_READONLY)));

  printf("  open the lob\n");
  /* Opening the BLOB locator is optional */
  checkerr (errhp, (OCILobOpen(svchp, errhp, blob, OCI_LOB_READWRITE)));

  /* Load the data from the audio file (bfile) into the blob */
  printf (" load the LOB from File\n");
  checkerr (errhp, OCILobLoadFromFile(svchp, errhp, blob, bfile, (ub4)amount,
                                      (ub4)1, (ub4)1));

  /* Closing the LOBs is Mandatory if they have been Opened */
  checkerr (errhp, OCILobClose(svchp, errhp, bfile));
  checkerr (errhp, OCILobClose(svchp, errhp, blob));

  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *) bfile, (ub4) OCI_DTYPE_FILE);
  (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB);

  return;
}

COBOL (Pro*COBOL): Load a LOB with Data from a BFILE

       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-LOAD.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  DEST           SQL-BLOB.
       01  BFILE1         SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
      * Declare the amount to load.  The value here
      * was chosen arbitrarily
       01  LOB-AMT        PIC S9(9) COMP VALUE 10.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
       LOB-LOAD.

           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.

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

           EXEC SQL 
              LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS,FILENAME = :FNAME 
           END-EXEC.

      * Allocate and initialize the destination BLOB
           EXEC SQL ALLOCATE :DEST END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT SOUND INTO :DEST
              FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 FOR UPDATE END-EXEC.
  
      * Open the source BFILE for READ
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.

      * Open the destination BLOB for READ/WRITE 
           EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC.

      * Load the destination BLOB from the source BFILE
           EXEC SQL LOB LOAD :LOB-AMT FROM FILE :BFILE1 INTO :DEST END-EXEC.

      * Close the source and destination LOBs
           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.
           EXEC SQL LOB CLOSE :DEST END-EXEC.
       END-OF-BLOB.
           EXEC SQL FREE :DEST 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.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C): Load a LOB with Data from a BFILE

#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 = "FRAME_DIR", *Name = "Washington_frame";
  int Amount = 4000;

  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 frame 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): Load a LOB with Data from a BFILE

Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraMyBfile as OraBFile

OraConnection.BeginTrans
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT)
Set OraSound1 = OraDyn.Fields("Sound").Value

OraDb.Parameters.Add "id", 1,ORAPARAM_INPUT
OraDb.Parameters.Add "mybfile", Null,ORAPARAM_OUTPUT
OraDb.Parameters("mybfile").serverType = ORATYPE_BFILE

OraDb.ExecuteSQL ("begin  GetBFile(:id, :mybfile); end;")

Set OraMyBFile = OraDb.Parameters("mybfile").Value
'Go to Next row
OraDyn.MoveNext

OraDyn.Edit
'Lets update OraSound1 data with that from the BFILE
OraSound1.CopyFromBFile  OraMyBFile
OraDyn.Update

OraConnection.CommitTrans

Java (JDBC): Load a LOB with Data from a BFILE

// 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 Ex2_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;
     BLOB dest_lob = null;
     InputStream in = null;
     OutputStream out = null;
     byte buf[] = new byte[1000];
     ResultSet rset = null;

       rset = stmt.executeQuery (
          "SELECT BFILENAME('AUDIO_DIR', 'Washington_audio') FROM DUAL");
   if (rset.next())
   {
      src_lob = ((OracleResultSet)rset).getBFILE (1);
      src_lob.openFile();
      in = src_lob.getBinaryStream();
   }

       rset = stmt.executeQuery (
          "SELECT sound FROM multimedia_tab WHERE clip_id = 99 FOR UPDATE");
   if (rset.next())
   {
      dest_lob = ((OracleResultSet)rset).getBLOB (1);

      // Fetch the output stream for dest_lob: 
        out = dest_lob.getBinaryOutputStream();
   }

   int length = 0;
   int pos = 0;
   while ((in != null) && (out != null) && ((length = in.read(buf)) != -1)) 
   {
      System.out.println(
         "Pos = " + Integer.toString(pos) + ".  Length = " +
         Integer.toString(length));
      pos += length;
      out.write(buf, pos, length);
   }

   // Close all streams and file handles: 
   in.close();
   out.flush();
   out.close();
   src_lob.closeFile();

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