SQL & PL/SQL

Quick question about CLOB to Varchar2 implicit conversion in PL/SQL.

I have a function (get_clob) that returns a clob. In this case the CLOB is : 13730 characters long. Used dbms_lob.getlength to determine this.

if I run this code it fails:

-------------------------
Test 1

declare

l_test varchar2(32000);
begin

l_test := get_clob;

end;

ORA-06502: PL/SQL: numeric or value error

-- Test 2 works --

declare

l_test varchar2(32000);
begin

select get_clob
into l_test
from dual;
end;

--- Test 3 Doesn't work ---

declare

l_test varchar2(32000);
begin

l_test := substr(get_clob,1,8192);

end;



ORA-06502: PL/SQL: numeric or value error

-- TEST 4 works --


declare

l_test varchar2(32000);
begin

l_test := substr(get_clob,1,8191);

end;


8191 vs 8192 seems to be the magic # here. Once I have a CLOB of 8192 characters, it bombs on me.

I have looked and the character set is AL32UTF8. I assumed this was my issue as I believe that an be multi-byte. However, if this was the case, why would Test #2 still work?

Any ideas why implicit converstion from CLOB to Varchar2 doesn't work for length over 8191?

Thanks for the help.