Loading a Temporary LOB with Data from a BFILE

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

Text description of the illustration adl11t21.gif

See: "Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs.

Purpose

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

Usage Notes

In using OCI, or any 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 operation 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.

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" -- CREATETEMPORARY, FREETEMPORARY

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

• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB LOAD

• Visual Basic (OO4O): There is no applicable syntax reference for this use case.

• Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Loading a LOB with Data from a BFILE" .

Scenario

The example procedures assume that there is an operating system source directory (AUDIO_DIR) that contains the LOB data to be loaded into the target LOB.

Examples

Examples are provided in the following programmatic environments:

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

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

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

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

• Visual Basic (OO4O): No example is provided with this release.

• Java (JDBC): No example is provided with this release.

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

/* Note that the example procedure freeTempLob_proc is not part of the 
   DBMS_LOB package: */

CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS
BEGIN
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);
   /* Use the temporary LOB locator here, then free it.*/
   /* Free the temporary LOB locator: */
   DBMS_LOB.FREETEMPORARY(Lob_loc);
   DBMS_OUTPUT.PUT_LINE('Temporary LOB was freed');
END;

C (OCI): Loading a Temporary LOB with Data from a BFILE

/* Here is a section of code which shows how to create a temporary LOB, and load 
   the contents of a BFILE into the temporary LOB: */

sb4 load_temp(OCIError *errhp,
              OCISvcCtx *svchp,
              OCIStmt   *stmthp,
              OCIEnv    *envhp)
{
  OCILobLocator *bfile;
  int amount =100;
  OCILobLocator *tblob;

  printf("in load_temp\n"); 
  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob,
                        (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in load_temp\n");
    return -1;
  }
  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&bfile,
                        (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in load_temp\n");
    return -1;
  }

  /* Create a temporary LOB: */
  if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, 
                           SQLCS_IMPLICIT, OCI_TEMP_BLOB, 
                           OCI_ATTR_NOCACHE, OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }
  
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"), (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED in load_temp\n");
    return -1;
  }

   /* Opening the BFILE is mandatory: */
   if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED for the bfile load_temp \n");
    return -1;
  }
 
   /* Opening the LOB is optional: */
   if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return -1;
  }

  if(OCILobLoadFromFile(svchp,
         errhp,
         tblob,
         (OCILobLocator*)bfile,
         (ub4)amount,
         (ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return -1;
  } 

  /* Close the lobs: */
  if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobClose FAILED for bfile \n");
    return -1;
  }

  checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob)));
  

  /* Free the temporary LOB now that we are done using it */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }
  }

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

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/cobol/tloadbf

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

       01  USERID   PIC X(11) VALUES "USER1/USER1".
       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 VALUE 10.
       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-TEMPORARY.

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

      * Allocate and initialize the BFILE and BLOB locators: 
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           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 :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
           
      * Close the LOBs: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-BLOB 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++): Loading a Temporary LOB with Data from a BFILE

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/proc/tload

#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 loadTempLobFromBFILE_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount = 4096;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the BFILE is mandatory; */
  /* Opening the LOB is optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Load the data from the BFILE into the Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Temp_loc;
  EXEC SQL LOB CLOSE :Lob_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Temp_loc;
  EXEC SQL FREE :Lob_loc;
}

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

}