Lob

13.5. Working with LOBs

The topic of working with large objects is, well, large, and we can't begin to cover every aspect of LOB programming in this chapter. What we can and will do, however, is provide you with a good introduction to the topic of LOB programming aimed especially at PL/SQL developers. We'll discuss some of the issues to be aware of and show examples of fundamental LOB operations. All of this, we hope, will provide you with a good foundation for your future LOB programming endeavors.

Before getting into the meat of this section, please note that all LOB examples are based on the following table definition:

    CREATE TABLE waterfalls (
       falls_name VARCHAR2(80),
       falls_photo BLOB,
       falls_directions CLOB,
       falls_description NCLOB,
       falls_web_page BFILE);

This table contains rows about waterfalls located in Michigan's Upper Peninsula. Figure 13-2 shows the Dryer Hose, a falls near Munising frequented by ice climbers in its frozen state.

Figure 13-2. The Dryer Hose in Munising, Michigan

The table implements one column for each of the four LOB types. Photos consist of large amounts of binary data, so the falls_photo column is defined as a BLOB. Directions and descriptions are text, so those columns are CLOB and NCLOB, respectively. Normally, you'd use either CLOB or NCLOB for both, but we wanted to provide an example that used each LOB type. Finally, the master copy of the web page for each waterfall is stored in an HTML file outside the database. We use a BFILE column to point to that HTML file. We'll use these columns in our examples to demonstrate various facets of working with LOB data in PL/SQL programs.

In our discussion of large objects, we'll frequently use the acronym LOB to refer to CLOBs, BLOBs, NCLOBs, and BFILEs in general. We'll use specific type names only when discussing something specific to a type.

13.5.1. Understanding LOB Locators

Fundamental to working with LOBs is the concept of a LOB locator. A LOB locator is a pointer to large object data in a database. By way of explanation, let's look at what happens when you select a BLOB column into a BLOB PL/SQL variable:

    DECLARE
       photo BLOB;
    BEGIN
       SELECT falls_photo
         INTO photo
         FROM waterfalls
        WHERE falls_name='Dryer Hose';

What, exactly, is in the photo variable after the SELECT statement executes? Is the photo itself retrieved? No. Only a pointer to the photo is retrieved. You end up with the situation shown in Figure 13-3.

Figure 13-3. A LOB locator points to its associated large object data within the database

This is different from the way in which other database column types work. Database LOB columns store LOB locators , and those locators point to the real data stored elsewhere in the database. Likewise, PL/SQL LOB variables hold those same LOB locators, which point to LOB data within the database. To work with LOB data, you first retrieve a LOB locator, and you then use a built-in package named DBMS_LOB to retrieve and/or modify the actual LOB data. For example, to retrieve the binary photo data from the falls_photo BLOB column used in the previous example, you would go through the following steps:

1. Issue a SELECT statement to retrieve the LOB locator for the photo you wish to display.

2. Open the LOB via a call to DBMS_LOB.OPEN.

3. Make a call to DBMS_LOB.GETCHUNKSIZE to get the optimal chunk size to use when reading (and writing) the LOB's value.

4. Make a call to DBMS_LOB.GETLENGTH to get the number of bytes or characters in the LOB value.

5. Make multiple calls to DBMS_LOB.READ in order to retrieve the LOB data.

6. Close the LOB.

Not all of these steps are necessary, and don't worry if you don't understand them fully right now. We'll explain all the steps and operations shortly.

The use of locators might initially appear clumsy. It's a good approach, though, because it obviates the need to return all the data for a given LOB each time that you fetch a row from a table. Imagine how long a fetch would take if up to 128 terabytes of LOB data had to be transferred. Imagine the waste if you needed to access only a small fraction of that data. With Oracle's approach, you fetch locaters, a quick operation, and then you retrieve only the LOB data that you need.

Oracle's LOB Documentation

If you are working with LOBs, we strongly recommend that you familiarize yourself with the following portions of Oracle's documentation set:

• Application Developer's GuideLarge Objects. Contains complete information on LOB programming.

• PL/SQL Packages and Types Reference. See the chapter on the DBMS_LOB package.

• SQL Reference. The "Datatypes" section in Chapter 2, Basic Elements of Oracle SQL, contains important information about LOBs.

This is not an exhaustive list of LOB documentation, but you'll find all the essential information in these sources.

13.5.2. Empty Versus NULL LOBs

Now that you understand the distinction between a LOB locator and the value to which it points, you need to wrap your mind around another key concept: the empty LOB. An empty LOB is what you have when a LOB locator doesn't point to any LOB data. This is not the same as a NULL LOB, which is a LOB column (or variable) that doesn't hold a LOB locator. Clear as mud, right? Let's look at some example code:

    SQL> DECLARE
      2     directions CLOB;
      3  BEGIN
      4     IF directions IS NULL THEN
      5        DBMS_OUTPUT.PUT_LINE('directions is NULL');
      6     ELSE
      7        DBMS_OUTPUT.PUT_LINE('directions is not NULL');
      8     END IF;
      9  END;
     10  /

    directions is NULL

Here we've declared a CLOB variable, which is NULL because we haven't yet assigned it a value. You're used to this behavior, right? It's the same with any other datatype: declare a variable without assigning a value and the result is NULL. Let's press ahead and create a LOB locator for the variable. The following code uses a call to EMPTY_CLOB in conjunction with an INSERT statement to create a LOB locator. Subsequently, a SELECT statement retrieves that same LOB locator from the database and places it into the directions variable. We'll talk more about the reasons for this somewhat cumbersome approach in the next section. For now, focus on the output from this code snippet.

