Trimming LOB Data

Figure 10-38 Use Case Diagram: Trimming LOB Data

Text description of the illustration adl10p47.gif

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

Purpose

This procedure describes how to trim LOB data.

Usage Notes

Locking the Row Prior to Updating

Prior to updating a LOB value via the PL/SQL DBMS_LOB package, or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of:

• A SELECT FOR UPDATE statement in SQL and PL/SQL programs.

• An OCI pin or lock function in OCI programs.

For more details on the state of the locator after an update, refer to "Updating LOBs Via Updated Locators" in Chapter 5, "Large Objects: Advanced Topics".

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

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

• 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 TRIM

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

• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > trim

• 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

Unless otherwise noted, these examples access text (CLOB data) referenced in the Script column of table Voiceover_tab, and trim it.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Trimming LOB Data

• C (OCI): Trimming LOB Data

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

• COBOL (Pro*COBOL): Trimming LOB Data

• C/C++ (Pro*C/C++): Trimming LOB Data

• Visual Basic (OO4O): Trimming LOB Data

• Java (JDBC): Trimming LOB Data

PL/SQL (DBMS_LOB Package): Trimming LOB Data

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

/* Note that the example procedure trimLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE trimLOB_proc IS
   Lob_loc        CLOB;
BEGIN
   /* Select the LOB, get the LOB locator: */
   SELECT Mtab.Voiced_ref.Script INTO Lob_loc FROM Multimedia_tab Mtab
      WHERE Mtab.Clip_ID = 2
         FOR UPDATE;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Trim the LOB data: */
   DBMS_LOB.TRIM(Lob_loc,100);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
COMMIT;
/* Exception handling: */
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Trimming LOB Data

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

/* Select the locator into a locator variable */
sb4 select_lock_voice_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
      (text *) "SELECT Mtab.Voiced_ref.Script \
           FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2 FOR UPDATE";
  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_CLOB,(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 trimLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Lob_loc;
  unsigned int trimLength;
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, 
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  /* Select the CLOB */
  printf( " select a voice LOB\n");
  select_lock_voice_locator(Lob_loc, errhp, svchp, stmthp);

  /* Open the CLOB */
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));

  /* Trim the LOB to its new length */
  trimLength = 100;                      /* <New truncated length of the LOB>*/

  printf (" trim the lob to %d bytes\n", trimLength);
  checkerr (errhp, OCILobTrim (svchp, errhp, Lob_loc, trimLength ));

  /* Closing the CLOB is mandatory if you have opened it */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));

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

COBOL (Pro*COBOL): Trimming LOB Data

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

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TRIM-CLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  CLOB1          SQL-CLOB.
       01  NEW-LEN        PIC S9(9) COMP.
      * Define the source and destination position and location: 
       01  SRC-POS        PIC S9(9) COMP.
       01  DEST-POS       PIC S9(9) COMP.
       01  SRC-LOC        PIC S9(9) COMP.
       01  DEST-LOC       PIC S9(9) COMP.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
        
       PROCEDURE DIVISION.
       TRIM-CLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the CLOB locators: 
           EXEC SQL ALLOCATE :CLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.
           EXEC SQL 
                SELECT MTAB.STORY INTO :CLOB1
                FROM MULTIMEDIA_TAB MTAB
                WHERE MTAB.CLIP_ID = 2 FOR UPDATE END-EXEC.
 
      * Open the CLOB: 
           EXEC SQL LOB OPEN :CLOB1 READ WRITE END-EXEC.

      * Move some value to  NEW-LEN: 
           MOVE 3 TO NEW-LEN.
           EXEC SQL 
                LOB TRIM :CLOB1 TO :NEW-LEN END-EXEC.

           EXEC SQL LOB CLOSE :CLOB1 END-EXEC.
       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++): Trimming LOB Data

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

/*In addition to the data structures set up above in the section "Examples" , you should use DML like this:
INSERT INTO multimedia_tab VALUES (2, 'The quick brown fox jumped over the lazy 
dog',  empty_clob(), NULL, empty_blob(), empty_blob(), NULL, NULL, NULL, NULL);
INSERT INTO voiceover_tab VALUES (voiced_typ('hello', (SELECT story FROM 
multimedia_tab WHERE clip_id = 2), 'world', 1, NULL))
UPDATE multimedia_tab SET voiced_ref = (SELECT REF(r) FROM voiceover_tab r WHERE 
r.take = 1) WHERE clip_id = 2
Then create this text file, pers_trim.typ, containing:
case=lower
type voiced_typ
Then run this Object Type Translator command:
ott intyp=pers_trim.typ outtyp=pers_trim_o.typ 
hfile=pers_trim.h code=c user=samp/samp
*/

