Create a Temporary LOB

Figure 10-3 Use Case Diagram: Create a Temporary LOB

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

Purpose

This procedure describes how to create a temporary LOB.

Usage Notes

A temporary LOB is empty when it is created.

Temporary LOBs do not support the EMPTY_BLOB()or EMPTY_CLOB() functions that are supported for permanent LOBs. The EMPTY_BLOB() function specifies the fact that the LOB is initialized, but not populated with any data.

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): Oracle8i 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, OCILobCreateTemporary

• COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide: Appendix F, "Embedded SQL and Precompiler Directives" -- LOB DESCRIBE, LOB COPY

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

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

• Java (JDBC): There is no applicable syntax reference for this use case.

Scenario

These examples read in a single video Frame from the Multimedia_tab table. Then they create a temporary LOB to be used to convert the video image from MPEG to JPEG format. The temporary LOB is read through the CACHE, and is automatically cleaned up at the end of the user's session, if it is not explicitly freed sooner.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Create a Temporary LOB

• C (OCI): Create a Temporary LOB

• COBOL (Pro*COBOL): Create a Temporary LOB

• C/C++ (Pro*C/C++): Create a Temporary LOB

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

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

PL/SQL (DBMS_LOB Package): Create a Temporary LOB

Note: You may need to set up the following data structures for certain examples to work: CREATE TABLE long_raw_tab (id number, long_raw_col long raw); INSERT INTO long_raw_tab VALUES (1,HEXTORAW('7D')); INSERT INTO multimedia_tab (clip_id,frame) SELECT id,TO_LOB(long_raw_col) FROM long_raw_tab;

Note: The DBMS_LOB.CREATETEMPORARY procedure takes an optional duration parameter. In PL/SQL, this duration parameter is used only as a hint about the duration of the LOB data. PL/SQL calculates the duration of the LOB data internally, taking into account your hint. You do not have to specify the duration of the LOB data!

DECLARE
   Dest_loc       BLOB;
   Src_loc        BLOB;
   Amount         INTEGER := 4000;
BEGIN
   SELECT Frame INTO Src_loc FROM Multimedia_tab WHERE Clip_ID = 1;
    /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   /* Copy the entire frame from the Src_loc to the Temporary Lob: */
   DBMS_LOB.COPY(Dest_loc,Src_loc,DBMS_LOB.GETLENGTH(Src_loc),1,1);
   DBMS_LOB.FREETEMPORARY(Dest_loc);
END;

C (OCI): Create a Temporary LOB

/* This function reads in a single video Frame from the Multimedia_tab table.
  Then it creates a temporary LOB so that we can use the temporary LOB to 
  convert the video image from MPEG to JPEG format.. The Temporary LOB which is 
  created will be read through the CACHE, and it will be automatically cleaned 
  up at the end of the user's session, if it is not explicitly freed sooner. 
  This function returns 0 if it completes successfully, and -1 if it fails: */
sb4 select_and_createtemp (OCILobLocator *lob_loc, 
                           OCIError      *errhp, 
                           OCISvcCtx     *svchp,
                           OCIStmt       *stmthp,
                           OCIEnv        *envhp) 
{
  OCIDefine     *defnp1;
  OCIBind       *bndhp;
  text          *sqlstmt;
  int rowind =1;
  ub4 loblen = 0;
  OCILobLocator *tblob;
  printf ("in select_and_createtemp \n");
  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob,
                        (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0))
  {
    printf("failed in OCIDescriptor Alloc in select_and_createtemp \n");
    return -1;
  }

  /* Arbitrarily select where Clip_ID =1: */
  sqlstmt = (text *)
     "SELECT Frame FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE";

  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                     (ub4) strlen((char *)sqlstmt),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
      (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
      return -1;
  }

  /* Define for BLOB: */
  if (OCIDefineByPos(stmthp, 
             &defnp1, errhp, (ub4) 1, (dvoid *) &lob_loc, (sb4)0, 
             (ub2) SQLT_BLOB, (dvoid *) 0, (ub2 *) 0, 
             (ub2 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: Select locator: OCIDefineByPos()\n");
    return -1;
  }
  /* Execute the select and fetch one row: */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                     (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
    return -1;
  }
  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 (OCILobGetLength(svchp, errhp, lob_loc, &loblen) != 0)
  {
    printf("OCILobGetLength FAILED\n");
    return -1;
  }
  if (OCILobCopy(svchp, errhp, tblob,lob_loc,(ub4)loblen, (ub4) 1, (ub4) 1))
  {
    printf( "OCILobCopy FAILED \n");
  }   
  if(OCILobFreeTemporary(svchp,errhp,tblob))
  {
    printf ("FAILED: OCILobFreeTemporary call \n");
    return -1;
  }
    
  return 0;
  }

COBOL (Pro*COBOL): Create a Temporary LOB

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

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  TEMP-BLOB      SQL-BLOB.
       01  LEN            PIC S9(9) COMP.
       01  D-LEN          PIC 9(9).
       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.
       CREATE-TEMPORARY.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locators: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC ORACLE OPTION (SELECT_ERROR=NO) END-EXEC.
           EXEC SQL 
                SELECT FRAME INTO :BLOB1
                FROM MULTIMEDIA_TAB 
                WHERE CLIP_ID = 1
           END-EXEC.
 
      * Get the length of the persistent BLOB: 
           EXEC SQL 
                LOB DESCRIBE :BLOB1
                GET LENGTH INTO :LEN
           END-EXEC.
     
      * Copy the entire length from persistent to temporary: 
           EXEC SQL 
                LOB COPY :LEN FROM :BLOB1 TO :TEMP-BLOB
           END-EXEC.

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

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL FREE :TEMP-BLOB 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++): Create a Temporary LOB

#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 createTempLOB_proc()
{
  OCIBlobLocator *Lob_loc, *Temp_loc;
  int Amount;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the LOB Locators: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL ALLOCATE :Temp_loc;

  /* Create the Temporary LOB: */
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  EXEC SQL SELECT Frame INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1;

  /* Copy the full length of the source LOB into the Temporary LOB: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Amount;
  EXEC SQL LOB COPY :Amount FROM :Lob_loc TO :Temp_loc;

  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;

  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

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