First the code:

    DECLARE
       directions CLOB;
    BEGIN
       --Delete any existing rows for 'Munising Falls' so that this
       --example can be executed multiple times
       DELETE
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator
       INSERT INTO waterfalls
                 (falls_name,falls_directions)
          VALUES ('Munising Falls',EMPTY_CLOB(  ));

       --Retrieve the LOB locater created by the previous INSERT statement
       SELECT falls_directions
         INTO directions
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       IF directions IS NULL THEN
          DBMS_OUTPUT.PUT_LINE('directions is NULL');
       ELSE
          DBMS_OUTPUT.PUT_LINE('directions is not NULL');
       END IF;

       DBMS_OUTPUT.PUT_LINE('Length = '
                            || DBMS_LOB.GETLENGTH(directions));
    END;

and now the output:

    directions is not NULL
    Length = 0

EMPTY_CLOB is a built-in function that returns a CLOB locator, which we've stored in the database and then retrieved. Our CLOB variable is now no longer NULL because it contains a value: the locator. However, the results from the call to DBMS_LOB.GETLENGTH indicate that there is no data being pointed to; thus, the directions CLOB is an empty LOB. This is important to understand because the way in which you test for the presence or absence of data is more complicated for a LOB than it is for other datatypes.

A simple IS NULL test suffices for traditional scalar datatypes:

    IF some_number IS NULL THEN
       --You know there is no data

If an IS NULL test on a NUMBER or a VARCHAR2 (or any other scalar type) returns TRUE, you know that the variable holds no data. With LOBs, however, you not only need to check for nullity (no locator), but you also need to check the length:

    IF some_clob IS NULL THEN
       --There is no data
    ELSEIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN
       --There is no data
    ELSE
       --Only now is there data
    END IF;

As illustrated in this example, you can't check the length of a LOB without first having a locator. Thus, to determine whether a LOB holds data, you must first check for the presence of a locator using an IS NULL test, and then check for a non-zero length.

13.5.3. Creating a LOB

In the previous section, we used the following code to create a LOB locator:

    --Insert a new row using EMPTY_CLOB() to create a LOB locator
    INSERT INTO waterfalls
              (falls_name,falls_directions)
       VALUES ('Munising Falls',EMPTY_CLOB());

We then issued a SELECT statement to retrieve the new locator from a table. That sure seems like a cumbersome approach, doesn't it? You're probably wondering why we didn't just do the following:

    directions := EMPTY_CLOB();

It turns out that there is a very good reason why we took the approach we did. Remember that a CLOB variable (such as directions) really holds a LOB locator that points to the actual LOB data in the database. The key words to focus on here are in the database. LOBs do not exist in memory. They exist on disk in one or more of the database files. Furthermore, the physical details of how and where a given LOB is stored are specified as part of the table definition of the table containing the LOB. When we invoke the EMPTY_CLOB( ) function, we get an empty LOB (perhaps "empty LOB locator" would be a better term) that does not point to any specific location on disk. It's not until we store that empty LOB into a column in a database table that Oracle finishes filling in the blanks to produce a locator that we can use. When we insert our empty LOB into the waterfalls table, Oracle sees that the LOB locator is incomplete, decides on a location for the LOB based on storage information that the DBA provided as part of the waterfall table's definition, updates the LOB locator to point to that location, and finally stores the new locator as part of the table row that we are inserting. Only after all that can we actually work with the LOB. Of course, to get the newly completed LOB locator, we need to select it back again from the row that we just inserted.

It's worth noting that you don't necessarily need to embed the call to EMPTY_CLOB( ) within the INSERT statement. We can assign the results of EMPTY_CLOB to a CLOB variable, and then insert that variable into our table:

    --Insert a new row using EMPTY_CLOB() to create a LOB locator
    directions := EMPTY_CLOB();
    INSERT INTO waterfalls
             (falls_name,falls_directions)
      VALUES ('Munising Falls',directions);

Note that even after this code executes, directions is still an empty LOB. A subsequent SELECT statement is necessary to retrieve the updated, nonempty LOB from the database.

When working with BLOBs, use EMPTY_BLOB( ) to create an empty BLOB. Use EMPTY_CLOB( ) for CLOBs and NCLOBs.

Beginning with Oracle8i Database, it is possible to work with LOBs without having to insert rows into your database. You do this using temporary LOBs, which are discussed in the later section, "Temporary LOBs."

13.5.4. Writing into a LOB

Once you have a valid LOB locator, you can write data into that LOB using one of these procedures from the built-in DBMS_LOB package:

DBMS_LOB.WRITE

Allows you to write data randomly into a LOB

DBMS_LOB.WRITEAPPEND

Allows you to append data to the end of a LOB

