Use of CLOB in PL/SQL

Posted on 2009-04-07
Medium Priority
Last Modified: 2013-12-07
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.

Question by:New2Oracle
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 74

Assisted Solution

sdstuber earned 400 total points
ID: 24090909
can you post more of your code?
or a minimal test case that reproduces the error?

Accepted Solution

New2Oracle earned 0 total points
ID: 24091525
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;

Author Comment

ID: 24091675
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.
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 24091704
Thanks to all.
LVL 74

Expert Comment

ID: 24091718
glad I could help, even if indirectly
LVL 74

Expert Comment

ID: 24091744
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)


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question