Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Question related to CLOB data type in Oracle 11g

690659Dec 21 2010 — edited Dec 23 2010
Hi,
I'm trying to assign a string value to a CLOB variable but it is giving me an error as described below. Can someone please let me know what am I missing here? As far as I know, in 11g a CLOB variable can store upto 4GB of data and all i'm trying to use is only 7K of data.

DECLARE
v_sql1 CLOB;
v_sql2 CLOB;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);

v_sql1 := 'BLAH-BLAH-BLAH.........'; --> this string is actually more than 4000 characters.

v_sql2 := TO_CLOB('BLAH-BLAH-BLAH.........'); --> this string is actually more than 4000 characters.

dbms_output.put_line(v_sql1);
dbms_output.put_line(v_sql2);
END;

When I try to execute the above script, I get the error message: ORA-06502: PL/SQL: numeric or value error:character string buffer too small.
I even tried to use TO_CLOB function as shown above but it is failing as well.

NOTE: The string to assign is not coming from any table but mainly a SQL statement used for sys_refcursor.

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 20 2011
Added on Dec 21 2010
7 comments
5,118 views