Following is an extension of the previous examples in this chapter. It begins by creating a LOB locator for the directions column in the waterfalls table. After creating the locator, we use DBMS_LOB.WRITE to begin writing directions to Munising Falls into the CLOB column. We then use DBMS_LOB.WRITEAPPEND to finish the job:

    /* File on web: munising_falls_01.sql */
    DECLARE
       directions CLOB;
       amount BINARY_INTEGER;
       offset INTEGER;
       first_direction VARCHAR2(100);
       more_directions VARCHAR2(500);
    BEGIN
       --Delete any existing rows for 'Munising Falls' so that this
       --example can be executed multiple times
       DELETE
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator
       INSERT INTO waterfalls
                 (falls_name,falls_directions)
          VALUES ('Munising Falls',EMPTY_CLOB(  ));

       --Retrieve the LOB locator created by the previous INSERT statement
       SELECT falls_directions
         INTO directions
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       --Open the LOB; not strictly necessary, but best to open/close LOBs.
       DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);

       --Use DBMS_LOB.WRITE to begin
       first_direction := 'Follow I-75 across the Mackinac Bridge.';
       amount := LENGTH(first_direction);  --number of characters to write
       offset := 1; --begin writing to the first character of the CLOB
       DBMS_LOB.WRITE(directions, amount, offset, first_direction);

       --Add some more directions using DBMS_LOB.WRITEAPPEND
       more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
                       || ' Turn north on M-77 and drive to Seney.'
                       || ' From Seney, take M-28 west to Munising.';
       DBMS_LOB.WRITEAPPEND(directions,
                            LENGTH(more_directions), more_directions);

       --Add yet more directions
       more_directions := ' In front of the paper mill, turn right on H-58.'
                       || ' Follow H-58 to Washington Street. Veer left onto'
                       || ' Washington Street. You''ll find the Munising'
                       || ' Falls visitor center across from the hospital at'
                       || ' the point where Washington Street becomes'
                       || ' Sand Point Road.';
       DBMS_LOB.WRITEAPPEND(directions,
                            LENGTH(more_directions), more_directions);

       --Close the LOB, and we are done.
       DBMS_LOB.CLOSE(directions);
    END;

In this example, we used both WRITE and WRITEAPPEND solely to demonstrate the use of both procedures. Because our LOB had no data to begin with, we could have done all the work using only WRITEAPPEND. Notice that we opened and closed the LOB; while this is not strictly necessary, it is a good idea, especially if you are using Oracle Text. Otherwise, any Oracle Text domain- and function-based indexes will be updated with each WRITE or WRITEAPPEND call, rather than being updated once when you call CLOSE.

In the section on BFILEs, we show how to read LOB data directly from an external operating-system file.

When writing to a LOB, as we have done here, there is no need to update the LOB column in the table. That's because the LOB locator does not change. We did not change the contents of falls_directions (the LOB locator). Rather, we added data to the LOB to which the locator pointed.

LOB updates take place within the context of a transaction. We did not COMMIT in our example code. You should issue a COMMIT after executing the PL/SQL block if you want the Munising Falls directions to remain permanently in your database. If you issue a ROLLBACK after executing the PL/SQL block, all the work done by this block will be undone.

Our example writes to a CLOB column. You write BLOB data in the same manner, except that your inputs to WRITE and WRITEAPPEND should be of the RAW type instead of the VARCHAR2 type.

The following SQL*Plus example shows one way you can see the data just inserted by our example. The next section will show you how to retrieve the data using the various DBMS_LOB procedures.

    SQL> SET LONG 2000
    SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70
    SQL> SELECT falls_directions
      2  FROM waterfalls
      3  WHERE falls_name='Munising Falls';

    FALLS_DIRECTIONS
    ----------------------------------------------------------------------
    Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace
    to Blaney Park. Turn north on M-77 and drive to Seney. From Seney,
    take M-28 west to Munising. In front of the paper mill, turn right on
    H-58. Follow H-58 to Washington Street. Veer left onto Washington
    Street. You'll find the Munising Falls visitor center across from the
    hospital at the point where Washington Street becomes Sand Point Road.

13.5.5. Reading from a LOB

To retrieve data from a LOB, you use the DBMS_LOB.READ procedure . First, of course, you must retrieve the LOB locator. When reading from a CLOB, you specify an offset in terms of characters. Reading begins at the offset that you specify, and the first character of a CLOB is always number 1. When you are working with BLOBs, offsets are in terms of bytes. Note that when you are calling DBMS_LOB.READ, you must specify the number of characters (or bytes) that you wish to read. Given that LOBs are large, it's reasonable to plan on doing more than one read to get at all the data.

The following example retrieves and displays the directions to Munising Falls. We've carefully chosen the number of characters to read both to accommodate DBMS_OUTPUT's line-length restriction and to ensure a nice-looking line break in the final output.

    /* File on web: munising_falls_02.sql */
    DECLARE
       directions CLOB;
       directions_1 VARCHAR2(300);
       directions_2 VARCHAR2(300);
       chars_read_1 BINARY_INTEGER;
       chars_read_2 BINARY_INTEGER;
       offset INTEGER;
    BEGIN
       --Retrieve the LOB locator inserted previously
       SELECT falls_directions
         INTO directions
         FROM waterfalls
        WHERE falls_name='Munising Falls';

       --Begin reading with the first character
       offset := 1;

       --Attempt to read 229 characters of directions, chars_read_1 will
       --be updated with the actual number of characters read
       chars_read_1 := 229;
       DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);

       --If we read 229 characters, update the offset and try to
       --read 255 more.
       IF chars_read_1 = 229 THEN
          offset := offset + chars_read_1;
          chars_read_2 := 255;
          DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
       ELSE
          chars_read_2 := 0;
          directions_2 := '';
       END IF;

       --Display the total number of characters read
       DBMS_OUTPUT.PUT_LINE('Characters read = ' ||
                            TO_CHAR(chars_read_1+chars_read_2));

       --Display the directions
       DBMS_OUTPUT.PUT_LINE(directions_1);
       DBMS_OUTPUT.PUT_LINE(directions_2);
    END;

The output from this code is as follows:

    Characters read = 414
    Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace to Blaney
    Park. Turn north on M-77 and drive to Seney. From Seney, take M-28 west to
    Munising. In front of the paper mill, turn right on H-58. Follow H-58 to
    Washington Street. Veer left onto Washington Street. You'll find the Munising
    Falls visitor center across from the hospital at the point where Washington
    Street becomes Sand Point Road.

