tailieunhanh - Teach Yourself PL/SQL in 21 Days- P10

Teach Yourself PL/SQL in 21 Days- P10: Welcome to Sams Teach Yourself PL/SQL in 21 Days, Second Edition. The fact that you purchased this book indicates that you have an interest in learning the PL/SQL language, which is Oracle’s relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle8i database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features | Leveraging Large Object Types 427 I The second part of the procedure selects the row where text will be added locks the row for updating assigns the starting position to the length of the contents 1 so no data is overwritten and calls the WRITE procedure. This transaction is then committed. Analyzing the Contents of an Internal LOB In this section you can analyze the contents of an internal LOB by working with the functions INSTR and SUBSTR. Execute the code in Listing and make sure that you have entered SET SERVEROUTPUT ON at the SQL Plus prompt so you can see output as the program executes. Input Listing Extracting and Matching Data Inside CLOBs 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 DECLARE This PL SQL block finds patterns in a CLOB. It also extracts part of the data from a CLOB with SUBSTR Source_Lob CLOB v_Pattern VARCHAR2 6 Oracle1 v_Starting_Location INTEGER 1 v_Nth_Occurrence INTEGER 1 v_Position INTEGER v_Extract_Amount INTEGER v_Buffer VARCHAR2 100 BEGIN -- Search for 1st Occurrence of Oracle in Row 5 SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX 5 v_Position Source_LOB v_Pattern v_Starting_Location v_Nth_Occurrence The first occurrence starts at position i v_Position -- Search for 2nd Occurrence of Oracle in Row 5 v_Nth_Occurrence 2 SELECT CLOB_LOCATOR into Source_LOB FROM LOBS WHERE LOB_INDEX 5 v_Position Source_LOB v_Pattern v_Starting_Location v_Nth_Occurrence The second occurrence starts at position i v_Position -- Extract part of the data from a CLOB continues 14 I 428 Day 14 Listing continued 35 SELECT CL0B_L0CAT0R into Source_L0B 36 FROM LOBS 37 WHERE L0B_INDEX 6 38 v_Buffer Source_L0B 11 v_Starting_Location 39 The substring extracted is 1 v_Buffer 40 41 END 42 Output Analysis The first occurrence starts at position 16 The second occurrence starts at .