Internal Persistent LOBs, 11 of 42

INSERT Row by Initializing a LOB Locator Bind Variable

Figure 9-14 Use Case Diagram: INSERT Row by Initializing a LOB Locator Bind Variable

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

Purpose

This procedure inserts a row by initializing a LOB locator bind variable.

Usage Notes

See Chapter 7, "Modeling and Design", "Binds Greater Than 4,000 Bytes in INSERTs and UPDATEs", for usage notes and examples on using binds greater then 4,000 bytes in INSERTs and UPDATEs.

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:

SQL: Oracle8i SQL Reference , "Chapter 7, SQL Statements" -- INSERT
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions.
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide , Appendix F, "Embedded SQL Statements and Precompiler Directives" -- INSERT
C/C++ (Pro*C): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- INSERT
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > Objects > Oradynaset
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

In the following examples we use a LOB locator bind variable to take Sound data in one row of Multimedia_tab and insert it into another row.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Insert Row by Initializing a LOB Locator Bind Variable

/* Note that the example procedure insertUseBindVariable_proc is not part of the 
   DBMS_LOB package.   */
CREATE OR REPLACE PROCEDURE insertUseBindVariable_proc 
   (Rownum IN NUMBER, Blob_loc IN BLOB) IS
BEGIN
   INSERT INTO Multimedia_tab (Clip_ID, Sound) VALUES (Rownum, Blob_loc);
END;

DECLARE
   Blob_loc  BLOB;
BEGIN
   /* Select the LOB from the row where Clip_ID = 1, 
      Initialize the LOB locator bind variable: */
   SELECT Sound INTO Blob_loc
      FROM Multimedia_tab
      WHERE Clip_ID = 1;
  /* Insert into the row where Clip_ID = 2: */
  insertUseBindVariable_proc (2, Blob_loc);
  COMMIT;
END;

C (OCI): Insert Row by Initializing a LOB Locator Bind Variable

/* Select the locator into a locator variable */

sb4 select_MultimediaLocator (Lob_loc, errhp, stmthp, svchp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCIStmt       *stmthp; 
OCISvcCtx     *svchp;
{
  
  OCIDefine *defnp1;

  text  *sqlstmt = 
    (text *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1";

  /* Prepare the SQL statement */     
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

  /* Define the column being selected */ 
  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 and fetch one row */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
  return (0);

}
/* Insert the selected Locator into table using Bind Variables.
   This function selects a locator from the Multimedia_tab and inserts
   it into the same table in another row.
 */
void insertUseBindVariable (envhp, errhp, svchp, stmthp)
OCIEnv        *envhp; 
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;
{
  int            clipid;
  OCILobLocator *Lob_loc;
  OCIBind       *bndhp2;
  OCIBind       *bndhp1;

  text          *insstmt = 
   (text *) "INSERT INTO Multimedia_tab (Clip_ID,  Sound) VALUES (:1, :2)";
   

  /* Allocate locator resources */
  (void) OCIDescriptorAlloc((dvoid *) envhp, 
                            (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, 
                            (size_t) 0, (dvoid **) 0);

  /* Select a LOB locator from the Multimedia Table */
  select_MultimediaLocator(Lob_loc, errhp, stmthp, svchp); 


  /* Insert the locator into the Multimedia_tab with Clip_ID=2 */
  clipid = 2;
     
  /* Prepare the SQL statement */
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) 
                                  strlen((char *) insstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

  /* Binds the bind positions */
  checkerr (errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1,
                                (dvoid *) &clipid, (sb4) sizeof(clipid),
                                SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));
  checkerr (errhp, OCIBindByPos(stmthp, &bndhp2, errhp, (ub4) 2,
                                (dvoid *) &Lob_loc, (sb4) 0,  SQLT_BLOB,
                                (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

  /* Execute the SQL statement */
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));

  /* Free LOB resources*/
  OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB);

}

COBOL (Pro*COBOL): Insert Row by Initializing a LOB Locator Bind Variable

       IDENTIFICATION DIVISION.
       PROGRAM-ID. INSERT-LOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01 BLOB1 SQL-BLOB.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       INSERT-LOB.
    
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Initialize the BLOB locator
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.

      * Populate the LOB
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
              SELECT SOUND INTO :BLOB1
                 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC.
  
      * Insert the value with CLIP_ID of 2.
           EXEC SQL 
              INSERT INTO MULTIMEDIA_TAB (CLIP_ID, SOUND)
                 VALUES (2, :BLOB1)END-EXEC.

      * Free resources held by locator
       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): Insert Row by Initializing a LOB Locator Bind Variable

#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 insertUseBindVariable_proc(Rownum, Lob_loc)
   int Rownum;
   OCIBlobLocator *Lob_loc;
{
   EXEC SQL WHENEVER SQLERROR DO Sample_Error();
   EXEC SQL INSERT INTO Multimedia_tab (Clip_ID, Sound)
      VALUES (:Rownum, :Lob_loc);
}
void insertBLOB_proc()
{
   OCIBlobLocator *Lob_loc;

   /* Initialize the BLOB Locator: */
   EXEC SQL ALLOCATE :Lob_loc;
   /* Select the LOB from the row where Clip_ID = 1: */
   EXEC SQL SELECT Sound INTO :Lob_loc
      FROM Multimedia_tab WHERE Clip_ID = 1;
   /* Insert into the row where Clip_ID = 2: */
   insertUseBindVariable_proc(2, Lob_loc);
   /* Release resources held by the locator: */
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Insert Row by Initializing a LOB Locator Bind Variable

Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBLOB
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT)
Set OraSound1 = OraDyn.Fields("Sound").Value
'Clone it for future reference
Set OraSoundClone = OraSound1  

'Go to Next row
OraDyn.MoveNext

'Lets update the current row and set the LOB to OraSoundClone
OraDyn.Edit
Set OraSound1 = OraSoundClone
OraDyn.Update

Java (JDBC): Insert Row by Initializing a LOB Locator Bind Variable


// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
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_31
{
  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 = stmt.executeQuery (
          "SELECT sound FROM multimedia_tab WHERE clip_id = 1");
       if (rset.next())
       {
          // retrieve the LOB locator from the ResultSet
          BLOB sound_blob = ((OracleResultSet)rset).getBLOB (1);
          OraclePreparedStatement ops = 
          (OraclePreparedStatement) conn.prepareStatement(
             "INSERT INTO multimedia_tab (clip_id, sound) VALUES (2, ?)");
          ops.setBlob(1, sound_blob);
          ops.execute();
          conn.commit();
          conn.close();
       }
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}