Internal Persistent LOBs, 10 of 43

Inserting a Row by Initializing a LOB Locator Bind Variable

Figure 10-14 Use Case Diagram: Inserting a Row by Initializing a LOB Locator Bind Variable

Text description of adl10p13.gif follows

Text description of the illustration adl10p13.gif

See:

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

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 Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

SQL: Oracle9i SQL Reference , "Chapter 7, SQL Statements" -- INSERT
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions.
C++ (OCCI): Oracle C++ Interface Programmer's Guide
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide , Appendix F, "Embedded SQL Statements and Precompiler Directives" -- INSERT
C/C++ (ProC/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): 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

In the following examples you 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): Inserting a Row by Initializing a LOB Locator Bind Variable
C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable
C++ (OCCI): No example is provided with this release.
COBOL (Pro*COBOL): Inserting a Row by Initializing a LOB Locator Bind Variable
C/C++ (ProC/C++): Inserting a Row by Initializing a LOB Locator Bind Variable
Visual Basic (OO4O): Inserting a Row by Initializing a LOB Locator Bind Variable
Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable

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

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

/* 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): Inserting a Row by Initializing a LOB Locator Bind Variable

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

/* 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): Inserting a Row by Initializing a LOB Locator Bind Variable

This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/iinsert

       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++ (ProC/C++): Inserting a Row by Initializing a LOB Locator Bind Variable

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

#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): Inserting a Row by Initializing a LOB Locator Bind Variable

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

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): Inserting a Row by Initializing a LOB Locator Bind Variable

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


// 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();
    }
  }
}