See If a LOB is Temporary

Figure 10-4 Use Case Diagram: See If a LOB is Temporary

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

Purpose

This procedure describes how to see if a LOB is temporary.

Usage Notes

Not applicable.

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

• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobIsTemporary

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

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

• 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 are generic examples that query whether the locator is associated with a temporary LOB or not.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): See If a LOB is Temporary

• C (OCI): See If a LOB is Temporary

• COBOL (Pro*COBOL): See If a LOB is Temporary

• C/C++ (Pro*C/C++): See If a LOB is Temporary

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

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

PL/SQL (DBMS_LOB Package): See If a LOB is Temporary

/* This is also an example of freeing a temporary LOB. First we test to make
   sure that the LOB locator points to a temporary LOB, then we free it.
   Otherwise, we issue an error: */

CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS
BEGIN
   /* Free the temporary LOB locator passed in. */
   /* First check to make sure that the locator is pointing to a temporary 
      LOB:*/
    IF DBMS_LOB.ISTEMPORARY(Lob_loc) = 1 THEN
       /* Free the temporary LOB locator: */
        DBMS_LOB.FREETEMPORARY(Lob_loc);
        DBMS_OUTPUT.PUT_LINE(' temporary LOB was freed');
    ELSE
        /* Print an error: */
        DBMS_OUTPUT.PUT_LINE(
           'Locator passed in was not a temporary LOB locator');
    END IF;
END;

C (OCI): See If a LOB is Temporary

/* This function also frees a temporary LOB. It takes a locator as an argument,   
   checks to see if it is a temporary LOB, and if it is the function will free 
   the temporary LOB. Otherwise, it will print out a message saying the locator 
   wasn't a temporary LOB locator. This function returns 0 if it 
   completes successfully, and -1 otherwise: */ 

sb4 check_and_free_temp(OCILobLocator *tblob,
                        OCIError      *errhp, 
                        OCISvcCtx     *svchp,
                        OCIStmt       *stmthp, 
                        OCIEnv        *envhp)
{
  boolean is_temp;
  is_temp = FALSE;
 
  if (OCILobIsTemporary(envhp, errhp, tblob, &is_temp))
  {
    printf ("FAILED: OCILobIsTemporary call\n");
    return -1;
  }
  if(is_temp)
  {
      if(OCILobFreeTemporary(svchp, errhp, tblob))
      {
          printf ("FAILED: OCILobFreeTemporary call\n");
          return -1;
 
      }else
      {
          printf("Temporary LOB freed\n");
      }
  }else
  {
      printf("locator is not a temporary LOB locator\n");
  }
  return 0;
  }

COBOL (Pro*COBOL): See If a LOB is Temporary

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

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

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

      * Check if the LOB is temporary: 
           EXEC SQL 
                LOB DESCRIBE :TEMP-BLOB
                GET ISTEMPORARY INTO :IS-TEMP
           END-EXEC.
     
           IF IS-TEMP = 1
      *      Logic for a temporary LOB goes here
             DISPLAY "LOB is temporary."
           ELSE
      *      Logic for a persistent LOB goes here.
             DISPLAY "LOB is persistent."
           END-IF.

           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           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++): See If a LOB is Temporary

#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 lobIsTemp_proc()
{
  OCIBlobLocator *Temp_loc;
  int isTemporary = 0;

  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;
  /* Determine if the Locator is a Temporary LOB Locator: */
  EXEC SQL LOB DESCRIBE :Temp_loc GET ISTEMPORARY INTO :isTemporary;
   
  /* Note that in this example, isTemporary should be 1 (TRUE) */
  if (isTemporary)
    printf("Locator is a Temporary LOB locator\n");
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
else
    printf("Locator is not a Temporary LOB locator \n");
   }

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

}