Skip Headers

Oracle® Database Application Developer's Guide - Large Objects
10g Release 1 (10.1)

Part Number B10796-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

PL/SQL Semantics for LOBs

This chapter covers the following topics:

PL/SQL Statements and Variables

In PL/SQL, a number of semantic changes have been made as described in the previous paragraphs.


The following discussions, concerning CLOBs and VARCHAR2s, also apply to BLOBs and RAWs, unless otherwise noted. In the text, BLOB and RAW are not explicitly mentioned.

PL/SQL semantics support is described in the following sections:

Implicit Conversions Between CLOB and VARCHAR2

Implicit conversions from CLOB to VARCHAR2 and from VARCHAR2 to CLOB datatypes are allowed in PL/SQL. These conversions enable you to perform the following operations in your application:

Accessing a CLOB as a VARCHAR2 in PL/SQL

The following example illustrates the way CLOB data is accessed when the CLOBs are treated as VARCHAR2s:

   myStoryBuf VARCHAR2(4001);
   -- Display Story by printing myStoryBuf directly

Assigning a CLOB to a VARCHAR2 in PL/SQL

-- myLOB is a temporary LOB.
-- Use myLOB as a lob locator

Explicit Conversion Functions

In SQL and PL/SQL, the following explicit conversion functions convert other data types to CLOB, NCLOB, and BLOB as part of the LONG-to-LOB migration:

Other explicit conversion functions are not supported, such as, TO_NUMBER(), see Table 9-1. Conversion function details are explained in Chapter 11, "Migrating Table Columns from LONGs to LOBs".

VARCHAR2 and CLOB in PL/SQL Built-In Functions

CLOB and VARCHAR2 are still two distinct types. But depending on the usage, a CLOB can be passed to SQL and PL/SQL VARCHAR2 built-in functions, used exactly like a VARCHAR2. Or the variable can be passed into DBMS_LOB APIs, acting like a LOB locator. Please see the following combined example,"CLOB Variables in PL/SQL".

PL/SQL VARCHAR2 functions/operators need to take CLOBs as argument or operands.

When the size of a VARCHAR2 variable is not large enough to contain the result from a function that returns a CLOB, or a SELECT on a CLOB column, an error should be raised and no operation will be performed. This is consistent with VARCHAR2 semantics.

CLOB Variables in PL/SQL

1 declare
2   myStory CLOB;
3   revisedStory CLOB;
4   myGist VARCHAR2(100);
5   revisedGist VARCHAR2(100);
6 begin
7  -- select a CLOB column into a CLOB variable
8  SELECT Story INTO myStory FROM print_media WHERE product_id=10;
9  -- perform VARCHAR2 operations on a CLOB variable
10 revisedStory := UPPER(SUBSTR(myStory, 100, 1)); 
11 -- revisedStory is a temporary LOB
12 -- Concat a VARCHAR2 at the end of a CLOB
13 revisedStory := revisedStory || myGist;

14 -- The following statement will raise an error because myStory is 
15 -- longer than 100 bytes
16 myGist := myStory;
17 end;

Please note that in line 10 of "CLOB Variables in PL/SQL", a temporary CLOB is implicitly created and is pointed to by the revisedStory CLOB locator. In the current interface the line can be expanded as:

buffer VARCHAR2(32000)
buffer := UPPER(DBMS_LOB.SUBSTR(myStory,100,1));
DBMS_LOB.WRITE(revisedStory,length(buffer),1, buffer);

In line 13, myGist is appended to the end of the temporary LOB, which has the same effect of:

DBMS_LOB.WRITEAPPEND(revisedStory, myGist, length(myGist));

In some occasions, implicitly created temporary LOBs in PL/SQL statements can change the representation of LOB locators previously defined. Consider the next example.

Change in Locator-Data Linkage

1 declare
2 myStory CLOB;
3 amt number:=100;
4 buffer VARCHAR2(100):='some data';
5 begin
6 -- select a CLOB column into a CLOB variable
7 S               ELECT Story INTO myStory FROM print_media WHERE product_id=10;
8 DBMS_LOB.WRITE(myStory, amt, 1, buf);
9 -- write to the persistent LOB in the table
11 myStory:= UPPER(SUBSTR(myStory, 100, 1));
12 -- perform VARCHAR2 operations on a CLOB variable, temporary LOB created. 
13 -- will not be reflected in the database table from this point on.
15 update print_media set Story = myStory WHERE product_id = 10;
16 -- an update is necessary to synchronize the data in the table.
17 end;

After line 7, myStory represents a persistent LOB in print_media.

The DBMS_LOB.WRITE() call in line 8 directly writes the data to the table.

No UPDATE statement is necessary. Subsequently in line 11, a temporary LOB is created and assigned to myStory because myStory is now used like a local VARCHAR2 variable. The LOB locator myStory now points to the newly-created temporary LOB.

Therefore, modifications to myStory will no longer be reflected in the database. To propagate the changes to the database table, an UPDATE statement becomes necessary now. Note again that for the previous persistent LOB, the UPDATE is not required.

Temporary LOBs created in a program block as a result of a SELECT or an assignment are freed automatically at the end of the PL/SQL block/function/procedure. You can choose to free the temporary LOBs to reclaim system resources and temporary tablespace by calling DBMS_LOB.FREETEMPORARY() on the CLOB variable.

Freeing Temporary LOBs Automatically and Manually

   Story1 CLOB;
   Story2 CLOB;
   StoryCombined CLOB;
   StoryLower CLOB;
   SELECT Story INTO Story1 FROM print_media WHERE product_ID = 1;
   SELECT Story INTO Story2 FROM print_media WHERE product_ID = 2;
   StoryCombined := Story1 || Story2; -- StoryCombined is a temporary LOB
   -- Free the StoryCombined manually to free up space taken
   StoryLower := LOWER(Story1) || LOWER(Story2);
end; -- At the end of block, StoryLower is freed.

PL/SQL CLOB Comparison Rules

Like VARCHAR2s, when a CLOB is compared with another CLOB or compared with a VARCHAR2, a set of rules determines the comparison. The rules are usually called a "collating sequence". In Oracle, CHARs and VARCHAR2s have slightly different sequences due to the blank padding of CHARs.

CLOBs Follow the VARCHAR2 Collating Sequence

As a rule, CLOBs follow the same collating sequence as VARCHAR2s. That is, when a CLOB is compared, the result is consistent with if the CLOB data content is retrieved into a VARCHAR2 buffer and the VARCHAR2 is compared. The rule applies to all cases including comparisons between CLOB and CLOB, CLOB and VARCHAR2, and CLOB and CHAR.


When a CLOB is compared with a CHAR string, it is always the character data of the CLOB being compared with the string. Likewise, when two CLOBs are compared, the data content of the two CLOBs are compared, not their LOB locators.

It makes no sense to compare CLOBs with non-character data, or with BLOBs. An error is returned in these cases.