Analyzing the Contents of an Internal LOB

In the next exercise, you analyze the contents of an internal LOB. You will work with the functions INSTR and SUBSTR. Execute the code in Listing 15.10. Make sure that you have entered SET SERVEROUTPUT ON at the SQL*Plus prompt so that you can see output as the program executes.

INPUT
Listing 15.10. Extracting and matching data inside CLOBs.

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) := `Oracle';
    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 := DBMS_LOB.INSTR(Source_LOB,v_Pattern,
         v_Starting_Location,v_Nth_Occurrence);
    DBMS_OUTPUT.PUT_LINE(`The first occurrence starts at position: `
         || 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 := DBMS_LOB.INSTR(Source_LOB,v_Pattern,
         v_Starting_Location,v_Nth_Occurrence);
    DBMS_OUTPUT.PUT_LINE(`The second occurrence starts at position: `
         || v_Position);

-- Extract part of the data from a CLOB
   SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6;
   v_Buffer := DBMS_LOB.SUBSTR(Source_LOB,11,v_Starting_Location);
   DBMS_OUTPUT.PUT_LINE(`The substring extracted is: `  || v_Buffer);

END;

OUTPUT
Your output should be

The first occurrence starts at position: 16
The second occurrence starts at position: 50
The substring extracted is: Oracle Data

ANALYSIS
The procedure begins by selecting the data from row 5 and reading the locator into the Source_Lob variable. Using the INSTR function, the pattern `Oracle', assigned to the v_Pattern variable, is searched for the first occurrence, specified by the v_Nth_Occurrence variable. The starting location is defaulted to the first position in the CLOB, stored in the v_Starting_Location variable. The process is repeated except that you are now searching for the second occurrence of `Oracle' in the CLOB.

The last part of the procedure extracts 11 characters from row 6 and stores this in v_Buffer, which is then displayed to the screen.

Using TRIM and ERASE to Edit CLOBs

The last exercise will demonstrate the TRIM and ERASE procedures. Execute the code in List-ing 15.11.

INPUT
Listing 15.11. Reducing data in CLOBs.

DECLARE
/* This erases the data in Row 6, and trims the data in
   row 5 to one occurrence of the book title. */

    Source_Lob   CLOB;
    Erase_Amount INTEGER;
    Trim_Amount INTEGER;

BEGIN
-- Erase the data completely in Row 6

    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6 FOR UPDATE; -- Locks Row for Update
    Erase_Amount :=DBMS_LOB.GETLENGTH(Source_LOB);
    DBMS_LOB.ERASE(Source_LOB,Erase_Amount,1);

--Reduce Data in Row 5 to one instance of Book Title
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5 FOR UPDATE;

    TRIM_AMOUNT := DBMS_LOB.GETLENGTH(Source_LOB) / 2;
    DBMS_LOB.TRIM(Source_LOB, TRIM_AMOUNT);
    COMMIT;

END;

To verify that the ERASE and TRIM procedures worked, at the SQL prompt type

SELECT * FROM LOBS;

OUTPUT
Your output should be

LOB_INDEX CLOB_LOCATOR
--------- -----------------------------------------------------------
        1 Teach Yourself Oracle8 in 21 Days
        2 Oracle Data Warehousing Unleashed
        3 Teach Yourself Database Development with Oracle in 21 Days
        4 Oracle Unleashed 2E
        5 Teach Yourself Oracle8 in 21 Days
        6

ANALYSIS
Three variables are declared: Source_Lob holds the locator for the CLOBs you will alter. The variable Erase_Amount will hold the number of bytes to erase from row 6. The Trim_Amount will store the number of bytes that should remain in row 5. The procedure starts by reading the locator for the CLOB into the variable Source_Lob. Erase_Amount is assigned the value of the length of the data in row 6 by using the GETLENGTH function. The ERASE procedure is called and passes the CLOB locator, the total bytes to erase, and the starting position for erasing the data, which is hard-coded to a value of 1 in this example.

The second half of the block will reduce the data in row 5 by half. The locator for the CLOB in row 5 is read into the variable Source_Lob. The Amount of data to remain is calculated by taking the total length of the data by using the GETLENGTH function and dividing this value by 2. The TRIM procedure is called, passing the locator and the amount of bytes to remain. The transactions are then committed.

Summary

In this lesson, you learned about recursion. Recursion is a method in which a function continuously calls itself until some exit condition occurs. One of the problems with using recursion is that possible logic errors could cause an infinite loop. Although recursion code is short and brief, it is rarely used because it is hard to follow and causes poor execution when the number of calls to the function increases. All problems that can be solved with recursion can also be solved using a different approach.

You also learned how Oracle handles large objects, referred to as LOBs. The two types of LOBs are internal and external LOBs. External LOBs, called BFILEs, are files accessible to the operating system, rather than data stored in a table. Oracle stores a pointer to the location of the file in a table, which contains the path and filename of the file. The path is defined by the CREATE DIRECTORY SQL statement. If the locator is not updated and the file is moved or deleted, an exception is raised. Internal LOBs can also be binary, character, multicharacter, and fixed width. These have full transactional support and can be committed or rolled back. When you copy an internal LOB, all data is copied and a new locator is entered into the table. LOBs can have a maximum size of 4 gigabytes, or the size of an unsigned long integer.

Q&A

Q What is recursion?
A Recursion is a method in which a function calls itself until some exit condition occurs.
Q When should I use recursion?
A Use recursion when there would be a small number of iterations, no more than 10 to 15.
Q What is the difference between an external and an internal large object?
A Internal large objects are stored within the Oracle database. External large objects are stored and maintained by the operating system.
Q What possible uses are there for large objects?
A You can easily store and track pictures, large text files, and sound files, which can then be used by front-end systems to display or play back the data.
Q How are paths accessed by Oracle?
A A path is defined as a directory object by using the SQL statement CREATE DIRECTORY.

Workshop

You will now have a chance to practice your knowledge of recursion and the DBMS_LOB package. The answers to the quiz and exercise can be found in Appendix A, "Answers."

Quiz

1. Could you use recursion to generate an organizational chart as long as you had the ID of your immediate boss coded in your employee record?
2. Should you use recursion as much as possible?
3. What is the largest size of a large object?
4. Can you write to external files?
5. When copying LOBs from one row to another, is only a new locator copied?

Exercise

Rewrite the code in Listing 15.1, but as a loop instead of a recursive function. Provide the result of 6 factorial (6!).