Temporary LOBs, 7 of 29

Freeing a Temporary LOB

Figure 11-5 Use Case Diagram: Freeing a Temporary LOB

Text description of adl11t20.gif follows

Text description of the illustration adl11t20.gif

See:

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

Purpose

This procedure describes how to free a temporary LOB.

Usage Notes

A temporary LOB instance can only be destroyed for example, in OCI or the DBMS_LOB package by using the appropriate FREETEMPORARY or OCIDurationEnd or OCILOBFreeTemporary statements.

To make a temporary LOB permanent, the user must explicitly use the OCI or DBMS_LOB copy() command and copy the temporary LOB into a permanent one.

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" -- FREETEMPORARY
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobFreeTemporary
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide: Appendix F, "Embedded SQL and Precompiler Directives" -- LOB FREE TEMPORARY
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 3, "LOB Support in Different Programmatic Environments", Table 3-50, "JDBC: Temporary BLOB APIs". Note: temporaryClob.java has been deprecated as there is no longer a need for this class in the JDBC standard.

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Freeing a Temporary LOB
C (OCI): Freeing a Temporary LOB
C/C++ (Pro*C/C++): Freeing a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): Freeing a Temporary BLOB and Java (JDBC): Freeing a Temporary CLOB.
Java (JDBC): Creating and Freeing a Temporary CLOB Using TemporaryClob.java has been deprecated.

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

DECLARE
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount         INTEGER := 4000;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   /* Opening the 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(Src_loc);
   DBMS_LOB.CLOSE(Dest_loc);
   /* Free the temporary LOB: */
   DBMS_LOB.FREETEMPORARY(Dest_loc);
END;

C (OCI): Freeing a Temporary LOB

/* This function creates a temporary LOB and then frees it: 
   This function returns 0 if it completes successfully, and -1 otherwise:  */ 

sb4 freeTempLob(OCIError      *errhp, 
                OCISvcCtx     *svchp,
                OCIStmt       *stmthp, 
                OCIEnv        *envhp)
{
  OCILobLocator *tblob;
  checkerr (errhp,OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob,
                                     (ub4)OCI_DTYPE_LOB, (size_t)0,
                                     (dvoid**)0));
  if(OCILobCreateTemporary(svchp,errhp,tblob,(ub2)0,SQLCS_IMPLICIT,
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE,
                           OCI_DURATION_SESSION))
  {
      (void) printf("FAILED:CreateTemporary():freeTempLob\n");
      return -1;
  }

  if(OCILobFreeTemporary(svchp,errhp,tblob))
  {
      printf ("FAILED: OCILobFreeTemporary call in freeTempLob\n");
      return -1;

  }else
  {
   printf("Temporary LOB freed in freeTempLob\n");
  }
  return 0;
 }

COBOL (Pro*COBOL): Freeing a Temporary LOB

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/oci/tfree

       IDENTIFICATION DIVISION.
       PROGRAM-ID. FREE-TEMPORARY.
       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.
       FREE-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.

      * Do something with the temporary LOB here: 

      * Free the temporary LOB:
           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++): Freeing a Temporary LOB

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/oci/tfree

#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 freeTempLob_proc()
{
  OCIBlobLocator *Temp_loc;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Do something with the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  EXEC SQL FREE :Temp_loc;
}

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

Java (JDBC): Freeing a Temporary BLOB

To free a temporary BLOB, the JDBC application can either use the freeTemporary instance method to free the current BLOB object, or pass the temporary BLOB to be freed to the static freeTemporary method to free the specified temporary BLOB. These two methods are defined as follows:

/** 
* Free the contents and the locator of the temporary blob. 
* @param temp_lob A temporary blob to be freed. 
* @exception SQLException if temp_lob is a permanent lob or temp_lob has already 
been freed. 
*/ 
public static void freeTemporary (BLOB temp_lob) throws SQLException 

/** 
* Free the contents and the locator of the temporary blob. 
* @exception SQLException if self is a permanent lob or self has already been 
freed. 
*/ 
public void freeTemporary() throws SQLException

The usage example example is -- 

BLOB tempBlob1 = ... 
BLOB tempBlob2 = ... 

// free the temporary BLOB 
tempBlob1.freeTemporary (); 
// free the specified temporary BLOB 
BLOB.freeTemporary(tempBlob2);

The new freeTemporary APIs should replace the previous workaround of using dbms_
lob.freeTemporary() in dbms_lob PL/SQL package.

Java (JDBC): Freeing a Temporary CLOB

To free a temporary CLOB, the JDBC application can either use the freeTemporary instance method to free the current CLOB object, or pass the temporary CLOB to be freed to the static freeTemporary method to free the specified temporary CLOB. These two methods are defined as follows:

/** 
* Free the contents and the locator of the temporary blob. 
* @param temp_lob A temporary clob to be freed. 
* @since 8.2.0 
* @exception SQLException if temp_lob is a permanent lob or temp_lob has 
*            already been freed. 
*/ 
public static void freeTemporary (CLOB temp_lob) throws SQLException 

/** 
* Free the contents and the locator of the temporary clob. 
* 
* @since 8.2.0 
* @exception SQLException if self is a permanent lob or self has 
*            already been freed. 
*/ 
public void freeTemporary() throws SQLException

Use the free temporary BLOB API as follows:

CLOB tempClob1 = ... 
CLOB tempClob2 = ... 

// free the temporary CLOB 
tempClob1.freeTemporary (); 

// free the specified temporary CLOB 
CLOB.freeTemporary(tempClob2);

The freeTemporary API replaces previous workarounds that use DBMS_LOB.freetemporary().

Java (JDBC): Creating and Freeing a Temporary CLOB Using TemporaryClob.java

Note:

temporaryClob.java and temporaryBlob.java classes have been deprecated. These classes are no longer needed or used in the JDBC standard.