Internal Persistent LOBs, 24 of 42

Copy All or Part of a LOB to Another LOB

Figure 9-27 Use Case Diagram: Copy All or Part of a LOB to Another LOB

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

Purpose

This procedure describes how to copy all or part of a LOB to another 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 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" -- COPY
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OciLobCopy.
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB COPY. Also reference PL/SQL DBMS_LOB.COMPARE
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB COPY
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > copy, and > OBJECTS > Oradynaset > METHODS > movenext, edit
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

The code in these examples show you how to copy a portion of Sound from one clip to another.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Copy All or Part of a LOB to Another LOB

/* Note that the example procedure copyLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE copyLOB_proc IS
   Dest_loc     BLOB;
   Src_loc      BLOB;
   Amount       NUMBER;
   Dest_pos     NUMBER;
   Src_pos      NUMBER;
BEGIN
   SELECT Sound INTO Dest_loc FROM Multimedia_tab
      WHERE Clip_ID = 2 FOR UPDATE;
   /* Select the LOB: */
   SELECT Sound INTO Src_loc FROM Multimedia_tab
      WHERE Clip_ID = 1;
   /* Opening the LOBs is optional: */
   DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Copies the LOB from the source position to the destination position: */
   DBMS_LOB.COPY(Dest_loc, Src_loc, Amount, Dest_pos, Src_pos);
   /* Closing LOBs is mandatory if you have opened them: */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Src_loc);
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Copy All or Part of a LOB to Another 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;
}

/* This function copies part of the Source LOB into a specified position
   in the destination LOB 
 */
void copyAllPartLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCIBlobLocator *Dest_loc, *Src_loc;
  int Amount = 1000;                                    /* <Amount to Copy> */
  int Dest_pos = 100;                     /*<Position to start copying into> */
  int Src_pos = 1;                        /* <Position to start copying from> */

  /* 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 the destination and source locators\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 */
  printf (" open the destination locator (optional)\n");
  checkerr (errhp, OCILobOpen(svchp, errhp, Dest_loc, OCI_LOB_READWRITE)); 
  printf (" open the source locator (optional)\n");
  checkerr (errhp, OCILobOpen(svchp, errhp, Src_loc, OCI_LOB_READONLY));
  
  printf (" copy the lob (amount) from the source to destination\n");
  checkerr (errhp, OCILobCopy(svchp, errhp, Dest_loc, Src_loc,
                              Amount, Dest_pos, Src_pos));

  /* Closing the LOBs is Mandatory if they have been Opened */
  printf(" close the locators\n");
  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): Copy All or Part of a LOB to Another LOB

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

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  DEST           SQL-BLOB.
       01  SRC            SQL-BLOB.
     
      * Define the amount to copy.
      * This value has been chosen arbitrarily: 
       01  AMT            PIC S9(9) COMP VALUE 10.
      * Define the source and destination position.
      * These values have been chosen arbitrarily: 
       01  SRC-POS        PIC S9(9) COMP VALUE 1.
       01  DEST-POS       PIC S9(9) COMP VALUE 1.

      * The return value from PL/SQL function: 
       01  RET            PIC S9(9) COMP.
           EXEC SQL INCLUDE SQLCA END-EXEC.
       PROCEDURE DIVISION.
       COPY-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.
           DISPLAY "Source and destination LOBs are open.".

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
                SELECT SOUND INTO :SRC
                FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 END-EXEC.
           DISPLAY "Source LOB populated.".
           EXEC SQL
                SELECT SOUND INTO :DEST
                FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 2 FOR UPDATE END-EXEC.
           DISPLAY "Destination LOB populated.".
 
      * 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.
           DISPLAY "Source and destination LOBs are open.".

      * Copy the desired amount
           EXEC SQL 
                LOB COPY :AMT FROM :SRC AT :SRC-POS
                TO :DEST AT :DEST-POS END-EXEC.
           DISPLAY "Src LOB copied to destination LOB.".

      * Execute PL/SQL to get COMPARE functionality
      * to make sure that the BLOBs are identical
           EXEC SQL EXECUTE
             BEGIN
               :RET := DBMS_LOB.COMPARE(:SRC,:DEST,:AMT,1,1); END; END-EXEC.

           IF RET = 0
      *        Logic for equal BLOBs goes here
               DISPLAY "BLOBs are equal"
           ELSE
      *        Logic for unequal BLOBs goes here
               DISPLAY "BLOBs are not equal"
           END-IF.

           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++): Copy All or Part of a LOB to Another LOB

#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 copyLOB_proc()
{
  OCIBlobLocator *Dest_loc, *Src_loc;
  int Amount = 5;
  int Dest_pos = 10;
  int Src_pos = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the LOB locators: */
  EXEC SQL ALLOCATE :Dest_loc;
  EXEC SQL ALLOCATE :Src_loc;
  /* Select the LOBs: */
  EXEC SQL SELECT Sound INTO :Dest_loc
           FROM Multimedia_tab WHERE Clip_ID = 2 FOR UPDATE;
  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;
  /* Copies the specified Amount from the source position in the source
     LOB to the destination position in the destination LOB: */
  EXEC SQL LOB COPY :Amount
               FROM :Src_loc AT :Src_pos TO :Dest_loc AT :Dest_pos;
  /* 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;
  copyLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Copy All or Part of a LOB to Another LOB

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.Clone

'Go to next row and copy LOB

OraDyn.MoveNext

OraDyn.Edit
OraSound1.Copy OraSoundClone, OraSoundClone.Size, 1, 1
OraDyn.Update

Java (JDBC): Copy All or Part of a LOB to Another LOB

 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_100
{

  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
    {
     final int AMOUNT_TO_COPY = 2000;
     ResultSet rset = null;
     BLOB dest_loc = null;
     BLOB src_loc = null;
     InputStream in = null;
     OutputStream out = null;
     byte[] buf = new byte[AMOUNT_TO_COPY];
     rset = stmt.executeQuery (
          "SELECT sound FROM multimedia_tab WHERE clip_id = 1");
   if (rset.next())
   {
     src_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   in = src_loc.getBinaryStream();
   
       rset = stmt.executeQuery (
          "SELECT sound FROM multimedia_tab WHERE clip_id = 2 FOR UPDATE");
   if (rset.next())
   {
     dest_loc = ((OracleResultSet)rset).getBLOB (1);
   }
   out = dest_loc.getBinaryOutputStream();
   

   // read AMOUNT_TO_COPY bytes into buf from stream, starting from offset 0: 
   in.read(buf, 0, AMOUNT_TO_COPY);

   // write AMOUNT_TO_COPY bytes from buf into output stream, starting at offset 
0: 
   out.write(buf, 0, AMOUNT_TO_COPY);

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

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