#include "pers_trim.h"
#include <stdio.h>
#include <sqlca.h>
void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("sqlcode = %ld\n", sqlca.sqlcode);
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void trimLOB_proc()
{
  voiced_typ_ref *vt_ref;
  voiced_typ *vt_typ;
  OCIClobLocator *Lob_loc;
  unsigned int Length, trimLength;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL ALLOCATE :vt_ref;
  EXEC SQL ALLOCATE :vt_typ;

  /* Retrieve the REF using Associative SQL */
  EXEC SQL SELECT Mtab.Voiced_ref INTO :vt_ref
           FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2 FOR UPDATE;

  /* Dereference the Object using the Navigational Interface */
  EXEC SQL OBJECT DEREF :vt_ref INTO :vt_typ FOR UPDATE;
  Lob_loc = vt_typ->script;

  /* Opening the LOB is Optional */
  EXEC SQL LOB OPEN :Lob_loc READ WRITE;
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  printf("Old length was %d\n", Length);
  trimLength = (unsigned int)(Length / 2);

  /* Trim the LOB to its new length */
  EXEC SQL LOB TRIM :Lob_loc TO :trimLength;

  /* Closing the LOB is mandatory if it has been opened */
  EXEC SQL LOB CLOSE :Lob_loc;

  /* Mark the Object as Modified (Dirty) */
  EXEC SQL OBJECT UPDATE :vt_typ;

  /* Flush the changes to the LOB in the Object Cache */
  EXEC SQL OBJECT FLUSH :vt_typ;

  /* Display the new (modified) length */
  EXEC SQL SELECT Mtab.Voiced_ref.Script INTO :Lob_loc
           FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2;
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  printf("New length is now %d\n", Length);

  /* Free the Objects and the LOB Locator */
  EXEC SQL FREE :vt_ref;
  EXEC SQL FREE :vt_typ;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Trimming LOB Data

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

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

OraDyn.Edit
OraSound1.Trim 10
OraDyn.Update

Java (JDBC): Trimming LOB Data

/* 
 * This sample shows how to trim BLOBs and CLOBs. 
 */ 

// You need to import the java.sql package to use JDBC 
import java.sql.*; 

// You need to import the oracle.sql package to use oracle.sql.BLOB 
import oracle.sql.*; 

class TrimLob 
{ 
  public static void main (String args []) 
       throws SQLException 
  { 
    // Load the Oracle JDBC driver 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 

    String url = "jdbc:oracle:oci8:@"; 
    try { 
      String url1 = System.getProperty("JDBC_URL"); 
      if (url1 != null) 
        url = url1; 
    } catch (Exception e) { 
      // If there is any security exception, ignore it 
      // and use the default 
    } 

    // Connect to the database 
    Connection conn = 
      DriverManager.getConnection (url, "scott", "tiger"); 
    // It's faster when auto commit is off 
    conn.setAutoCommit (false); 

    // Create a Statement 
    Statement stmt = conn.createStatement (); 

    try 
    { 
      stmt.execute ("drop table basic_lob_table"); 
    } 
    catch (SQLException e) 
    { 
      // An exception could be raised here if the table did not exist already. 
    } 

    // Create a table containing a BLOB and a CLOB 
    stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c 
clob)"); 

    // Populate the table 
    stmt.execute ("insert into basic_lob_table values ('one', 
'010101010101010101010101010101', 'onetwothreefour')"); 

    // Select the lobs 
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Show the original lob length 
      System.out.println ("Open the lobs"); 
      System.out.println ("blob.length()="+blob.length()); 
      System.out.println ("clob.length()="+clob.length()); 

      // Trim the lobs 
      System.out.println ("Trim the lob to length = 6"); 
      blob.trim (6); 
      clob.trim (6); 

      // Show the lob length after trim() 
      System.out.println ("Open the lobs"); 
      System.out.println ("blob.length()="+blob.length()); 
      System.out.println ("clob.length()="+clob.length()); 
    } 

    // Close the ResultSet 
    rset.close (); 

    // Close the Statement 
    stmt.close (); 

    // Close the connection 
    conn.close (); 
  } 
} 

*************

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

Here is the old way of trimming LOB data, using DBMS_LOB.trim:

// Java IO classes: 
import java.io.InputStream;
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_141
{
  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 mtab.voiced_ref.script FROM multimedia_tab mtab 
            WHERE mtab.clip_id = 2 FOR UPDATE");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getCLOB (1);
   }

   // Open the LOB for READWRITE: 
       OracleCallableStatement cstmt = (OracleCallableStatement)
          conn.prepareCall ("BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READWRITE);  
          END;");
   cstmt.setCLOB(1, lob_loc);
   cstmt.execute();

   // Trim the LOB to length of 400: 
   cstmt = (OracleCallableStatement) 
      conn.prepareCall ("BEGIN DBMS_LOB.TRIM(?, 400); END;");
   cstmt.setCLOB(1, lob_loc);
   cstmt.execute();

   // Close the LOB: 
   cstmt = (OracleCallableStatement) conn.prepareCall (
      "BEGIN DBMS_LOB.CLOSE(?); END;");
   cstmt.setCLOB(1, lob_loc);
   cstmt.execute();

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