Length: Determining the Length of a LOB

Figure 10-28 Use Case Diagram: Determining the Length of a LOB

Text description of the illustration adl10p32.gif

See: "Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.

Purpose

This procedure describes how to determine the length of a LOB.

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" -- GETLENGTH

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

• 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++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE ...GET LENGTH...

• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oradynaset > PROPERTIES > fields

• 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 examples demonstrate how to determine the length of a LOB in terms of the foreign language subtitle (FLSub).

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Determining the Length of a LOB

• C (OCI): Determining the Length of a LOB

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

• COBOL (Pro*COBOL): Determining the Length of a LOB

• C/C++ (Pro*C/C++): Determining the Length of a LOB

• Visual Basic (OO4O): Determining the Length of a LOB

• Java (JDBC): Determining the Length of a LOB

PL/SQL (DBMS_LOB Package): Determining the Length of a LOB

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

/* Note that the example procedure getLengthLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE getLengthLOB_proc IS
   Lob_loc     NCLOB;
   Length      INTEGER;
BEGIN
   /* Select the LOB: */
   SELECT FLSub INTO Lob_loc FROM Multimedia_tab
       WHERE Clip_ID = 2;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Get the length of the LOB: */
   length := DBMS_LOB.GETLENGTH(Lob_loc);
   IF length IS NULL THEN
       DBMS_OUTPUT.PUT_LINE('LOB is null.');
   ELSE
       DBMS_OUTPUT.PUT_LINE('The length is '|| length);
   END IF;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
END;

C (OCI): Determining the Length of a LOB

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

/* Select the locator into a locator variable */
sb4 select_FLSub_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  OCIDefine *defnp1;

  text  *sqlstmt = 
    (text *)"SELECT FLSub FROM Multimedia_tab WHERE Clip_ID = 2";
     
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
  /* Define the column being selected */ 
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2)SQLT_CLOB,(dvoid *)0, (ub2 *)0, 
                                  (ub2 *)0, (ub4)OCI_DEFAULT));
   /* Execute and fetch one row */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
  return 0;
}

/* This function gets the length of the selected LOB */
void getLengthLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  ub4 length;
  OCILobLocator *Lob_loc;
    /* Allocate Locator resources */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, 
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);
  
  /* Select a LOB locator from FLSub */
  printf(" select a FLSub locator\n");
  select_FLSub_locator(Lob_loc, errhp, svchp, stmthp);
  
  /* Opening the LOB is Optional */
  printf(" Open the locator (optional)\n");
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY)));
  
  printf(" get the length of FLSub.\n");
  checkerr (errhp, OCILobGetLength(svchp, errhp, Lob_loc, &length));

  /* Length is undefined if the LOB is NULL or undefined */
  fprintf(stderr," Length of LOB is %d\n",length);

  /* Closing the LOBs is Mandatory if they have been Opened */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB);
  
  return;
  }

COBOL (Pro*COBOL): Determining the Length of a LOB

This script is provided at $ORACLE_HOME/rdbms/demo/lobs/cobol/ilength

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

       01  CLOB1          SQL-CLOB.
       01  LOB-ATTR-GRP.
           05 LEN         PIC S9(9) COMP.
       01  D-LEN          PIC 9(4).
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
       LOB-LENGTH.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the target CLOB: 
           EXEC SQL ALLOCATE :CLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.
           EXEC SQL 
              SELECT STORY INTO :CLOB1
              FROM MULTIMEDIA_TAB WHERE CLIP_ID = 2 END-EXEC.
  
      * Obtain the length of the CLOB: 
           EXEC SQL 
              LOB DESCRIBE :CLOB1 GET LENGTH INTO :LEN END-EXEC.
           MOVE LEN TO D-LEN.
           DISPLAY "The length is ", D-LEN.

      * Free the resources used by the CLOB: 
       END-OF-CLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :CLOB1 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++ (Pro*C/C++): Determining the Length of a LOB

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

#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 getLengthLOB_proc()
{
  OCIClobLocator *Lob_loc;
  unsigned int Length;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Story INTO :Lob_loc
           FROM Multimedia_tab WHERE Clip_ID = 1;
  /* Opening the LOB is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Get the Length: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  /* If the LOB is NULL or unitialized, then Length is Undefined: */
  printf("Length is %d characters\n", Length);
  /* Closing the LOB is mandatory if you have Opened it: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Determining the Length of a LOB

You can find this script at $ORACLE_HOME/rdbms/demo/lobs/vbasic/ilength

Dim MySession As OraSession
Dim OraDb As OraDatabase

Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
    "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT)
Set OraSound1 = OraDyn.Fields("Sound").Value

'Display out size of the lob: 
MsgBox "Length of the lob is " & OraSound1.Size

Java (JDBC): Determining the Length of a LOB

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/java/ilength.

import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
import java.sql.Statement;
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_95
{

  static final int MAXBUFSIZE = 32767;
  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
    {
     CLOB lob_loc = null;
      ResultSet rset = stmt.executeQuery 
          ("SELECT story FROM multimedia_tab WHERE clip_id = 2");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getCLOB (1);
   }

   System.out.println(
      "Length of this column is : " + Long.toString(lob_loc.length()));

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