| ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #197621] |
Thu, 12 October 2006 01:36  |
keshavpradeep
Messages: 3
Registered: October 2006
|
Junior Member |
|
|
Hi,
I have loaded an xml file into a CLOB type column in Oracle 10g DB.
The size of the file is ard 3 MB. Approximately is has 82,500 lines of xml code.
The loading has gone through perfectly, without any problem. I am able to see the complete data in the CLOB column.
Now when i try to query the data from that CLOB column with the below sql command,
select xmltype.extract(xmltype(val),'/BMECAT/T_NEW_CATALOG/ARTICLE/SUPPLIER_AID/text()').getStringVal() supid from store_xml where id = 11
i get the following error message
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
store_xml table has 2 columns
id number
val Clob
length(val) = 3181687 [ length of the data in te Clob column ].
any help in resolving this error is greatly appreciated
|
|
|
|
|
|
| Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #251297 is a reply to message #197621] |
Thu, 12 July 2007 16:46  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hi,
It's probably because you're using the getStringVal() member function. If the result of your XPath expression returns more than a valid Oracle string (i.e. 32767 bytes) then you need to handle it as a CLOB, by using the getClobVal() member function, i.e.
SQL> SELECT XMLELEMENT("x", LPAD('x', 32767, 'x')).getStringVal() FROM dual;
SELECT XMLELEMENT("x", LPAD('x', 32767, 'x')).getStringVal() FROM dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.XMLTYPE", line 163
SELECT XMLELEMENT("x", LPAD('x', 32767, 'x')).getClobVal() FROM dual
Regards
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
