Checking If a LOB is Temporary

Figure 11-4 Use Case Diagram: Checking If a LOB is Temporary

Text description of the illustration adl11t19.gif

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 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" -- 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): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

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): Checking If a LOB is Temporary

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

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

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

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

• Java (JDBC): Checking if a BLOB is Temporary

• Java (JDBC): Checking if a CLOB is Temporary.

PL/SQL (DBMS_LOB Package): Checking 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): Checking 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): Checking If a LOB is Temporary

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/procobol/ tiftemp

       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++): Checking If a LOB is Temporary

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

#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;

}

Java (JDBC): Checking if a BLOB is Temporary

To see if a BLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current BLOB object is temporary, or pass the BLOB object to the static isTemporary method to determine whether the specified BLOB object is temporary. These two methods are defined as follows:

/** 
* Returns true if lob locator points to a temporary blob. False if not. 
* @param lob the blob to test. 
* @returns true if lob locator points to a temporary blob. False if not. 
*/ 
  public static boolean isTemporary (BLOB lob) throws SQLException 

/** 
 * Returns true if lob locator points to a temporary blob. False if not. 
 * @returns true if lob locator points to a temporary blob. False if not. 
 */ 
   public boolean isTemporary () throws SQLException

The usage example is -- 

  BLOB blob = ... 

  // See if the BLOB is temporary 
  boolean isTemporary = blob.isTemporary (); 

  // See if the specified BLOB is temporary 
  boolean isTemporary2 = BLOB.isTemporary(blob);

This new JDBC APIs replaces previous workarounds that use DBMS_LOB.isTemporary().

Java (JDBC): Checking if a CLOB is Temporary

To see if a CLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current CLOB object is temporary, or pass the CLOB object to the static isTemporary method to determine whether the specified CLOB object is temporary. These two methods are defined as follows:

/** 
 * Return true if the lob locator points to a temporary clob. False if it 
 * does not. 
 * 
 * @param lob the blob to test. 
 * @return true if the lob locator points to a temporary clob. False if it 
 *         does not. 
 */ 
  public static boolean isTemporary (CLOB lob) throws SQLException 

  /** 
   * Return true if the lob locator points to a temporary clob. False if it 
   * does not. 
   * 
   * @return true if the lob locator points to a temporary clob. False if it 
   *         does not. 
   */ 
    public boolean isTemporary () throws SQLException 

The usage example is:

CLOB clob = ... 

// See if the CLOB is temporary 
boolean isTemporary = clob.isTemporary (); 
// See if the specified CLOB is temporary 
boolean isTemporary2 = CLOB.isTemporary(clob);

This new API replaces previous workarounds that used DBMS_LOB.istemporary().