The chars_read_1 (amount to read) parameter, which is the second parameter you pass to DBMS_LOB.READ, is an IN OUT parameter, and DBMS_LOB.READ will update it to reflect the number of characters (or bytes) actually read. You'll know you've reached the end of a LOB when the number of characters or bytes read is less than the number you requested. It seems to us a bit inconvenient that the offset is not updated in the same manner. When reading several sequential portions of a LOB, you must update the offset each time based on the number of characters or bytes just read.

You can use DBMS_LOB.GET_LENGTH (lob_locator) to retrieve the length of a LOB. The length is returned as a number of bytes for BLOBs and BFILEs , and as a number of characters for CLOBs.

13.5.6. BFILEs Are Different

As mentioned earlier, the BLOB, CLOB, and NCLOB types represent internal LOBs , meaning that they are stored within the database. A BFILE, on the other hand, is an external LOB type. BFILEs are very different from internal LOBs in three important ways:

• The value of a BFILE is stored in an operating-system file, not within the database.

• BFILEs do not participate in transactions (i.e., changes to a BFILE cannot be rolled back or committed). However, changes to a BFILE locator can be rolled back and committed.

• From within PL/SQL and Oracle in general, you can only read BFILEs. Oracle does not allow you to write BFILE data. You must generate the external filesto which BFILE locators pointcompletely outside of the Oracle database.

When you work with BFILEs in PL/SQL, you still do work with a LOB locator. In the case of a BFILE, however, the locator simply points to a file stored on the server. For this reason, two different rows in a database table can have a BFILE column that points to the same file.

A BFILE locator is composed of a directory alias and a filename. You use the BFILENAME function , which we'll describe shortly, to return a locator based on those two pieces of information. A directory alias is simply an Oracle-specific name for an operating-system directory. Directory aliases allow your PL/SQL programs to work with directories in an operating system-independent manner. If you have the CREATE ANY DIRECTORY privilege, you can create a directory alias and grant access to it as follows:

    CREATE DIRECTORY bfile_data AS 'c:\PLSQL Book\Ch12_Misc_Datatypes';

    GRANT READ ON DIRECTORY bfile_data TO gennick;

Creating directory aliases and dealing with access to those aliases are more database-administration functions than PL/SQL issues, so we won't go too deeply into those topics. The examples here should be enough to get you started. To learn more about directory aliases, talk to your DBA or read the section in Oracle's SQL Reference on the CREATE DIRECTORY command.

13.5.6.1 Creating a BFILE locator

BFILE locators are trivial to create; you simply invoke the BFILENAME function and pass it a directory alias and a filename. Unlike locators for other LOB types, you don't need to store a BFILE locator in the database prior to using it. In the following example, we create a BFILE locator for the HTML file containing the Tannery Falls web page. We then store that locator into the waterfalls table.

    DECLARE
       web_page BFILE;
    BEGIN
       --Delete row for Tannery Falls so this example can
       --be executed multiple times
       DELETE FROM waterfalls WHERE falls_name='Tannery Falls';

       --Invoke BFILENAME to create a BFILE locator
       web_page := BFILENAME('BFILE_DATA','Tannery Falls.htm');

       --Save our new locator in the waterfalls table
       INSERT INTO waterfalls (falls_name, falls_web_page)
          VALUES ('Tannery Falls',web_page);
    END;

A BFILE locator is simply a combination of directory alias and filename. The actual file and directory don't even need to exist. That is, Oracle allows you to create directory aliases for directories that do not yet exist, and BFILENAME allows you to create BFILE locators for files that do not yet exist. There are times when it's convenient to do these things.

The directory name you specify in calls to BFILENAME is case-sensitive, and its case must match that shown by the ALL_DIRECTORIES data dictionary view. We first used lowercase bfile_data in our example, only to be greatly frustrated by errors when we tried to access our external BFILE data (as in the next section). In most cases, you'll want to use all-uppercase for the directory name in a call to BFILENAME.

13.5.6.2 Accessing BFILEs

Once you have a BFILE locator, you can access the data from an external file in much the same manner as you would access a BLOB. The following example retrieves the first 60 bytes of HTML from the Tannery Falls web page. The results, which are of the RAW type, are cast to a character string using the built-in UTL_RAW.CAST_TO_VARCHAR2 function.

    DECLARE
       web_page BFILE;
       html RAW(60);
       amount BINARY_INTEGER := 60;
       offset INTEGER := 1;
    BEGIN
       --Retrieve the LOB locat0r for the web page
       SELECT falls_web_page
         INTO web_page
         FROM waterfalls
        WHERE falls_name='Tannery Falls';

       --Open the locator, read 60 bytes, and close the locator
       DBMS_LOB.OPEN(web_page);
       DBMS_LOB.READ(web_page, amount, offset, html);
       DBMS_LOB.CLOSE(web_page);

       --Uncomment following line to display results in hex
       --DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));

       --Cast RAW results to a character string we can read
       DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
    END;

The output from this code will appear as follows:

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN

The maximum number of BFILEs that can be opened within a session is established by the database initialization parameter, SESSION_MAX_OPEN_FILES. This parameter defines an upper limit on the number of files opened simultaneously in a session (not just BFILEs, but all kinds of files, including those opened using the UTL_FILE package).

