Internal Persistent LOBs, 25 of 42

Copy a LOB Locator

Figure 9-28 Use Case Diagram: Copy a LOB Locator

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

Purpose

This procedure describes how to copy a LOB locator.

Usage Notes

Not applicable.

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): Refer to the "Read-Consistent Locators" in Chapter 5, "Advanced Topics" for information on assigning one lob locator to another.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OciLobAssign, OciLobIsEqual.
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- ALLOCATE, LOB ASSIGN
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- SELECT, LOB ASSIGN
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > copy
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 show how to copy one locator to another involving the video frame (Frame). Note how different locators may point to the same or different, current or outdated data.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Copy a LOB Locator

Note:
Assigning one LOB to another using PL/SQL entails using the ":=" sign. This is an advanced topic that is discussed in more detail under the heading "Read-Consistent Locators".

/* Note that the example procedure lobAssign_proc is not part of the 
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE lobAssign_proc IS 
  Lob_loc1    blob; 
  Lob_loc2    blob; 
BEGIN 
  SELECT Frame INTO Lob_loc1 FROM Multimedia_tab where Clip_ID = 1 FOR UPDATE; 
   /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the lob 
     at this point in time. */ 
  Lob_loc2 := Lob_loc1; 
  /* When you write some data to the lob through Lob_loc1, Lob_loc2 will not see 
     the newly written data whereas Lob_loc1 will see the new data. */ 
END;

C (OCI): Copy a LOB Locator

/* Select the locator */
sb4 select_lock_frame_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
    (text *)"SELECT Frame 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 assignLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *dest_loc, *src_loc;
  boolean       isEqual;

  /* 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 and lock a frame locator\n");
  select_lock_frame_locator(src_loc, errhp, svchp, stmthp);/* source locator */
 
  /* Assign src_loc to dest_loc thereby saving a copy of the value of the LOB
     at this point in time. 
   */
  printf(" assign the src locator to dest locator\n");
  checkerr (errhp, OCILobAssign(envhp, errhp, src_loc, &dest_loc)); 

  /* When you write some data to the LOB through Lob_loc1, Lob_loc2 will not
     see the newly written data whereas Lob_loc1 will see the new data. 
   */

  /* Call OCI to see if the two locators are Equal */

  printf (" check if Lobs are Equal.\n");
  checkerr (errhp, OCILobIsEqual(envhp, src_loc, dest_loc, &isEqual));
  if (isEqual)
  {
    /* ... The LOB locators are Equal */
    printf(" Lob Locators are equal.\n");
  }
  else
  {
    /* ... The LOB locators are not Equal */
    printf(" Lob Locators are NOT Equal.\n");
  }

  /* Note that in this example, the LOB locators will be Equal */

  /* 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 a LOB Locator

       IDENTIFICATION DIVISION.
       PROGRAM-ID. COPY-LOCATOR.
       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.
       COPY-BLOB-LOCATOR.
           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 FRAME INTO :SRC
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 2 FOR UPDATE
           END-EXEC.
            EXEC SQL LOB ASSIGN :SRC TO :DEST END-EXEC.

      * When you write data to the LOB through SRC, DEST will
      * not see the newly written data

       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 a LOB Locator

#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 lobAssign_proc()
{
  OCIBlobLocator *Lob_loc1, *Lob_loc2;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT Frame INTO :Lob_loc1
           FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE;
  /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the
     LOB at this point in time: */
  EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2;
  /* When you write some data to the LOB through Lob_loc1, Lob_loc2 will not
     see the newly written data whereas Lob_loc1 will see the new data: */
}

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

Visual Basic (OO4O)Copy a LOB Locator

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
 
OraDyn.MoveNext
 
'Copy 1000 bytes of LOB values OraSoundClone to OraSoundl at OraSoundl
'offset 100: 
OraDyn.Edit
OraSound1.Copy OraSoundClone, 1000, 100
OraDyn.Update

Java (JDBC): Copy a LOB Locator

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_104
{
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_loc1 = null;
     BLOB lob_loc2 = null;
     ResultSet rset = stmt.executeQuery (
         "SELECT frame FROM multimedia_tab WHERE clip_id = 1");
   if (rset.next())
   {
     lob_loc1 = ((OracleResultSet)rset).getBLOB (1);
   }

   // When you write data to LOB through lob_loc1,lob_loc2 will not see changes
   lob_loc2 = lob_loc1;
   stmt.close();
   conn.commit();
   conn.close();

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