Question related to CLOB data type in Oracle 11g
690659Dec 21 2010 — edited Dec 23 2010Hi,
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.