Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Internal Persistent LOBs, 10 of 43
See:
"Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations. |
This procedure inserts a row by initializing a LOB locator bind variable.
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.
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:
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 are provided in the following programmatic environments:
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;
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);}
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.
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; }
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
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(); } } }
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|