|Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 2 (11.2)
Part Number E18294-01
This chapter contains these topics:
In PL/SQL, semantic changes have been made.
Note:The following discussions, concerning
VARCHAR2s, also apply to
RAWs, unless otherwise noted. In the text,
RAWare not explicitly mentioned.
PL/SQL semantics support is described in the following sections:
Implicit conversions from
VARCHAR2 and from
CLOB data types are allowed in PL/SQL. These conversions enable you to perform the following operations in your application:
CLOB columns can be selected into
VARCHAR2 PL/SQL variables
VARCHAR2 columns can be selected into
Assignment and parameter passing between
The following example illustrates the way
CLOB data is accessed when the
CLOBs are treated as
declare myStoryBuf VARCHAR2(4001); BEGIN SELECT ad_sourcetext INTO myStoryBuf FROM print_media WHERE ad_id = 12001; -- Display Story by printing myStoryBuf directly END; /
declare myLOB CLOB; BEGIN SELECT 'ABCDE' INTO myLOB FROM print_media WHERE ad_id = 11001; -- myLOB is a temporary LOB. -- Use myLOB as a lob locator DBMS_OUTPUT.PUT_LINE('Is temp? '||DBMS_LOB.ISTEMPORARY(myLOB)); END; /
In SQL and PL/SQL, the following explicit conversion functions convert other data types to and from
BLOB as part of the LONG-to-LOB migration:
TO_CLOB(): Converting from
NCLOB to a
TO_NCLOB(): Converting from
CLOB to an
TO_BLOB(): Converting from
RAW to a
TO_CHAR() converts a
CLOB to a
CHAR type. When you use this function to convert a character LOB into the database character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.
TO_NCHAR() converts an
NCLOB to an NCHAR type. When you use this function to convert a character LOB into the national character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.
CAST does not directly support any of the LOB data types. When you use
CAST to convert a
CLOB value into a character data type, an
NCLOB value into a national character data type, or a
BLOB value into a
RAW data type, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target data type. If the resulting value is larger than the target type, then the database returns an error.
Other explicit conversion functions are not supported, such as,
TO_NUMBER(), see Table 16-1, "SQL VARCHAR2 Functions and Operators on LOBs". Conversion function details are explained in Chapter 18, "Migrating Columns from LONGs to LOBs".
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".
VARCHAR2 functions and operators can take
CLOBs as arguments 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 is raised and no operation is performed. This is consistent with
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 raises 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
CLOB locator. In the current interface the line can be expanded as:
buffer VARCHAR2(32000) DBMS_LOB.CREATETEMPORARY(revisedStory); 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.
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 SELECT 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 10 11 myStory:= UPPER(SUBSTR(myStory, 100, 1)); 12 -- perform VARCHAR2 operations on a CLOB variable, temporary LOB created. 13 -- Changes are not reflected in the database table from this point on. 14 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
DBMS_LOB.WRITE call in line 8 directly writes the data to the table.
UPDATE statement is necessary. Subsequently in line 11, a temporary LOB is created and assigned to
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 are no longer 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 or function or procedure. You must also free the temporary LOBs that were created with
DBMS_LOB.CREATETEMPORARY to reclaim system resources and temporary tablespace. Do this by calling
DBMS_LOB.FREETEMPORARY on the
Note:If the SQL statement returns a LOB or a LOB is an
OUTparameter for a PL/SQL function or procedure, you must test if it is a temporary LOB, and if it is, then free it after you are done with it.
declare Story1 CLOB; Story2 CLOB; StoryCombined CLOB; StoryLower CLOB; BEGIN 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 DBMS_LOB.FREETEMPORARY(StoryCombined); StoryLower := LOWER(Story1) || LOWER(Story2); END; -- At the end of block, StoryLower is freed.
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,
VARCHAR2s have slightly different sequences due to the blank padding of
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
CLOBis compared with a
CHARstring, it is always the character data of the
CLOBbeing 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.
Built-in and user-defined PL/SQL functions that are executed on the remote site and operate on remote LOBs and
BFILEs are allowed, as long as the final value returned by nested functions is not a LOB. Examples are:
SELECT product_id FROM print_media@dbs2 WHERE foo@dbs2(ad_sourcetext, 'aa') > 0; -- foo is a user-define function returning a NUMBER DELETE FROM print_media@dbs2 WHERE DBMS_LOB.GETLENGTH@dbs2(ad_graphic) = 0;
The restrictions that apply to SQL functions apply here also.
A function in one
dblink cannot operate on LOB data in another dblink. For example, the following statement is not supported:
SELECT a.product_id FROM print_media@dbs1 a, print_media@dbs2 b WHERE CONTAINS@dbs1(b.ad_sourcetext, 'aa') > 0;
One query block cannot contain tables and functions at different
dblinks. For example, the following statement is not supported:
SELECT a.product_id FROM print_media@dbs2 a, print_media@dbs3 b WHERE CONTAINS@dbs2(a.ad_sourcetext, 'aa') > 0 AND foo@dbs3(b.ad_sourcetext) > 0; -- foo is a user-defined function in dbs3
There is no support for performing remote LOB operations (that is,
DBMS_LOB) from within PL/SQL, other than issuing SQL statements from PL/SQL.