Remember that from within the Oracle database, you can only read BFILEs. The BFILE type is ideal when you want to access binary data, such as a collection of images, that is generated outside the database environment. For example, you might upload a collection of images from a digital camera to your server and create a BFILE locator to point to each of those images. You could then access the images from your PL/SQL programs.

13.5.6.3 Using BFILEs to load LOB columns

In addition to allowing you to access binary file data created outside the Oracle database environment, BFILEs provide a convenient means to load data from external files into internal LOB columns. Up through Oracle9i Database Release 1, you could use the DBMS_LOB.LOADFROMFILE function to read binary data from a BFILE and store it into a BLOB column. Oracle9i Database Release 2 introduced the following, much improved, functions:

DBMS_LOB.LOADCLOBFROMFILE

Loads CLOBs from BFILEs. Takes care of any needed character set translation.

DBMS_LOB.LOADBLOBFROMFILE

Loads BLOBs from BFILEs. Does the same thing as DBMS_LOB.LOADFROMFILE, but with an interface that is consistent with that of LOADCLOBFROMFILE.

Imagine that we had directions to Tannery Falls in an external text file named TanneryFalls.directions in a directory pointed to by the BFILE_DATA directory alias. The following example shows how we could use DBMS_LOB.LOADCLOBFROMFILE to load the directions into the falls_directions CLOB column in the waterfalls table:

    /* File on web: munising_falls_03.sql */
    DECLARE
       Tannery_Falls_Directions BFILE
          := BFILENAME('BFILE_DATA','TanneryFalls.directions');
       directions CLOB;
       destination_offset INTEGER := 1;
       source_offset INTEGER := 1;
       language_context INTEGER := DBMS_LOB.default_lang_ctx;
       warning_message INTEGER;
    BEGIN
       --Delete row for Tannery Falls, so this example
       --can run multiple times.
       DELETE FROM waterfalls WHERE falls_name='Tannery Falls';

       --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator
       INSERT INTO waterfalls
                 (falls_name,falls_directions)
          VALUES ('Tannery Falls',EMPTY_CLOB(  ));

       --Retrieve the LOB locator created by the previous INSERT statement
       SELECT falls_directions
         INTO directions
         FROM waterfalls
        WHERE falls_name='Tannery Falls';

       --Open the target CLOB and the source BFILE
       DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
       DBMS_LOB.OPEN(Tannery_Falls_Directions);

       --Load the contents of the BFILE into the CLOB column
       DBMS_LOB.LOADCLOBFROMFILE 
(directions, Tannery_Falls_Directions,
                                 DBMS_LOB.LOBMAXSIZE,
                                 destination_offset, source_offset,
                                 NLS_CHARSET_ID('US7ASCII'),
                                 language_context, warning_message);

       --Check for the only possible warning message.
       IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
            dbms_output.put_line(
               'Warning! Some characters couldn''t be converted.');
       END IF;

       --Close both LOBs
       DBMS_LOB.CLOSE(directions);
       DBMS_LOB.CLOSE(Tannery_Falls_Directions);
    END;

The real work in this snippet of code is done by the call to DBMS_LOB.LOADCLOBFROMFILE. That procedure reads data from the external file, performs any character set translation that's necessary, and writes the data to the CLOB column. We use the new DBMS_LOB.LOBMAXSIZE constant to specify the amount of data to load. We really want all the data from the external file, and DBMS_LOB.LOBMAXSIZE is as much as a CLOB will hold.

The destination and source offsets both begin at 1. We want to begin reading with the first character in the BFILE, and we want to begin writing to the first character of the CLOB. To facilitate multiple, sequential calls to LOADCLOBFROMFILE, the procedure will update both these offsets to point one character past the most recently read character. Because they are IN OUT parameters, we must use variables and not constants in our procedure call.

The call to NLS_CHARSET_ID returns the character set ID number for the character set used by the external file. The LOADCLOBFROMFILE procedure will then convert the data being loaded from that character set to the database character set. The only possible warning message LOADCLOBFROMFILE can return is that some characters were not convertible from the source to the target character set. We check for this warning in the IF statement following the load.

A warning is not the same as a PL/SQL error; the load will still have occurred, just as we requested.

The following SQL*Plus example shows the data loaded from our external file using LOADCLOBFROMFILE:

    SQL>SET LONG 2000
    SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70
    SQL> SELECT falls_directions
         FROM waterfalls
         WHERE falls_name='Tannery Falls';

    FALLS_DIRECTIONS
    ----------------------------------------------------------------------
    From downtown Munising, take Munising Avenue east. It will
    shortly turn into H-58. Watch for Washington Street veering
    off to your left. At that intersection you'll see a wooden
    stairway going into the woods on your right. Go up that
    stairway and follow the trail to the falls. Do not park
    on H-58! You'll get a ticket. You can park on Nestor Street,
    which is just uphill from the stairway.

13.5.7. Temporary LOBs

So far, we've been talking about permanently storing large amounts of unstructured data by means of the various LOB datatypes. Such LOBs are known as persistent LOBs. Many applications have a need for temporary LOBs that act like local variables but do not exist permanently in the database. This section discusses temporary LOBs and the use of the DBMS_LOB built-in package to manipulate them.

Oracle8i Database and subsequent releases of Oracle's database software support the creation, freeing, access, and update of temporary LOBs through the Oracle Call Interface (OCI) and DBMS_LOB calls. The default lifetime of a temporary LOB is the lifetime of the session that created it, but such LOBs may be explicitly freed sooner by the application. Temporary LOBs are ideal as transient workspaces for data manipulation, and because no logging is done, and no redo records are generated, they offer better performance than persistent LOBs do. In addition, whenever you rewrite or update a LOB, Oracle copies the entire LOB to a new segment. By avoiding all the associated redo and logging, applications that perform lots of piecewise operations on LOBs should see significant performance improvements with temporary LOBs.

