Release 2 (8.1.6)
Part Number A76940-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Large Objects (LOBs) Release 2 (8.1.6) Part Number A76940-01 |
|
Temporary LOBs, 13 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to compare all or part of two temporary LOBs.
Not applicable.
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:
The following examples compare two frames from the archival table VideoframesLib_tab to see whether they are different. Depending on the result of comparison, the examples insert the Frame into the Multimedia_tab.
Examples are provided in the following programmatic environments:
/* Note that the example procedure compareTwoTemporPersistLOBs_proc is not part of the DBMS_LOB package. */ CREATE OR REPLACE PROCEDURE compareTwoTmpPerLOBs_proc IS Lob_loc1 BLOB; Lob_loc2 BLOB; Temp_loc BLOB; Amount INTEGER := 32767; Retval INTEGER; BEGIN /* Select the LOB: */ SELECT Frame INTO Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1; SELECT Frame INTO Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 2; /* Copy a frame into a temp LOB and convert it to a different format */ /* before comparing the frames : */ DBMS_LOB.CREATETEMPORARY(Temp_loc, TRUE); DBMS_LOB.OPEN(Temp_loc, DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY); DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READONLY); /* Copy the persistent LOB into the temp LOB: */ DBMS_LOB.COPY(Temp_loc,Lob_loc2,DBMS_LOB.GETLENGTH(Lob_loc2),1,1); /* Perform some conversion function on the temp LOB before comparing it*/ /* ...some_conversion_format_function(Temp_loc); */ retval := DBMS_LOB.COMPARE(Lob_loc1, Temp_loc, Amount, 1, 1); IF retval = 0 THEN DBMS_OUTPUT.PUT_LINE('Processing for equal frames'); ELSE DBMS_OUTPUT.PUT_LINE('Processing for non-equal frames'); END IF; DBMS_LOB.CLOSE(Temp_loc); DBMS_LOB.CLOSE(Lob_loc1); DBMS_LOB.CLOSE(Lob_loc2); /* Free the temporary LOB now that we are done using it: */ DBMS_LOB.FREETEMPORARY(Temp_loc); END;
IDENTIFICATION DIVISION. PROGRAM-ID. BLOB-COMPARE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BLOB1 SQL-BLOB. 01 BLOB2 SQL-BLOB. 01 TEMP-BLOB SQL-BLOB. 01 RET PIC S9(9) COMP. 01 AMT PIC S9(9) COMP VALUE 5. 01 ORASLNRD PIC 9(4). EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. PROCEDURE DIVISION. BLOB-COMPARE. 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 :BLOB1 END-EXEC. EXEC SQL ALLOCATE :BLOB2 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT FRAME INTO :BLOB1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 END-EXEC. EXEC SQL SELECT FRAME INTO :BLOB2 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 2 END-EXEC. * Allocate and create a temporary LOB: EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB END-EXEC. * Open the BLOBs for READ ONLY, Open temp LOB READ/WRITE: EXEC SQL LOB OPEN :BLOB1 READ ONLY END-EXEC. EXEC SQL LOB OPEN :BLOB2 READ ONLY END-EXEC. EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC. * Copy data from BLOB2 to the temporary BLOB: EXEC SQL LOB COPY :AMT FROM :BLOB2 TO :TEMP-BLOB END-EXEC. * Execute PL/SQL to use its COMPARE functionality: MOVE 5 TO AMT. EXEC SQL EXECUTE BEGIN :RET := DBMS_LOB.COMPARE(:BLOB1,:TEMP-BLOB,:AMT,1,1); END; END-EXEC. IF RET = 0 * Logic for equal BLOBs goes here DISPLAY "BLOBs are equal" ELSE * Logic for unequal BLOBs goes here DISPLAY "BLOBs are not equal" END-IF. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. EXEC SQL LOB CLOSE :BLOB2 END-EXEC. EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC. EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC. EXEC SQL FREE :TEMP-BLOB END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL FREE :BLOB2 END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. MOVE ORASLNR TO ORASLNRD. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
#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 compareTwoTempOrPersistLOBs_proc() { OCIBlobLocator *Lob_loc1, *Lob_loc2, *Temp_loc; int Amount = 128; int Retval; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate the LOB locators: */ EXEC SQL ALLOCATE :Lob_loc1; EXEC SQL ALLOCATE :Lob_loc2; /* Select the LOBs: */ EXEC SQL SELECT Frame INTO :Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1; EXEC SQL SELECT Frame INTO :Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 2; /* Allocate and Create the Temporary LOB: */ EXEC SQL ALLOCATE :Temp_loc; EXEC SQL LOB CREATE TEMPORARY :Temp_loc; /* Opening the LOBs is Optional: */ EXEC SQL LOB OPEN :Lob_loc1 READ ONLY; EXEC SQL LOB OPEN :Lob_loc2 READ ONLY; EXEC SQL LOB OPEN :Temp_loc READ WRITE; /* Copy the Persistent LOB into the Temporary LOB: */ EXEC SQL LOB COPY :Amount FROM :Lob_loc2 TO :Temp_loc; /* Compare the two Frames using DBMS_LOB.COMPARE() from within PL/SQL: */ EXEC SQL EXECUTE BEGIN :Retval := DBMS_LOB.COMPARE(:Lob_loc1, :Temp_loc, :Amount, 1, 1); END; END-EXEC; if (0 == Retval) printf("Frames are equal\n"); else printf("Frames are not equal\n"); /* Closing the LOBs is mandatory if you have opened them: */ EXEC SQL LOB CLOSE :Lob_loc1; EXEC SQL LOB CLOSE :Lob_loc2; EXEC SQL LOB CLOSE :Temp_loc; /* Free the Temporary LOB: */ EXEC SQL LOB FREE TEMPORARY :Temp_loc; /* Release resources held by the locators: */ EXEC SQL FREE :Lob_loc1; EXEC SQL FREE :Lob_loc2; EXEC SQL FREE :Temp_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; compareTwoTempOrPersistLOBs_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|