Wednesday, February 27, 2008

PLS-00103: Encountered the symbol "CLOB" when expecting one of the following

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may encounter this compilation error in a situation like this:

create or replace
function fn_c2b (
p_clob in NOCOPY clob,
p_charset in varchar2)
return blob
as
begin
...
end;


PLS-00103: Encountered the symbol "CLOB" when expecting one of the following:
:= . ) , @ % default character
The symbol ":=" was substituted for "CLOB" to continue.

The problem is in the combination of IN with NOCOPY.
NOCOPY requires that the parameter is of type OUT or IN OUT, not IN.

NOCOPY is a useful hint that allows PL/SQL procedure to access parameters by reference, instead of by value (the default).
Accessing parameters like LOBs and PL/SQL tables or varrays by reference significantly reduces memory consumption and CPU time and therefore increases performance.

Read more about the NOCOPY hint optimization (from Oracle 10G documentation).

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles