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 |
|
Temporary LOBs, 14 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to see if a pattern exists in a temporary LOB (instr).
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:
The following examples examine the storyboard text to see if the string "children" is present.
Examples are provided in the following programmatic environments:
/* Note that the example procedure instringTempLOB_proc is not part of the DBMS_LOB package. */ CREATE OR REPLACE PROCEDURE instringTempLOB_proc IS Lob_loc CLOB; Temp_clob CLOB; Pattern VARCHAR2(30) := 'children'; Position INTEGER := 0; Offset INTEGER := 1; Occurrence INTEGER := 1; BEGIN /* Create the temp LOB and copy a CLOB into it: */ DBMS_LOB.CREATETEMPORARY(Temp_clob,TRUE); SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; DBMS_LOB.OPEN(Temp_clob,DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(Lob_loc,DBMS_LOB.LOB_READONLY); /* Copy the CLOB into the temp CLOB: */ DBMS_LOB.COPY(Temp_clob,Lob_loc,DBMS_LOB.GETLENGTH(Lob_loc),1,1); /* Seek the pattern in the temp CLOB: */ Position := DBMS_LOB.INSTR(Temp_clob, Pattern, Offset, Occurrence); IF Position = 0 THEN DBMS_OUTPUT.PUT_LINE('Pattern not found'); ELSE DBMS_OUTPUT.PUT_LINE('The pattern occurs at '|| position); END IF; DBMS_LOB.CLOSE(Lob_loc); DBMS_LOB.CLOSE(Temp_clob); /* Free the temporary LOB: */ DBMS_LOB.FREETEMPORARY(Temp_clob); END;
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/cobol/tpattern
IDENTIFICATION DIVISION. PROGRAM-ID. CLOB-INSTR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 CLOB1 SQL-CLOB. 01 TEMP-CLOB SQL-CLOB. 01 PATTERN PIC X(8) VALUE "children". 01 BUFFER2 PIC X(32767) VARYING. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 OCCURRENCE PIC S9(9) COMP VALUE 1. 01 LEN PIC S9(9) COMP. 01 POS PIC S9(9) COMP. 01 ORASLNRD PIC 9(4). EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC. PROCEDURE DIVISION. CLOB-INSTR. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the CLOB locator: EXEC SQL ALLOCATE :CLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC. EXEC ORACLE OPTION (SELECT_ERROR=NO) END-EXEC. EXEC SQL SELECT STORY INTO :CLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC. EXEC SQL ALLOCATE :TEMP-CLOB END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-CLOB END-EXEC. * Open the CLOB for READ ONLY: EXEC SQL LOB OPEN :CLOB1 READ ONLY END-EXEC. * Use LOB describe to get the length of CLOB1: EXEC SQL LOB DESCRIBE :CLOB1 GET LENGTH INTO :LEN END-EXEC. EXEC SQL LOB COPY :LEN FROM :CLOB1 TO :TEMP-CLOB END-EXEC. * Execute PL/SQL to get INSTR functionality: EXEC SQL EXECUTE BEGIN :POS := DBMS_LOB.INSTR(:TEMP-CLOB,:PATTERN, :OFFSET, :OCCURRENCE); END; END-EXEC. IF POS = 0 * Logic for pattern not found here DISPLAY "Pattern was not found" ELSE * Pos contains position where pattern is found DISPLAY "Pattern was found" END-IF. * Close and free the LOBs: EXEC SQL LOB CLOSE :CLOB1 END-EXEC. EXEC SQL FREE :TEMP-CLOB END-EXEC. EXEC SQL LOB FREE TEMPORARY :TEMP-CLOB END-EXEC. EXEC SQL FREE :TEMP-CLOB END-EXEC. END-OF-CLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :CLOB1 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.
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/proc/tpattern
#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 instringTempLOB_proc() { OCIClobLocator *Lob_loc, *Temp_loc; char *Pattern = "The End"; unsigned int Length; int Position = 0; int Offset = 1; int Occurrence = 1; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Initialize the Persistent LOB: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Story INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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_loc READ ONLY; EXEC SQL LOB OPEN :Temp_loc READ WRITE; /* Determine the Length of the Persistent LOB: */ EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH into :Length; /* Copy the Persistent LOB into the Temporary LOB: */ EXEC SQL LOB COPY :Length FROM :Lob_loc TO :Temp_loc; /* Seek the Pattern using DBMS_LOB.INSTR() in a PL/SQL block: */ EXEC SQL EXECUTE BEGIN :Position := DBMS_LOB.INSTR(:Temp_loc, :Pattern, :Offset, :Occurrence); END; END-EXEC; if (0 == Position) printf("Pattern not found\n"); else printf("The pattern occurs at %d\n", Position); /* Closing the LOBs is mandatory if you have opened them: */ EXEC SQL LOB CLOSE :Lob_loc; 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_loc; EXEC SQL FREE :Temp_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; instringTempLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|