Home » Developer & Programmer » JDeveloper, Java & XML » ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #197621] Thu, 12 October 2006 01:36 Go to next message
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 #246992 is a reply to message #197621] Fri, 22 June 2007 14:43 Go to previous messageGo to next message
lavallee_g
Messages: 22
Registered: May 2005
Location: Quebec
Junior Member
Hi,
To manage your CLOB column, you can use the DBMS_LOB package.. It can be usefull for many things like:

select dbms_lob.getlength(column) from table...

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 Go to previous messageGo to next message
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
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #257178 is a reply to message #251297] Tue, 07 August 2007 12:57 Go to previous message
turfybot
Messages: 3
Registered: August 2007
Junior Member
Hi,

To obtain a description of the code ORA-06502, see http://turfybot.free.fr/oracle/8i/errors/ORA-06502.html
Previous Topic: Problems in running exe files in pl/sql using java
Next Topic: How to Open JSP file as Ms Excel
Goto Forum:
  


Current Time: Thu Mar 28 07:45:27 CDT 2024