Open: Checking If a LOB Is Open

Figure 10-17 Use Case Diagram: Checking If a LOB Is Open

Text description of the illustration adl10p22.gif

Purpose

This procedure describes how to check if LOB is open.

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 (DBM_LOB Package): Oracle9i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- OPEN, ISOPEN.

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

• C++ (OCCI): Oracle C++ Interface Programmer's Guide

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

• C/C++ (ProC/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): 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

The following "Checking if a LOB is Open" examples open a Video frame (Frame), and then evaluate it to see if the LOB is open.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Checking if a LOB is Open

• C (OCI): Checking if a LOB is Open

• C++ (OCCI): No example is provided with this release.

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

• C/C++ (ProC/C++): Checking if a LOB is Open

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

• Java (JDBC): Checking if a LOB is Open

PL/SQL (DBMS_LOB Package): Checking if a LOB is Open

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/pls/iisopen.

/* Note that the example procedure lobIsOpen_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE lobIsOpen_proc IS
   Lob_loc     BLOB;
   Retval      INTEGER;
BEGIN
   SELECT Frame INTO Lob_loc  FROM Multimedia_tab where Clip_ID = 1;

   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc , DBMS_LOB.LOB_READONLY);

   /* See if the LOB is open: */
   Retval := DBMS_LOB.ISOPEN(Lob_loc);
  /* The value of Retval will be 1 meaning that the LOB is open. */
END;

C (OCI): Checking if a LOB is Open

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

/* Select the locator into a locator variable */
sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text      *sqlstmt = 
    (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1";
  OCIDefine *defnp1;
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));
  /* Execute the select and fetch one row */
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT));
    return (0);
}

void seeIfLOBIsOpen(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Lob_loc;
  int isOpen;

  /* Allocate locator resources */
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc,
                            (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);
  /* Select the locator */
  (void)select_frame_locator(Lob_loc, errhp, svchp, stmthp);
  
  /* See if the LOB is Open */
  checkerr (errhp, OCILobIsOpen(svchp, errhp, Lob_loc, &isOpen));
 
  if (isOpen)
  {
    printf("  Lob is Open\n");
    /* ... Processing given that the LOB has already been Opened */
  }
  else
  {
    printf("  Lob is not Open\n");
    /* ... Processing given that the LOB has not been Opened */
  }
  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *)  Lob_loc, (ub4) OCI_DTYPE_LOB);
  
  return;
  }

COBOL (Pro*COBOL): Checking if a LOB is Open

This script is provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/iifopen

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

       01  BLOB1          SQL-BLOB.
       01  LOB-ATTR-GRP.
           05 ISOPN       PIC S9(9) COMP.
       01  SRC            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  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       LOB-OPEN.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the target BLOB
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT FRAME INTO :BLOB1
              FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC.
  
      * See if the LOB is OPEN
           EXEC SQL 
              LOB DESCRIBE :BLOB1 GET ISOPEN INTO :ISOPN END-EXEC.

           IF ISOPN = 1 
      *       <Processing for the LOB OPEN case>
              DISPLAY "The LOB is open"
           ELSE
      *         <Processing for the LOB NOT OPEN case>
              DISPLAY "The LOB is not open"
           END-IF.

      * Free the resources used by the BLOB
       END-OF-BLOB.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
              ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (ProC/C++): Checking if a LOB is Open

You can also find this script at $ORACLE_HOME/rdbms/demo/lobs/proc/iifopen

#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 seeIfLOBIsOpen()
{
  OCIBlobLocator *Lob_loc;
  int isOpen = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Frame INTO :Lob_loc
           FROM Multimedia_tab WHERE Clip_ID = 1;
  /* See if the LOB is Open: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET ISOPEN INTO :isOpen;
  if (isOpen)
    printf("LOB is open\n");
  else
    printf("LOB is not open\n");
  /* Note that in this example, the LOB is not open */
  EXEC SQL FREE :Lob_loc;
}

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

Java (JDBC): Checking if a LOB is Open

Checking if a CLOB is Open

To see if a CLOB is opened, your JDBC application can now use the isOpen method defined in oracle.sql.CLOB. The return boolean value indicates whether the CLOB has been previously opened or not. The isOpen method is defined as follows:

/** 
  * Check whether the CLOB is opened. 
  * @return true if the LOB is opened. 
  */ 
public boolean isOpen () throws SQLException

The usage example is:

CLOB clob = ... 
 // See if the CLOB is opened 
 boolean isOpen = clob.isOpen ();

Checking If a BLOB is Opened

To see if a BLOB is opened, your JDBC application can now use the isOpen method defined in oracle.sql.BLOB. The return boolean value indicates whether the BLOB has been previously opened or not. The isOpen method is defined as follows:

/** 
 * Check whether the BLOB is opened. 
 * @return true if the LOB is opened. 
 */ 
 public boolean isOpen () throws SQLException

The usage example is:

BLOB blob = ... 
// See if the BLOB is opened 
boolean isOpen = blob.isOpen ();

This script is located at $ORACLE_HOME/rdbms/demo/lobs/java/iisopen.java.
// Core JDBC classes: 
import java.io.OutputStream;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.Types;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_48
{
  public Ex2_48 ()
  {
  }

  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database: 
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp");
    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);
    // Create a Statement: 
    Statement stmt = conn.createStatement ();

    try
    {
      BLOB blob = null;
      ResultSet rset = stmt.executeQuery (
          "SELECT frame FROM multimedia_tab WHERE clip_id = 1");
   if (rset.next())
   {
     blob = ((OracleResultSet)rset).getBLOB (1);
   }
       OracleCallableStatement cstmt =
            (OracleCallableStatement) conn.prepareCall (
            "BEGIN ? := DBMS_LOB.ISOPEN(?); END;");
   cstmt.registerOutParameter (1, Types.NUMERIC);
   cstmt.setBLOB(2, blob);
   cstmt.execute();
   int result = cstmt.getInt(1);
   System.out.println("The result is: " + Integer.toString(result));

   OracleCallableStatement cstmt2 = (OracleCallableStatement) 
       conn.prepareCall (
       "BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READONLY); END;");
   cstmt2.setBLOB(1, blob);
   cstmt2.execute();

   System.out.println("The LOB has been opened with a call to DBMS_LOB.OPEN()");

   // Use the existing cstmt handle to re-query the status of the locator: 
   cstmt.setBLOB(2, blob);
   cstmt.execute();
   result = cstmt.getInt(1);
   System.out.println("This result is: " + Integer.toString(result));

   stmt.close();
   cstmt.close();
   cstmt2.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
}