Append One LOB to Another

Figure 9-33 Use Case Diagram: Append One LOB to Another

See: "Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs.

Purpose

This procedure describes how to append one LOB to another.

Usage Notes

Locking the Row Prior to Updating

Prior to updating a LOB value via the PL/SQL DBMS_LOB package or the 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 SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs. For more details on the state of the locator after an update, refer to "Updated LObs Via Updated Locators" in Chapter 5, "Advanced Topics".

Syntax

See Chapter 3, "LOB 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): Oracle8i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- APPEND

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

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

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

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

• Java (JDBC): Oracle8i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle8i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

These examples deal with the task of appending one segment of Sound to another. We assume that you use sound-specific editing tools to match the wave-forms.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Append One LOB to Another

• C (OCI): Append One LOB to Another

• COBOL (Pro*COBOL): Append One LOB to Another

• C/C++ (Pro*C/C++): Append One LOB to Another

• Visual Basic (OO4O): Append One LOB to Another

• Java (JDBC): Append One LOB to Another

PL/SQL (DBMS_LOB Package): Append One LOB to Another

/* Note that the example procedure appendLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE appendLOB_proc IS
   Dest_loc        BLOB;
   Src_loc         BLOB;
BEGIN
   /* Select the LOB, get the destination LOB locator: */
   SELECT Sound INTO Dest_loc FROM Multimedia_tab
      WHERE Clip_ID = 2
         FOR UPDATE;
   /* Select the LOB, get the destination LOB locator: */
   SELECT Sound INTO Src_loc FROM Multimedia_tab
      WHERE Clip_ID = 1;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Dest_loc, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN (Src_loc, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.APPEND(Dest_loc, Src_loc);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Dest_loc);
   DBMS_LOB.CLOSE (Src_loc);
COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Append One LOB to Another

/* This function appends the Source LOB to the end of the Destination LOB*/
/* Select the locator */
sb4 select_lock_sound_locator_2(Lob_loc, dest_type, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
ub1            dest_type;                     /* whether destination locator */
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{       
  char        sqlstmt[150];
  OCIDefine   *defnp1;
if (dest_type == TRUE)
  {
    strcpy (sqlstmt, 
      (char *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=2 FOR UPDATE");
    printf ("  select destination sound locator\n");
  }
  else
  {
    strcpy(sqlstmt, (char *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1");
    printf ("  select source sound locator\n");
  }
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, (text *)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 appendLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Dest_loc, *Src_loc;
    
  /* Allocate the LOB locators */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Dest_loc,
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Src_loc, 
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  /* Select the LOBs */
  printf(" select source and destination Lobs\n");
  select_lock_sound_locator_2(Dest_loc, TRUE, errhp, svchp, stmthp); 
                                                      /* destination locator */
  select_lock_sound_locator_2(Src_loc, FALSE, errhp, svchp, stmthp); 
                                                           /* source locator */
  /* Opening the LOBs is Optional */
  checkerr (errhp, OCILobOpen(svchp, errhp, Dest_loc, OCI_LOB_READWRITE)); 
  checkerr (errhp, OCILobOpen(svchp, errhp, Src_loc, OCI_LOB_READONLY));
  
  /* Append Source LOB to the end of the Destination LOB. */
  printf(" append the source Lob to the destination Lob\n");
  checkerr(errhp, OCILobAppend(svchp, errhp, Dest_loc, Src_loc));

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

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

COBOL (Pro*COBOL): Append One LOB to Another

       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-APPEND.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID        PIC X(11) VALUES "SAMP/SAMP".
       01  DEST          SQL-BLOB.
       01  SRC           SQL-BLOB.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       APPEND-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 :DEST END-EXEC.
           EXEC SQL ALLOCATE :SRC END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT SOUND INTO :DEST
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 2 FOR UPDATE END-EXEC.
           EXEC SQL SELECT SOUND INTO :SRC
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC.
 
      * Open the DESTination LOB read/write and SRC LOB read only: 
           EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC READ ONLY END-EXEC.

      * Append the source LOB to the destination LOB: 
           EXEC SQL LOB APPEND :SRC TO :DEST END-EXEC.
           EXEC SQL LOB CLOSE :DEST END-EXEC.
           EXEC SQL LOB CLOSE :SRC END-EXEC.

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :DEST END-EXEC.
           EXEC SQL FREE :SRC 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++): Append One LOB to Another

#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 appendLOB_proc()
{
  OCIBlobLocator *Dest_loc, *Src_loc;
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();

  /* Allocate the locators: */
  EXEC SQL ALLOCATE :Dest_loc;
  EXEC SQL ALLOCATE :Src_loc;

  /* Select the destination locator: */
  EXEC SQL SELECT Sound INTO :Dest_loc
           FROM Multimedia_tab WHERE Clip_ID = 2 FOR UPDATE;

  /* Select the source locator: */
  EXEC SQL SELECT Sound INTO :Src_loc
           FROM Multimedia_tab WHERE Clip_ID = 1;

  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Dest_loc READ WRITE;
  EXEC SQL LOB OPEN :Src_loc READ ONLY;

  /* Append the source LOB to the end of the destination LOB: */
  EXEC SQL LOB APPEND :Src_loc TO :Dest_loc;

  /* Closing the LOBs is mandatory if they have been opened: */
  EXEC SQL LOB CLOSE :Dest_loc;
  EXEC SQL LOB CLOSE :Src_loc;

  /* Release resources held by the locators: */
  EXEC SQL FREE :Dest_loc;
  EXEC SQL FREE :Src_loc;
}

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

Visual Basic (OO4O): Append One LOB to Another

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
Set OraSoundClone = OraSound1

OraDyn.MoveNext
OraDyn.Edit
OraSound1.Append OraSoundClone
OraDyn.Update

Java (JDBC): Append One LOB to Another

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_121
{
  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
    {
     ResultSet rset = null;
     BLOB dest_loc = null;
     BLOB src_loc = null;
   InputStream in = null;
   byte[] buf = new byte[MAXBUFSIZE];
   int length = 0;
   long pos = 0;
      rset = stmt.executeQuery (
          "SELECT sound FROM multimedia_tab WHERE clip_id = 2");
   if (rset.next())
   {
     src_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   in = src_loc.getBinaryStream();

       rset = stmt.executeQuery (
          "SELECT sound FROM multimedia_tab WHERE clip_id = 1 FOR UPDATE");
   if (rset.next())
   {
     dest_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   // Start writing at the end of the LOB.  ie. append: 
   pos = dest_loc.length();
   while ((length = in.read(buf)) != -1)
   {
      // Write the contents of the buffer into position pos of the output LOB: 
      dest_loc.putBytes(pos, buf);
      pos += length;
   }

   // Close all streams and handles: 
   in.close();
   stmt.close();
   conn.commit();
   conn.close();

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