Use of CLOB in PL/SQL

I have an unknown length of characters to store. Hence, I opted to use CLOB in my PL/SQL.

My statements are quite simple:
msg CLOB;
...... msg := msg || 'more text';
end loop;

I got a ORA-6502 numeric value error - host bind array too small. I checked the size of the variable at the time was only about 200 bytes.

1) what am I doing wrong?
2) Is there a solution without involving a CAST statement?
3) does CLOB take up unused space when the actual size of the content is small?

any leads or help is appreciated. Thanks in advance.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

New2OracleConnect With a Mentor Author Commented:
Other codes in my mind is indifferent but here it goes anyway. could the chr(10) has any effect on the CLOB variable?, e.g. implicitly converted the field into another data type?

Thanks in advance for the help.

msg clob;
cnt number;
cnt := 0;
for i in (select * from table) loop
  cnt := cnt + 1;
   msg := msg ||' this is record '||cnt||chr(10);
end loop;
sdstuberConnect With a Mentor Commented:
can you post more of your code?
or a minimal test case that reproduces the error?
New2OracleAuthor Commented:
SORRY FOLKS! a foolish mistake on my part.

the error message was raised when I tried to dbms_output.put_line on a CLOB variable. By reiterating the code to sdstuber, I found my own foolish mistake.

thanks to all who looked at this problem.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

New2OracleAuthor Commented:
Thanks to all.
glad I could help, even if indirectly
I will note though,  once I substituted a real object instead of just "table"  in your loop your code ran fine for me.  

dbms_output.put_line was able to be printed a clob just fine until the clob  exceeds 32K (the varchar2 limit)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.