A temporary LOB is empty when it is created: you don't need to (and, in fact, you can't) use the EMPTY_CLOB and EMPTY_BLOB functions to initialize LOB locators for a temporary LOB. By default, all temporary LOBs are deleted at the end of the session in which they were created. If a process dies unexpectedly or if the database crashes, then temporary LOBs are deleted, and the space for temporary LOBs is freed.

Temporary LOBs are just like persistent LOBs in that they exist on disk inside your database. Don't let the word "temporary" fool you into thinking that they are memory structures. Temporary LOBs are written to disk, but instead of being associated with a specific LOB column in a specific table, they are written to disk in your session's temporary tablespace. Thus, if you use temporary LOBs, you need to make sure that your temporary tablespace is large enough to accommodate them.

Let's examine the processes for creating and freeing temporary LOBs. Then we'll look at how you can test to see whether a LOB locator points to a temporary or a permanent LOB. We'll finish up by covering some of the administrative details to consider when you're working with temporary LOBs.

13.5.7.1 Creating a temporary LOB

Before you can work with a temporary LOB, you need to create it. One way to do this is with a call to the DBMS_LOB.CREATETEMPORARY procedure. This procedure creates a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace. The header is:

    DBMS_LOB.CREATETEMPORARY (
       lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ],
       cache   IN BOOLEAN,
       dur     IN PLS_INTEGER := DBMS_LOB.SESSION);

The parameters to DBMS_LOB.CREATETEMPORARY are listed in Table 13-1.

Table 13-1. CREATETEMPORARY parameters

Parameter

Description

lob_loc

Receives the locator to the LOB.

cache

Specifies whether the LOB should be read into the buffer cache.

dur

Controls the duration of the LOB. The dur argument can be one of these two named constants:

DBMS_LOB.SESSION

Specifies that the temporary LOB created should be cleaned up (memory freed) at the end of the session. This is the default.

DBMS_LOB.CALL

Specifies that the temporary LOB created should be cleaned up (memory freed) at the end of the current program call in which the LOB was created.

Another way to create a temporary LOB is to declare a LOB variable in your PL/SQL code and assign a value to it. For example, the following code creates both a temporary BLOB and a temporary CLOB:

    DECLARE
       temp_clob CLOB;
       temp_blob BLOB;
    BEGIN
       --Assigning a value to a null CLOB or BLOB variable causes
       --PL/SQL to implicitly create a session-duration temporary
       --LOB for you.
       temp_clob :=' http://www.nps.gov/piro/';
       temp_blob := HEXTORAW('7A');
    END;

We don't really have a strong preference as to which method you should use to create a temporary LOB, but we do believe the use of DBMS_LOB.CREATETEMPORARY makes the intent of your code a bit more explicit.

13.5.7.2 Freeing a temporary LOB

The DBMS_LOB.FREETEMPORARY procedure frees a temporary BLOB or CLOB in your default temporary tablespace. The header for this procedure is:

    PROCEDURE DBMS_LOB.FREETEMPORARY (
       lob_loc IN OUT NOCOPY
          [ BLOB | CLOB CHARACTER SET ANY_CS ]);

In the following example, we again create two temporary LOBs. Then we explicitly free them:

    DECLARE
       temp_clob CLOB;
       temp_blob BLOB;
    BEGIN
       --Assigning a value to a null CLOB or BLOB variable causes
       --PL/SQL to implicitly create a session-duration temporary
       --LOB for you.
       temp_clob :=' http://www.exploringthenorth.com/alger/alger.html';
       temp_blob := HEXTORAW('7A');

       DBMS_LOB.FREETEMPORARY(temp_clob);
       DBMS_LOB.FREETEMPORARY(temp_blob);
    END;

After a call to FREETEMPORARY, the LOB locator that was freed (lob_loc in the previous specification) is marked as invalid. If an invalid LOB locator is assigned to another LOB locator through an assignment operation in PL/SQL, then the target of the assignment is also freed and marked as invalid.

PL/SQL will implicitly free temporary LOBs when they go out of scope at the end of a block.

13.5.7.3 Checking to see whether a LOB is temporary

