Release 1 (9.0.1)
Part Number A88879-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Internal Persistent LOBs, 26 of 43
|
See:
"Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations. |
This procedure describes how to copy a LOB locator.
Not applicable.
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:
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 are provided in the following programmatic environments:
|
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". |
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/pls/icopyloc
/* 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;
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/oci/icopyloc.
/* 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; }
This script is provided at $ORACLE_HOME/rdbms/demo/lobs/cobol/icopyloc
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.
You can find this script at $ORACLE_HOME/rdbms/demo/lobs/proc/icopyloc
#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; }
You can find this script at $ORACLE_HOME/rdbms/demo/lobs/vbasic/icopyloc
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
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/java/iccopyloc.
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(); } } }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|