Determining If a Temporary LOB Is Open

Figure 11-7 Use Case Diagram: Determining If a Temporary LOB Is Open

Text description of the illustration adl11t22.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 temporary LOB is open.

Usage Notes

Not applicable.

Syntax

sSee 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" -- ISOPEN

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

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

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

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

• Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Checking if a LOB is Open" .

Scenario

These generic examples takes a locator as input, create a temporary LOB, open it and test if the LOB is open.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL: Determining if a Temporary LOB is Open

• C (OCI) Determining if a Temporary LOB is Open

• COBOL (Pro*COBOL): Determining if a Temporary LOB is Open

• C/C++ (Pro*C/C++): Determining if a Temporary LOB is Open

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

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

PL/SQL: Determining if a Temporary LOB is Open

/* Note that the example procedure seeTempLOBIsOpen_proc is not part of the 
   DBMS_LOB package. This procedure takes a locator as input, creates a  
   temporary LOB, opens it and tests if the LOB is open. */

CREATE OR REPLACE PROCEDURE seeTempLOBIsOpen_proc(Lob_loc IN OUT BLOB, 
                          Retval OUT INTEGER) IS
BEGIN
   /* Create the temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);
   /* See If the LOB is open: */
   Retval := DBMS_LOB.ISOPEN(Lob_loc);
  /* The value of Retval will be 1  if  the LOB is open. */
   /* Free the temporary LOB: */
   DBMS_LOB.FREETEMPORARY(Lob_loc);
END;

C (OCI) Determining if a Temporary LOB is Open

/* This function takes a locator and returns 0 if the function 
   completes successfully. The function prints out "Temporary LOB is open" or 
  "Temporary LOB is closed". It does not check whether or not the locator is 
   actually pointing to a temporary LOB or not, but the open or close test will 
   work either way. The function returns 0 if it completes 
   successfully, and -1 if it fails. */

sb4 seeTempLOBIsOpen (OCILobLocator *lob_loc,
                      OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{
    boolean is_open = FALSE;

    printf("in seeTempLOBIsOpen \n");

    if(OCILobCreateTemporary(svchp, 
              errhp, 
              lob_loc,
              (ub2)0, 
              SQLCS_IMPLICIT, 
              OCI_TEMP_BLOB, 
              OCI_ATTR_NOCACHE, 
              OCI_DURATION_SESSION))
    {
        (void) printf("FAILED: CreateTemporary() \n");
        return -1;
    }
 
    if(OCILobIsOpen(svchp, errhp, lob_loc, &is_open))
    {
        printf("OCILobIsOpen FAILED\n");
        return -1;
    }
    if(is_open)
    {
        printf("Temporary LOB is open\n");
 
    }else
    {
        printf("Temporary LOB is closed\n");
 
    }

    if(OCILobFreeTemporary(svchp,errhp,lob_loc))
    {
        printf("OCILobFreeTemporary FAILED \n");
        return -1;
    }
    return 0;
    }

COBOL (Pro*COBOL): Determining if a Temporary LOB is Open

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

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

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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.
       01  IS-OPEN        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.
       TEMP-LOB-ISOPEN.
           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.

      * Open temporary LOB:
           EXEC SQL LOB OPEN :TEMP-BLOB READ ONLY END-EXEC.
           EXEC SQL 
                LOB DESCRIBE :TEMP-BLOB GET ISOPEN INTO :IS-OPEN
           END-EXEC.
           
           IF IS-OPEN = 1
      *       Logic for an open temporary LOB goes here:
              DISPLAY "Temporary LOB is OPEN."
           ELSE 
      *       Logic for a closed temporary LOB goes here:
              DISPLAY "Temporary LOB is CLOSED."
           END-IF.
           EXEC SQL
                ROLLBACK WORK RELEASE
           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++): Determining if a Temporary LOB is Open

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

#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 tempLobIsOpen_proc()
{
  OCIBlobLocator *Temp_loc;
  int isOpen = 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;
  /* Open the Temporary LOB */
  EXEC SQL LOB OPEN :Temp_loc READ ONLY;
  /* Determine if the LOB is Open */
  EXEC SQL LOB DESCRIBE :Temp_loc GET ISOPEN INTO :isOpen;
  if (isOpen)
    printf("Temporary LOB is open\n");
  else
    printf("Temporary LOB is not open\n");
  /* Note that in this example, the LOB is Open so isOpen == 1 (TRUE) */
  /* Close the LOB */
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator */
  EXEC SQL FREE :Temp_loc;
}

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

}