In this chapter, we’ll explore all the native PL/SQL datatypes that we haven’t yet covered. These include the BOOLEAN , RAW, and UROWID/ROWID types, as well as the LOB (large object) family of types. We’ll also discuss some useful, predefined object types, including XMLType, which enables you to store XML data in a database column, and the Any types, which allow you to store, well, just about anything.
Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can only be TRUE, FALSE, or NULL, you can use that variable to explain what is happening in your code. With Booleans you can write code that is easily readable because it is more English-like. You can replace a complicated Boolean expression involving many different variables and tests with a single Boolean variable that directly expresses the intention and meaning of the text.
Here is an example of an IF statement with a single Boolean variable (or function—you really can’t tell the difference just by looking at this short bit of code):
IF report_requested
THEN
print_report (report_id);
END IF;The beauty of this technique is that it not only makes your code a bit more self-documenting, it also has the potential to insulate your code from future change. For example, consider the human interface that needs to precede the previous code fragment. How do we know that a report was requested? Perhaps we ask the user to answer a question with a Y or an N, or perhaps the user must place a check in a checkbox or select an option from a drop-down list. The point is that it doesn’t matter. We can freely change the human interface of our code, and, as long as that interface properly sets the report_requested Boolean variable, the actual reporting functionality will continue to work correctly.
Tip
While PL/SQL supports a Boolean datatype, the RDBMS does not. You can create and work with Boolean variables from PL/SQL, but you cannot create tables having Boolean columns.
The fact that Boolean variables can be NULL has implications for IF...THEN...ELSE statements. For example, look at the difference in behavior between the following two statements:
IF report_requested
THEN
--Executes if report_requested = TRUE
ELSE
--Executes if report_requested = FALSE or IS NULL
END IF;
IF NOT report_requested
THEN
--Executes if report_requested = FALSE
ELSE
--Executes if report_requeste = TRUE or IS NULL
END IF;If you need separate logic for each of the three possible cases, you can write a three-pronged IF statement as follows:
IF report_requested
THEN
--Executes if report_requested = TRUE
ELSIF NOT report_requested
--Executes if report_requested = FALSE
ELSE
--Executes if report_requested IS NULL
END IF;For more details on the effects of NULLs in IF statements, refer back to Chapter 4.
The RAW datatype allows you to store and manipulate relatively small amounts of binary data. Unlike the case with VARCHAR2 and other character types, RAW data never undergoes any kind of character set conversion when traveling back and forth between your PL/SQL programs and the database. RAW variables are declared as follows:
variable_nameRAW(maximum_size)
The value for maximum_size may range from 1 through 32767. Be aware that while a RAW PL/SQL variable can hold up to 32,767 bytes of data, a RAW database column can hold only 2000 bytes.
RAW is not a type that we use or encounter very often. It’s useful mainly when you need to deal with small amounts of binary data. When dealing with the large amounts of binary data found in images, sound files, and the like, you should look into using the BLOB (binary large object) type. BLOB is described later in this chapter (see “The BLOB Datatype”).
The UROWID and ROWID types allow you to work with database rowids in your PL/SQL programs. A rowid is a row identifier—a binary value that identifies a row of data in an Oracle table. Referencing rowids in UPDATE and DELETE statements can sometimes lead to desirable improvements in processing speed, as access by rowid is typically the fastest way to locate or retrieve a particular row in the database—faster even than a search by primary key. Figure 13-1 contrasts the use of a rowid in an UPDATE statement with the use of column values such as those for a primary key.
In the history of Oracle, the ROWID type came first. As Oracle added functionality such as index-organized tables (IOTs) and gateways to other types of databases, Oracle developed new types of rowids and hence had to develop a new datatype capable of holding them. Enter the UROWID datatype . The U in UROWID stands for Universal, and a UROWID variable can contain any type of rowid from any type of table.
Tip
We recommend the use of UROWID for all new development involving rowids. The ROWID type provides backward compatibility but can’t accommodate all types of rowids now encountered in an Oracle database. UROWID is safer because it accommodates any type of rowid.
You can get the rowid for a given database row by including the keyword ROWID in your select list. For example:
DECLARE
employee_rowid UROWID;
employee_salary NUMBER;
BEGIN
--Retrieve employee information that we might want to modify
SELECT rowid, salary INTO employee_rowid, employee_salary
FROM employee
WHERE last_name='Grubbs' AND first_name='John';
END;Oracle terms ROWID a pseudo-column. It’s called that because the column does not really exist: its values are not really stored in the same sense that other values are, yet you can refer to ROWID as if it were a column. When you reference ROWID, the RDBMS knows to return rowids.
One potentially very useful application of rowids is in repeating access to a given database row. Recall the example from the previous section in which we retrieved the salary for a specific employee. What if we later want to modify that salary? One solution would be to issue an UPDATE statement with the same WHERE clause as the one we used in our original SELECT:
DECLARE
employee_rowid UROWID;
employee_salary NUMBER;
BEGIN
--Retrieve employee information that we might want to modify
SELECT rowid, salary INTO employee_rowid, employee_salary
FROM employee
WHERE last_name='Grubbs' AND first_name='John';
/* Do a bunch of processing to compute a new salary */
UPDATE employee
SET salary = employee_salary
WHERE last_name='Grubbs' AND first_name='John';
END;While this code will certainly work, it has the disadvantage of having to repeat the same access path for the UPDATE as was used for the SELECT. Most likely, one or more indexes will be consulted in order to determine the rowid for the employee row in question. But those indexes were just consulted for the SELECT statement, so why go through all the trouble of looking up the same rowid twice? Why indeed! Because we retrieved the rowid in our SELECT statement, we can simply supply that rowid to the UPDATE, thus bypassing the need to do any kind of index lookup:
DECLARE
employee_rowid UROWID;
employee_salary NUMBER;
BEGIN
--Retrieve employee information that we might want to modify
SELECT rowid, salary INTO employee_rowid, employee_salary
FROM employee
WHERE last_name='Grubbs' AND first_name='John';
/* Do a bunch of processing to compute a new salary */
UPDATE employee
SET salary = employee_salary
WHERE rowid = employee_rowid;
END;If the employee table is regular and heap-organized, the rowid in the UPDATE statement’s WHERE clause points directly to the location of the row on disk. If the employee table is index-organized, that may or may not be the case depending on how volatile the table is; nevertheless, the rowid still represents the fastest way of accessing a given row.
Tip
An often better way to achieve the same effect as using ROWID in an UPDATE or DELETE statement is to use an explicit cursor to retrieve data, and then use the WHERE CURRENT OF CURSOR clause to modify or delete it. See Chapter 15 for detailed information on this technique.
Do rowids ever change? That’s a good question. If you’re going to retrieve a rowid and use it later on, you need to know whether rowids ever expire. Physical rowids, used for standard, heap-organized tables, never change. Only if you delete a row and reinsert it—as you might when doing an export followed by an import—will you get a new rowid for a row. But we just said that physical rowids never change! Are we contradicting ourselves? By no means. When you delete a row and reinsert it, you are really inserting a new row with the same values, and the new row will get a new rowid.
Logical rowids, used in index-organized tables, are not as long-lived as physical rowids. Additionally, logical rowids can change when you change a row’s primary key. We’re not in the habit of writing applications that change primary keys, so in actual practice this doesn’t seem like much of a limitation. It is, however, something to keep in mind when you are writing applications that make use of rowids, especially if any of your application tables are index-organized.
Everything is a tradeoff. The use of rowids can make your applications more efficient, but the tradeoff is that you now have a few more things to think and worry about.
You can use the rowid in an Oracle Forms application to access the row in the database corresponding to the record on the screen. When you create a base-table block in Oracle Forms, it automatically includes the rowid in the block as an “invisible pseudo-item.” You do not see it on your item list, but you can reference it in your triggers and PL/SQL program units. For example, to update the name of an employee displayed on the screen, you could issue the following statement:
UPDATE employee
SET last_name = :employee.last_name
WHERE rowid = :employee.rowid;You can also use the rowid inside a cursor FOR loop (or any other loop that FETCHes records from a cursor) to make changes to the row just FETCHed, as follows:
PROCEDURE remove_internal_competitors IS
BEGIN
FOR emp_rec IN
(SELECT connections, rowid
FROM employee
WHERE sal > 500000)
LOOP
IF emp_rec.connections IN ('President', 'CEO')
THEN
send_holiday_greetings;
ELSE
DELETE FROM employee
WHERE rowid = emp_rec.rowid;
END IF;
END LOOP;
END;The DELETE uses the rowid stored in the emp_rec record to immediately get rid of anyone making more than $500,000 who does not have known connections to the President or CEO. Note that the DBA controls who may have EXECUTE privilege to this stored procedure. So one must now wonder: does the DBA have connections to the President or CEO? Well, in any case, use of the rowid guarantees the fastest possible DELETE of the unwanted employees.
We aren’t convinced that the theoretical performance gains of searching by rowid justify its use. The resulting code is harder to understand than the application-specific use of the primary key. You also must understand how rowids might change. We’ve heard of people storing rowids in tables, only to have everything break when the DBA does an export/import to reorganize storage. It’s best, in our opinion, not to store rowids in database columns.
A further issue with respect to rowids concerns portability. The rowid is not a part of the SQL standard; instead, it reflects directly the internal storage structure of the Oracle RDBMS. References to rowids could cause portability problems in the future because non-Oracle databases do not recognize or support rowids. If you are building applications that may need to work against non-Oracle data sources, you should avoid any references to the rowid pseudo-column and to the UROWID and ROWID datatypes.
Oracle and PL/SQL support several variations of LOB (large object) datatypes. LOBs can store large amounts—from 8 to 128 terabytes—of binary data (such as images) or character text data.
Tip
Through Oracle9i Database Release 2, LOBs could store only up to 4 gigabytes. In Oracle Database 10g Release 1, the limit was increased to a value between 8 and 128 terabytes that is dependent upon your database block size.
Within PL/SQL you can declare LOB variables of the following datatypes:
- BFILE
Binary file. Declares a variable that holds a file locator pointing to an operating-system file outside the database. Oracle treats the data in the file as binary data.
- BLOB
Binary large object. Declares a variable that holds a LOB locator pointing to a large binary object stored inside the database.
- CLOB
Character large object. Declares a variable that holds a LOB locator pointing to a large block of single-byte, fixed-width character data stored inside the database.
- NCLOB
National Language Support (NLS) character large object. Declares a variable that holds a LOB locator pointing to a large block of single-byte, fixed-width multibyte, or variable-width multibyte character data stored inside the database.
LOBs can be categorized as internal or external. Internal LOBs (BLOBs, CLOBs, and NCLOBs) are stored in the database and can participate in a transaction in the database server. External LOBs (BFILEs) represent binary data stored in operating-system files outside the database tablespaces. External LOBs cannot participate in transactions; in other words, you cannot commit or roll back changes to a BFILE. Instead, you must rely on the underlying filesystem for data integrity.
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.
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.
Tip
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.
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.
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:
Issue a SELECT statement to retrieve the LOB locator for the photo you wish to display.
Open the LOB via a call to DBMS_LOB.OPEN.
Make a call to DBMS_LOB.GETCHUNKSIZE to get the optimal chunk size to use when reading (and writing) the LOB’s value.
Make a call to DBMS_LOB.GETLENGTH to get the number of bytes or characters in the LOB value.
Make multiple calls to DBMS_LOB.READ in order to retrieve the LOB data.
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.
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>DECLARE2directions CLOB;3BEGIN4IF directions IS NULL THEN5DBMS_OUTPUT.PUT_LINE('directions is NULL'),6ELSE7DBMS_OUTPUT.PUT_LINE('directions is not NULL'),8END IF;9END;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 = 0EMPTY_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 dataIf 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.
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.
Tip
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.”
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.
Tip
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 2000SQL>COLUMN falls_directions WORD_WRAPPED FORMAT A70SQL>SELECT falls_directions2FROM waterfalls3WHERE 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.
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.
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 files—to which BFILE locators point—completely 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 BookCh12_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.
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.
Warning
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.
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.
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.
Tip
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 2000SQL>COLUMN falls_directions WORD_WRAPPED FORMAT A70SQL>SELECT falls_directionsFROM 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.
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.
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_locIN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ],cacheIN BOOLEAN,durIN 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:
|
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.
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.
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.
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.
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.
Tip
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 Guide—Large 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.
Tip
SQL semantics for LOBs apply only to internal LOBs: CLOBs, BLOBs, and NCLOBs. SQL semantics support does not apply to BFILEs.
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.
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.
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.
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.
Starting with Oracle9i Database Release 1, Oracle provides a collection of useful, predefined object types:
- XMLType
Use this to store and manipulate XML data.
- Various URI types
Use these to store uniform resource identifiers (such as HTML addresses).
- Various Any types
Use these to define a PL/SQL variable that can hold any type of data.
The following subsections discuss these predefined object types in more detail.
Oracle9i Database Release 1 introduced a native object type called XMLType. You can use XMLType to define database columns and PL/SQL variables containing XML documents. Methods defined on XMLType enable you to instantiate new XMLType values, to extract portions of an XML document, and to otherwise manipulate the contents of an XML document in various ways.
Tip
In Oracle9i Database Release 1, you needed to use the “SYS.” prefix when referencing the XMLType object type. Staring with Oracle9i Database Release 2, Oracle allows synonyms to point to object types, and the database creation script ($ORACLE_HOME/rdbms/admin/dbmsxmlt.sql) that creates XMLType now also creates the public synonym XMLTYPE, which points to the SYS.XMLType predefined object type.
Using XMLType, you can easily create a table to hold XML data:
CREATE TABLE falls (
fall_id NUMBER,
fall XMLType
);The fall column in this table is of XMLType and can hold XML data. To store XML data into this column, you must invoke the static CreateXML method, passing it your XML data. CreateXML accepts XML data as input and instantiates a new XMLType object to hold that data. The new object is then returned as the method’s result, and it is that object that you must store in the column. CreateXML is overloaded to accept both VARCHAR2 strings and CLOBs as input.
Use the following INSERT statements to create three XML documents in the falls table:
INSERT INTO falls VALUES (1, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Munising Falls</name>
<county>Alger</county>
<state>MI</state>
<url>
http://michiganwaterfalls.com/munising_falls/munising_falls.html
</url>
</fall>'));
INSERT INTO falls VALUES (2, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Au Train Falls</name>
<county>Alger</county>
<state>MI</state>
<url>
http://michiganwaterfalls.com/autrain_falls/autrain_falls.html
</url>
</fall>'));
INSERT INTO falls VALUES (3, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Laughing Whitefish Falls</name>
<county>Alger</county>
<state>MI</state>
</fall>'));You can query XML data in the table using various XMLType methods. The existsNode method used in the following example allows you to test for the existence of a specific XML node in an XML document. The built-in SQL EXISTSNODE function, also in the example, performs the same test. Whether you use the method or the built-in function, you identify the node of interest using an XPath expression.[*]
Both of the following statements produce the same output:
SQL> SELECT fall_id
2 FROM falls f
3 WHERE f.fall.existsNode('/fall/url') > 0;
SQL> SELECT fall_id
2 FROM falls
3 WHERE EXISTSNODE(fall,'/fall/url') > 0;
FALL_ID
----------
1
2You can, of course, also work with XML data from within PL/SQL. In the following example, we retrieve the fall column for Munising Falls into a PL/SQL variable that is also of XMLType. Thus, we retrieve the entire XML document into our PL/SQL program, where we can work further with it. After retrieving the document, we extract and print the text from the /fall/url node.
<<demo_block>>
DECLARE
fall XMLType;
url VARCHAR2(100);
BEGIN
--Retrieve XML for Munising FallsSELECT fall INTO demo_block.fall
FROM falls f
WHERE f.fall_id = 1;
--Extract and display the URL for Munising Falls
url := fall.extract('/fall/url/text()').getStringVal;
DBMS_OUTPUT.PUT_LINE(url);
END;We’d like to call your attention to the following two lines:
SELECT fall INTO demo_block.fallOur variable name, fall, matches the name of the column in the database table. In our SQL query, therefore, we qualify our variable name with the name of our PL/SQL block.
url := fall.extract('/fall/url/text()').getStringVal;To get the text of the URL, we invoke two of XMLType’s methods:
extractReturns an XML document, of XMLType, containing only the specified fragment of the original XML document. Use XPath notation to specify the fragment you want returned.
getStringValReturns the text of an XML document.
In our example, we apply the getStringVal method to the XML document returned by the extract method, thus retrieving the text for the Munising Fall’s URL. The extract method returns the contents of the <url> node as a XMLType object, and getStringVal then returns that content as a text string that we can display.
You can even index XMLType columns to allow for efficient retrieval of XML documents based on their content. You do this by creating a function-based index, for which you need the QUERY REWRITE privilege. The following example creates a function-based index on the first 80 characters of each falls name:
CREATE INDEX falls_by_name
ON falls f (
SUBSTR(
XMLType.getStringVal(
XMLType.extract(f.fall,'/fall/name/text()')
),1,80
)
);We had to use the SUBSTR function in the creation of this index. The getStringVal method returns a string that is too long to index, resulting in an ORA-01450: maximum key length (3166) exceeded error. Thus, when creating an index like this, you need to use SUBSTR to restrict the results to some reasonable length.
If you decide to use XMLType in any of your applications, be sure to consult Oracle’s documentation for more complete and current information. The XML DB Developer’s Guide for Oracle Database 10g Release 2 is an important, if not critical, reference for developers working with XML. The SQL Reference also has some useful information on XMLType and on the built-in SQL functions that support XML.
The URI types introduced in Oracle9i Database Release 1 consist of a supertype and a collection of subtypes that provide support for storing URIs in PL/SQL variables and in database columns. UriType is the supertype, and a UriType variable can hold any instance of one of the subtypes:
- HttpUriType
A subtype of UriType that is specific to HTTP URLs, which usually point to web pages.
- DBUriType
A subtype of UriType that supports URLs that are XPath expressions.
- XDBUriType
A subtype of UriType that supports URLs that reference Oracle XML DB objects. XML DB is Oracle’s name for a set of XML technologies built into the database.
To facilitate your work with URIs, Oracle also provides a UriFactory package that automatically generates the appropriate URI type for whatever URI you pass to it.
The URI types are created by the script named dbmsuri.sql $ORACLE_HOME/rdbms/admin. All the types and subtypes are owned by the user SYS. In Oracle9i Database Release 1, you must use the “SYS.” prefix to reference the URI types. From Oracle9i Database Release 2 onwards, you do not need to use the “SYS.” prefix.
The following code example demonstrates the use of HttpUriType:
DECLARE
WebPageURL HttpUriType;
WebPage CLOB;
BEGIN
--Create an instance of the type pointing
--to a message from Jonathan Gennick
WebPageURL := HttpUriType.createUri(
'http://gennick.com/message.plsql'),
--Retrieve the message via HTTP
WebPage := WebPageURL.getclob();
--Display the message
DBMS_OUTPUT.PUT_LINE((SUBSTR(WebPage,1,60)));
END;The output from this code example will be:
Brighten the corner where you are.
For more information on the use of the UriType family, see Chapter 9, Accessing Data Through URIs, of the XML DB Developer’s Guide for Oracle Database 10g Release 2.
Beginning with Oracle9i Database Release 1, a family of types known as the Any types enables you to write programs to manipulate data when you don’t know the type of that data until runtime. Member functions support introspection, allowing you to determine the type of a value at runtime and to access that value.[*]
The following predefined types belong to this family:
- AnyData
Can hold a single value of any type, whether it’s a built-in scalar datatype, a user-defined object type, a nested table, a large object, a varying array (VARRAY), or any other type not listed here.
- AnyDataSet
Can hold a set of values of any type, as long as all values are of the same type.
- AnyType
Can hold a description of a type. Think of this as an AnyData without the data.
The Any types are created by a script named dbmsany.sql found in $ORACLE_HOME/rdbms/admin, and are owned by the user SYS. As with the URI types, in Oracle9i Database Release 1 you must use a “SYS.” prefix to reference the Any types, but this is no longer necessary beginning in Oracle 9i Database Release 2).
In addition to creating the Any types, the dbmsany.sql script also creates a package named DBMS_TYPES that defines the constants in the following list. You can use these constants in conjunction with introspection functions such as GETTYPE in order to determine the type of data held by a given AnyData or AnyDataSet variable. The specific numeric values assigned to the constants are not important; rely on the constants, not on their underlying values.
TYPECODE_DATE
TYPECODE_NUMBER
TYPECODE_RAW
TYPECODE_CHAR
TYPECODE_VARCHAR2
TYPECODE_VARCHAR
TYPECODE_MLSLABEL
TYPECODE_BLOB
TYPECODE_BFILE
TYPECODE_CLOB
TYPECODE_CFILE
TYPECODE_TIMESTAMP
TYPECODE_TIMESTAMP_TZ
TYPECODE_TIMESTAMP_LTZ
TYPECODE_INTERVAL_YM
TYPECODE_INTERVAL_DS
TYPECODE_REF
TYPECODE_OBJECT
TYPECODE_VARRAY
TYPECODE_TABLE
TYPECODE_NAMEDCOLLECTION
TYPECODE_OPAQUEThe following example creates two user-defined types representing two kinds of geographic features. The subsequent PL/SQL block then uses SYS.AnyType to define a heterogeneous array of features (i.e., each array element can be of a different datatype).
First, you’ll need to create the following two types:
CREATE OR REPLACE TYPE waterfall AS OBJECT (
name VARCHAR2(30),
height NUMBER
);
CREATE OR REPLACE TYPE river AS OBJECT (
name VARCHAR2(30),
length NUMBER
);Next, execute the following PL/SQL code block:
DECLARE
TYPE feature_array IS VARRAY(2) OF SYS.AnyData;
features feature_array;
wf waterfall;
rv river;
ret_val NUMBER;
BEGIN
--Create an array where each element is of
--a different object type
features := feature_array(
AnyData.ConvertObject(
waterfall('Grand Sable Falls',30)),
AnyData.ConvertObject(
river('Manistique River', 85.40))
);
--Display the feature data
FOR x IN 1..features.COUNT LOOP
--Execute code pertaining to whatever object type
--we are currently looking at. NOTE! Replace GENNICK
--with whatever schema you are using.
CASE features(x).GetTypeName
WHEN 'GENNICK.WATERFALL' THEN
ret_val := features(x).GetObject(wf);
DBMS_OUTPUT.PUT_LINE('Waterfall: '
|| wf.name || ', Height = ' || wf.height || ' feet.'),
WHEN 'GENNICK.RIVER' THEN
ret_val := features(x).GetObject(rv);
DBMS_OUTPUT.PUT_LINE('River: '
|| rv.name || ', Length = ' || rv.length || ' miles.'),
END CASE;
END LOOP;
END;Finally, your output should appear as follows:
Waterfall: Grand Sable Falls, Height = 30 feet.
River: Manistique River, Length = 85.4 miles.Let’s look at this code one piece at a time. The features are stored in a VARRAY, which is initialized as follows:
features := feature_array(
AnyData.ConvertObject(
waterfall('Grand Sable Falls',30)),
AnyData.ConvertObject(
river('Manistique River, 85.40))
);Working from the inside out and focusing on Grand Sable Falls, you can interpret this code as follows:
waterfall('Grand Sable Falls',30)Invokes the constructor for the waterfall type to create an object of that type.
AnyData.ConvertObject(Converts the waterfall object into an instance of SYS.AnyData, allowing it to be stored in our array of SYS.AnyData objects.
feature_array(Invokes the constructor for the array. Each argument to feature_array is of type AnyData. The array is built from the two arguments we pass.
VARRAYs were discussed in Chapter 12, and you can read about object types in more detail in Chapter 25.
The next significant part of the code is the FOR loop in which each object in the features array is examined. A call to:
features(x).GetTypeName
returns the fully qualified type name of the current features object. For user-defined objects, the type name is prefixed with the schema name of the user who created the object. We had to include this schema name in our WHEN clauses; for example:
WHEN 'GENNICK.WATERFALL' THEN
If you’re running this example on your own system, be sure to replace the schema we used (GENNICK) with the one that is valid for you.
Tip
For built-in types such as NUMBER, DATE, and VARCHAR2, GetTypeName will return just the type name. Schema names apply only to user-defined types (i.e., those created using CREATE TYPE).
Once we determined which datatype we were dealing with, we retrieved the specific object using the following call:
ret_val := features(x).GetObject(wf);
In our example, we ignored the return code. There are two possible return code values:
- DBMS_TYPES.SUCCESS
The value (or object, in our case) was successfully returned.
- DBMS_TYPES.NO_DATA
No data was ever stored in the AnyData variable in question, so no data can be returned.
Once we had the object in a variable, it was an easy enough task to write a DBMS_OUTPUT statement specific to that object type. For example, to print information about waterfalls, we used:
DBMS_OUTPUT.PUT_LINE('Waterfall: '
|| wf.name || ', Height = ' || wf.height || ' feet.'),For more information on the “Any” family of types:
Visit Chapter 25, which examines the Any datatypes from an object-oriented perspective.
Check out Oracle’s PL/SQL Packages and Types Reference and the SQL Reference.
Try out the anynums.pkg and anynums.tst scripts on the book’s web site.
Tip
From an object-oriented design standpoint, there are better ways to deal with multiple feature types than the method we used in this section’s example. In the real world, however, not everything is ideal, and our example does serve the purpose of demonstrating the utility of the SYS.AnyData predefined object type.
[*] XPath is a syntax that describes parts of an XML document. Among other things, you can use XPath to specify a particular node or attribute value in an XML document.
[*] An introspection function is one that you can use in a program to examine and learn about variables declared by your program. In essence, your program learns about itself—hence the term introspection.