The ISTEMPORARY function tells you if the LOB locator (lob_loc in the following specification) points to a temporary or a persistent LOB. The function returns an integer value: 1 means that it is a temporary LOB, and 0 means that it is not (it's a persistent LOB instead).

    DBMS_LOB.ISTEMPORARY (
       lob_loc IN [ BLOB | CLOB CHARACTER SET ANY_CS ])
      RETURN INTEGER;

This function is designed to be called from within SQL; that, presumably, is the reason Oracle did not define ISTEMPORARY to be a Boolean function.

13.5.7.4 Managing temporary LOBs

Temporary LOBs are handled quite differently from normal, persistent, internal LOBs. With temporary LOBs, there is no support for transaction management, consistent read operations, rollbacks, and so forth. There are various consequences of this lack of support:

• If you encounter an error when processing with a temporary LOB, you must free that LOB and start your processing over again.

• You should not assign multiple LOB locators to the same temporary LOB. Lack of support for consistent read and undo operations can cause performance degradation with multiple locators.

• If a user modifies a temporary LOB while another locator is pointing to it, a copy (referred to by Oracle as a deep copy) of that LOB is made. The different locators will then no longer see the same data. To minimize these deep copies, use the NOCOPY compiler hint whenever you're passing LOB locators as arguments.

• To make a temporary LOB permanent, you must call the DBMS_LOB.COPY program and copy the temporary LOB into a permanent LOB.

• Temporary LOB locators are unique to a session. You cannot pass a locator from one session to another (through a database pipe, for example) and make the associated temporary LOB visible in that other session.

Oracle9i Database Release 1 introduced a V$ view called V$TEMPORARY_LOBS that shows how many cached and uncached LOBs exist per session. Your DBA can combine information from V$TEMPORARY_LOBS and the DBA_SEGMENTS data dictionary view to see how much space a session is using for temporary LOBs.

13.5.8. Native LOB Operations

Almost since the day Oracle unleashed LOB functionality to the vast hordes of Oracle database users, programmers and query-writers have wanted to treat LOBs as very large versions of regular, scalar variables. In particular, users wanted to treat CLOBs as very large character strings, passing them to SQL functions, using them in SQL statement WHERE clauses, and so forth. To the dismay of many, CLOBs could not be used interchangeably with VARCHAR2s. For example, in Oracle8 Database and Oracle8i Database, you could not apply a character function to a CLOB column:

    SELECT SUBSTR(falls_directions,1,60)
    FROM waterfalls;

Beginning in Oracle9i Database Release 1, however, you can use CLOBs interchangeably with VARCHAR2s in a wide variety of situations:

• You can pass CLOBs to most SQL and PL/SQL VARCHAR2 functions.

• In PL/SQL, but not in SQL, you can use various relational operators such as less-than (<), greater-than (>), and equals (=) with LOB variables.

• You can assign CLOB values to VARCHAR2 variables and vice versa. You can also select CLOB values into VARCHAR2 variables and vice versa. This is because PL/SQL now implicitly converts between the CLOB and VARCHAR2 types.

Oracle refers to these capabilities as offering "SQL semantics" for LOBs. From a PL/SQL developer's standpoint, it means that you can manipulate LOBs using native operators rather than a supplied package.

Following is an example showing some of the new things you can do with SQL semantics:

    DECLARE
       name CLOB;
       name_upper CLOB;
       directions CLOB;
       blank_space VARCHAR2(1) := ' ';
    BEGIN
       --Retrieve a VARCHAR2 into a CLOB, apply a function to a CLOB
       SELECT falls_name, SUBSTR(falls_directions,1,500)
       INTO name, directions
       FROM waterfalls
       WHERE falls_name = 'Munising Falls';

       --Uppercase a CLOB
       name_upper := UPPER(name);

       -- Compare two CLOBs
       IF name = name_upper THEN
          DBMS_OUTPUT.PUT_LINE('We did not need to uppercase the name.');
       END IF;

       --Concatenate a CLOB with some VARCHAR2 strings
       IF INSTR(directions,'Mackinac Bridge') <> 0 THEN
          DBMS_OUTPUT.PUT_LINE('To get to ' || name_upper || blank_space
                               || 'you must cross the Mackinac Bridge.');
       END IF;
    END;

This example will output the following message:

    To get to MUNISING FALLS you must cross the Mackinac Bridge.

The small piece of code in this example does several interesting things:

• The falls_name column is a VARCHAR2 column, yet it is retrieved into a CLOB variable. This is a demonstration of implicit conversion between the VARCHAR2 and CLOB types.

• The SUBSTR function is used to limit retrieval to only the first 500 characters of the directions to Munising Falls. Further, the UPPER function is used to uppercase the falls name. This demonstrates the application of SQL and PL/SQL functions to LOBs.

• The IF statement that compares name to name_upper is a bit forced, but it demonstrates that relational operators may now be applied to LOBs.

• The uppercased falls name, a CLOB, is concatenated with some string constants and one VARCHAR2 string (blank_space). This shows that CLOBs may now be concatenated.

There are many restrictions and caveats that you need to be aware of when using this functionality. For example, not every function that takes a VARCHAR2 input will accept a CLOB in its place; there are some exceptions. Likewise, not all relational operators are supported for use with LOBs. All of these restrictions and caveats are described in detail in the section called "SQL Semantics and LOBs" in Chapter 9 of the Application Developer's GuideLarge Objects manual for Oracle Database 10g Release 2. If you're using SQL semantics, we strongly suggest that you take a look at that section of the manual.

SQL semantics for LOBs apply only to internal LOBs: CLOBs, BLOBs, and NCLOBs. SQL semantics support does not apply to BFILEs.

13.5.8.1 SQL semantics may yield temporary LOBs

One issue you will need to understand when applying SQL semantics to LOBs is that the result is often the creation of a temporary LOB. Think about applying the UPPER function to a CLOB:

    DECLARE
       directions CLOB;
    BEGIN
       SELECT UPPER(falls_directions)
       INTO directions
       FROM waterfalls
       WHERE falls_name = 'Munising Falls';
    END;

Because they are potentially very large objects, CLOBs are stored on disk. Oracle can't uppercase the CLOB being retrieved because that would mean changing its value on disk, in effect changing a value that we simply want to retrieve. Nor can Oracle make the change to an in-memory copy of the CLOB because the value may not fit in memory and also because what is being retrieved is only a locator that points to a value that must be on disk. The only option is for the database software to create a temporary CLOB in your temporary tablespace. The UPPER function then copies data from the original CLOB to the temporary CLOB, uppercasing the characters during the copy operation. The SELECT statement then returns a LOB locator pointing to the temporary CLOB, not to the original CLOB. There are two extremely important ramifications to all this:

• You cannot use the locator returned by a function or expression to update the original LOB. The directions variable in our example cannot be used to update the persistent LOB stored in the database because it really points to a temporary LOB returned by the UPPER function.

• Disk space and CPU resources are expended to create a temporary LOB, which can be of considerable size. We'll discuss this issue more in the next section, "Performance impact of using SQL semantics."

If we want to retrieve an uppercase version of the directions to Munising Falls while still maintaining the ability to update the directions, we'll need to retrieve two LOB locators:

    DECLARE
       directions_upper CLOB;
       directions_persistent CLOB;
    BEGIN
       SELECT UPPER(falls_directions), falls_directions
       INTO directions_upper, directions_persistent
       FROM waterfalls
       WHERE falls_name = 'Munising Falls';
    END;

Now we can access the uppercase version of the directions via the locator in directions_upper, and we can modify the original directions via the locator in directions_persistent. There's no performance penalty in this case from retrieving the extra locator. The performance hit comes from uppercasing the directions and placing them into a temporary CLOB. The locator in directions_persistent is simply plucked as-is from the database table.

In general, any character-string function to which you normally pass a VARCHAR2, and that normally returns a VARCHAR2 value, will return a temporary CLOB when you pass in a CLOB as input. Similarly, expressions that return CLOBs will most certainly return temporary CLOBs. Temporary CLOBs and BLOBs cannot be used to update the LOBs that you originally used in an expression or function.

13.5.8.2 Performance impact of using SQL semantics

You'll need to give some thought to performance when you are using the new SQL semantics for LOB functionality. Remember that the "L" in LOB stands for "large," and that "large" can be as much as 128 terabytes (four gigabytes prior to Oracle Database 10g Release 1). Consequently, you may encounter some serious performance issues if you indiscriminately treat LOBs the same as any other type of variable or column. Have a look at the following query, which attempts to identify all waterfalls for which a visit might require a trip across the Mackinac Bridge:

    SELECT falls_name
    FROM waterfalls
    WHERE INSTR(UPPER(falls_directions),'MACKINAC BRIDGE') <> 0;

Think about what Oracle must do to resolve this query. For every row in the waterfalls table, it must take the falls_directions column, uppercase it, and place those results into a temporary CLOB (residing in your temporary tablespace). Then it must apply the INSTR function to that temporary LOB to search for the string 'MACKINAC BRIDGE'. In our examples, the directions have been fairly short. Imagine, however, that falls_directions were truly a large LOB, and that the average column size were one gigabyte. Think of the drain on your temporary tablespace as Oracle allocates the necessary room for the temporary LOBs created when uppercasing the directions. Then think of all the time required to make a copy of each CLOB in order to uppercase it, the time required to allocate and deallocate space for temporary CLOBs in your temporary tablespace, and the time required for the INSTR function to search character-by-character through an average of 1 GB per CLOB. Such a query would surely bring the wrath of your DBA down upon you.

Oracle Text and SQL Semantics

If you need to execute queries that look at uppercase versions of CLOB values, and you need to do so efficiently, Oracle Text may hold the solution. For example, you might reasonably expect to write a query such as the following some day:

    SELECT falls_name
    FROM waterfalls
    WHERE INSTR(UPPER(falls_directions),
                'MACKINAC BRIDGE') <> 0;

If falls_directions is a CLOB column, this query may not be all that efficient. However, if you are using Oracle Text, you can define a case-insensitive Oracle Text index on that CLOB column, and then use the CONTAINS predicate to efficiently evaluate the query:

    SELECT falls_name
    FROM waterfalls
    WHERE
        CONTAINS(falls_directions,'mackinac bridge') > 0;

For more information on CONTAINS and case-insensitive indexes using Oracle Text, see Oracle's Text Application Developer's Guide.

Because of all the performance ramifications of applying SQL semantics to LOBs, Oracle's documentation suggests that you limit such applications to LOBs that are 100 KB or less in size. We ourselves don't have a specific size recommendation to pass on to you; you should consider each case in terms of your particular circumstances and how badly you need to accomplish a given task. We encourage you, however, to always give thought to the performance implications of using SQL semantics for LOBs, and possibly to run some tests to experience these implications, so that you can make a reasonable decision based on your circumstances.

13.5.9. LOB Conversion Functions

Oracle provides several conversion functions that are sometimes useful when working with large object data, described in Table 13-2.

Table 13-2. LOB conversion functions

Function

Description

TO_CLOB (character_data)

Converts character data into a CLOB. The input to TO_CLOB can be any of the following character types: VARCHAR2, NVARCHAR2, CHAR, NCHAR, CLOB, and NCLOB. If necessary (for example, if the input is NVARCHAR2), input data is converted from the national character set into the database character set.

TO_BLOB(raw_data)

Similar to TO_CLOB, but converts RAW or LONG RAW data into a BLOB.

TO_NCLOB (character_data)

Does the same as TO_CLOB, except that the result is an NCLOB using the national character set.

TO_LOB (long_data)

Accepts either LONG or LONG RAW data as input, and converts that data to a CLOB or a BLOB, respectively. TO_LOB may be invoked only from the select list of a subquery in an INSERT ... SELECT ... FROM statement.

TO_RAW

Takes a BLOB as input and returns the BLOB's data as a RAW value.

The TO_LOB function is designed specifically to enable one-time conversion of LONG and LONG RAW columns into CLOB and BLOB columns, because LONG and LONG RAW are now considered obsolete. The TO_CLOB and TO_NCLOB functions provide a convenient mechanism for converting character large object data between the database and national language character sets.