• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

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;
BEGIN
loop
...
...... msg := msg || 'more text';
...
end loop;
END;

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.

0
New2Oracle
Asked:
New2Oracle
  • 3
  • 3
2 Solutions
 
sdstuberCommented:
can you post more of your code?
or a minimal test case that reproduces the error?
0
 
New2OracleAuthor 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.

declare
msg clob;
cnt number;
Begin
cnt := 0;
for i in (select * from table) loop
  cnt := cnt + 1;
   msg := msg ||' this is record '||cnt||chr(10);
end loop;
dbms_output.put_line(msg);
end;
0
 
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
New2OracleAuthor Commented:
Thanks to all.
0
 
sdstuberCommented:
glad I could help, even if indirectly
0
 
sdstuberCommented:
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)

0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now