[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Use of CLOB in PL/SQL

Posted on 2009-04-07
6
Medium Priority
?
733 Views
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;
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
Comment
Question by:New2Oracle
  • 3
  • 3
6 Comments
 
LVL 74

Assisted Solution

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

Accepted Solution

by:
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.

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
 

Author Comment

by:New2Oracle
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.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:New2Oracle
ID: 24091704
Thanks to all.
0
 
LVL 74

Expert Comment

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

Expert Comment

by:sdstuber
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)

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

872 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