New2Oracle
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
dbms_output.put_line was able to be printed a clob just fine until the clob exceeds 32K (the varchar2 limit)
ASKER
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.