PL/SQL Statements and Variables: New Semantics Changes

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

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

The new PL/SQL semantics support is described in the following sections as follows:

• Implicit Conversions Between CLOB and VARCHAR2
  • PL/SQL Example 1: Prior Release SQL Interface for a CLOB/VARCHAR2 Application

  • PL/SQL Example 2: Accessing CLOB Data When Treated as VARCHAR2s

  • PL/SQL Example 3: Defining a CLOB Variable on a VARCHAR2

• Explicit Conversion Functions

• VARCHAR2 and CLOB in PL/SQL Built-in Functions
  • PL/SQL Example 4: CLOB Variables in PL/SQL

  • PL/SQL Example 5: Change in Locator-Data Linkage

  • PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually

• PL/SQL CLOB Comparison Rules

• Interacting with SQL and PL/SQL in OCI and Java Interfaces

Implicit Conversions Between CLOB and VARCHAR2

The implicit conversion in both directions, from CLOB to VARCHAR2, and from VARCHAR2 to CLOB, have made the following operations between CLOBs and VARCHAR2s possible:

• CLOB columns can be selected into VARCHAR2 PL/SQL variables

• VARCHAR2 columns can be selected into CLOB variables

• Assignment and parameter passing between CLOBs and VARCHAR2s

PL/SQL Example 1: Prior Release SQL Interface for a CLOB/VARCHAR2 Application

The following example illustrates the way CLOB data was accessed prior to this release. This application tries to simply display both the Gist and Story from the table Multimedia_tab.

declare
   myStoryLOB CLOB;
   myStoryBuf VARCHAR2(4001);
   amt NUMBER:=4001;
   offset NUMBER := 1;
begin
   SELECT Story INTO myStoryLOB FROM Multimedia_tab WHERE Clip_ID = 10;
   DBMS_LOB.READ(myStoryLOB, amt, offset, myStoryBuf); 
   -- Display Gist and Story by printing 'myStoryBuf'.
	end;

PL/SQL Example 2: Accessing CLOB Data When Treated as VARCHAR2s

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

declare
   myStoryBuf VARCHAR2(4001);
begin
   SELECT Story INTO myStoryBuf FROM Multimedia_tab WHERE Clip_ID = 10;
   -- Display Story by printing myStoryBuf directly
end;

PL/SQL Example 3: Defining a CLOB Variable on a VARCHAR2

declare

myGistLOB CLOB;

begin

SELECT Gist INTO myGistLOB FROM Multimedia_tab WHERE Clip_ID = 10;
-- myGistLOB is a temporary LOB.
-- Use myGistLOB as a lob locator

end;

Note: In prior releases, in PL/SQL, you had to first issue the DBMS_LOB.CREATETEMPORARY() call before using the temporary LOB. From this release, the temporary LOB is created implicitly in 'assignments' and 'defines.'

Explicit Conversion Functions

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

• TO_CLOB(): Converting from VARCHAR2, NVARCHAR2, or NCLOB to a CLOB

• TO_NCLOB: Converting from VARCHAR2, NVARCHAR2, or CLOB to an NCLOB

• TO_BLOB(): Converting from RAW to a BLOB

• TO_CHAR() is enabled to convert a CLOB to a CHAR type.

• TO_NCHAR() is enabled to convert an NCLOB to a NCHAR type.

Other explicit conversion functions are not supported in this release, such as, TO_NUMBER(), see Table 7-6. Conversion function details are explained in Chapter 8, "Migrating 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, behaving 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,"PL/SQL Example 4: 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 current VARCHAR2 behavior.

PL/SQL Example 4: 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 Multimedia_tab WHERE clip_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 since myStory is 
15 -- longer than 100 bytes
16 myGist := myStory;
17 end;

Please note that in line 10 of "PL/SQL Example 4: 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)
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.

PL/SQL Example 5: 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 Multimedia_tab WHERE clip_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. 
Changes
13 -- will not be reflected in the database table from this point on.
14 
15 update Multimedia_tab set Story = myStory WHERE clip_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 Multimedia_tab.

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 should now behave 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.

PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually

declare
   Story1 CLOB;
   Story2 CLOB;
   StoryCombined CLOB;
   StoryLower CLOB;
begin
   SELECT Story INTO Story1 FROM Multimedia_tab WHERE Clip_ID = 1;
   SELECT Story INTO Story2 FROM Multimedia_tab WHERE Clip_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.