Internal Persistent LOBs, 35 of 43

Erasing Part of a LOB

Figure 10-39 Use Case Diagram: Erasing Part of a LOB

Text description of adl10ps2.gif follows

Text description of the illustration adl10ps2.gif

See:

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

Purpose

This procedure describes how to erase part of a LOB.

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 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, or by using the 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" -- ERASE
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobErase.
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 ERASE
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB ERASE
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > erase
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 examples demonstrate erasing a portion of sound (Sound).

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Erasing Part of a LOB
C (OCI): Erasing Part of a LOB
C++ (OCCI): No example is provided with this release.
COBOL (Pro*COBOL): Erasing Part of a LOB
C/C++ (Pro*C/C++): Erasing Part of a LOB
Visual Basic (OO4O): Erasing Part of a LOB
Java (JDBC): Erasing Part of a LOB

PL/SQL (DBMS_LOB Package): Erasing Part of a LOB

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

/* Note that the example procedure eraseLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE eraseLOB_proc IS
   Lob_loc        BLOB;
   Amount         INTEGER := 3000;
BEGIN
   /* Select the LOB, get the LOB locator: */
   SELECT Sound INTO lob_loc FROM Multimedia_tab
      WHERE Clip_ID = 1
         FOR UPDATE;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Erase the data: */
   DBMS_LOB.ERASE(Lob_loc, Amount, 2000);
   /* 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): Erasing Part of a LOB

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

/* Select the locator into a locator variable: */
sb4 select_lock_sound_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
     (text *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1 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_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 eraseLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Lob_loc;
  ub4 amount = 3000;
  ub4 offset = 2000;

  OCILobLocator *Lob_Loc;
 
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, 
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  /* Select the CLOB: */
  printf( " select and lock a sound LOB\n");
  select_lock_sound_locator(Lob_loc, errhp, svchp, stmthp);  

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

  /* Erase the data starting at the specified Offset: */
  printf(" erase %d bytes from the sound Lob\n", amount); 
  checkerr (errhp, OCILobErase (svchp, errhp, Lob_loc, &amount, offset ));

  /* Closing the BLOB 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);

  return;
}

COBOL (Pro*COBOL): Erasing Part of a LOB

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

       IDENTIFICATION DIVISION.
       PROGRAM-ID. ERASE-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP.
           EXEC SQL INCLUDE SQLCA END-EXEC.
        
       PROCEDURE DIVISION.
       ERASE-BLOB.

           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 :BLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
                SELECT SOUND INTO :BLOB1
                FROM MULTIMEDIA_TAB MTAB
                WHERE MTAB.CLIP_ID = 2 FOR UPDATE
           END-EXEC.
 
      * Open the BLOB: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.

      * Move some value to AMT and OFFSET: 
           MOVE 2 TO AMT.
           MOVE 1 TO OFFSET.
           EXEC SQL 
                LOB ERASE :AMT FROM :BLOB1 AT :OFFSET END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.
       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           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++ (Pro*C/C++): Erasing Part of a LOB

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

#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 eraseLob_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = 5;
  int Offset = 5;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Sound INTO :Lob_loc
           FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE;
  /* Opening the LOB is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ WRITE;
  /* Erase the data starting at the specified Offset: */
  EXEC SQL LOB ERASE :Amount FROM :Lob_loc AT :Offset;
  printf("Erased %d bytes\n", Amount);
  /* Closing the LOB is mandatory if it has been opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Erasing Part of a LOB

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

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
'Erase 10 bytes begining from the 100th byte: 
OraDyn.Edit
OraSound1.Erase 10, 100
OraDyn.Update

Java (JDBC): Erasing Part of a LOB

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

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_145
{
  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
    {
     BLOB lob_loc = null;
     int eraseAmount = 30;
     ResultSet rset = stmt.executeQuery (
        "SELECT sound FROM multimedia_tab WHERE clip_id = 2 FOR UPDATE");
   if (rset.next())
   {
     lob_loc = ((OracleResultSet)rset).getBLOB (1);
   }

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

   // Erase eraseAmount bytes starting at offset 2000: 
   cstmt = (OracleCallableStatement) 
      conn.prepareCall ("BEGIN DBMS_LOB.ERASE(?, ?, 1); END;");
   cstmt.registerOutParameter (1, OracleTypes.BLOB);
   cstmt.registerOutParameter (2, Types.INTEGER);
   cstmt.setBLOB(1, lob_loc);
   cstmt.setInt(2, eraseAmount);
   cstmt.execute();
   lob_loc = cstmt.getBLOB(1);
   eraseAmount = cstmt.getInt(2);

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

   conn.commit();
   stmt.close();
   cstmt.close();
   conn.commit();
   conn.close();

    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}