OraFAQ Forum: SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625880]

Wed, 15 October 2014 06:15

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi,

My requirement is to pull data from table resdata where 2 columns are clob field and contain html tags.

I tested the same with one id at a time and it worked fine.

SELECT REGEXP_REPLACE (DBMS_LOB.SUBSTR (description, 5000, 1),'<[^>]+& gt;|\&(nbsp;)|(amp;)',' '),
       REGEXP_REPLACE (DBMS_LOB.SUBSTR (requirements, 5000, 1),'<[^>]+ >|\&(nbsp;)|(amp;)',' ')
  FROM resdata
 WHERE jobopening_id = 559185288;

When I am using the same query to pull data from complete table in one go. I am getting the error below:-

SELECT REGEXP_REPLACE (DBMS_LOB.SUBSTR (description, 5000, 1),'<[^>]+& gt;|\&(nbsp;)|(amp;)',' '),
       REGEXP_REPLACE (DBMS_LOB.SUBSTR (requirements, 5000, 1),'<[^>]+ >|\&(nbsp;)|(amp;)',' ')
  FROM resdata;

ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

No rows selected

Please someone help me how to fix this issue to fetch the data from whole table.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625883 is a reply to message #625880]

Wed, 15 October 2014 07:38

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Did you RTFM DBMS_LOB? DBMS_LOB.SUBSTR returns VARCHAR2. And while PL/SQL VARCHAR2 is up to 32767, SQL VARCHAR2 is up to 4000.

SY.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625884 is a reply to message #625883]

Wed, 15 October 2014 07:46

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just use

SELECT REGEXP_REPLACE (SUBSTR (description, 5000, 1),'<[^>]+& gt;|\&(nbsp;)|(amp;)',' '),
       REGEXP_REPLACE (SUBSTR (requirements, 5000, 1),'<[^>]+ >|\&(nbsp;)|(amp;)',' ')
  FROM resdata;

SY.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625886 is a reply to message #625884]

Wed, 15 October 2014 08:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or rather:

SELECT REGEXP_REPLACE (SUBSTR (description, 1, 5000),'<[^>]+& gt;|\&(nbsp;)|(amp;)',' '),
       REGEXP_REPLACE (SUBSTR (requirements, 1, 5000),'<[^>]+ >|\&(nbsp;)|(amp;)',' ')
  FROM resdata;

Parameters offset and amount are swapped in dbms_log.substr.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625887 is a reply to message #625886]

Wed, 15 October 2014 08:22

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Oops, copy & paste errors. Thanks Michel!.

SY.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625930 is a reply to message #625886]

Thu, 16 October 2014 05:37

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi Michel,

I ran the queries given by you, but complete output is not pulled as my Description and requirement columns are clob fields.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625937 is a reply to message #625930]

Thu, 16 October 2014 06:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And?

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625938 is a reply to message #625937]

Thu, 16 October 2014 06:23

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi,

I ran the same in 2 ways :-

1) With the modified query provided by you:- 

 SELECT REGEXP_REPLACE (SUBSTR (description, 1, 5000),'<[^>]+ > |\&(nbsp;)|(amp;)',' '),REGEXP_REPLACE (SUBSTR (requirements, 1, 5000),'<[^>]+>|\&(nbsp;)|(amp;)',' ') 
05:24:14 2 
FROM resdata where jobopening_id=1611569043; 

------ 
<p><font size="2"><span style="font-family: Times New Roman;">Supervisa la funci
Licenciatura o su equivalente en educacion y experiencia. Licencia de conduc 

<p> 
</p> 
<ul> 
<li> 
<span style="font-size:12px;"><span style="font-fami 

2 rows selected. 

This output is not giving complete data from the description and requirement column. HTML tags are also coming. 

2) I ran the query with single jobbid instead of complete table:- 

select REGEXP_REPLACE (dbms_lob.substr(DESCRIPTION,5000,1),'<[^>]+& amp; gt;|\&(nbsp;)|(amp;)', ' ') ,REGEXP_REPLACE (dbms_lob.substr(REQUIREMENTS,5000,1),'<[^>]+ >|\&(nbsp;)|(amp;)', ' ') from resdata where jobopening_id=1611569043; 
 
2 rows selected. 

Its giving me proper results and HTML tags are also omiited. 
When I run the same query with the whole table it throws me an error as:- 

ERROR: 
ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
ORA-06512: at line 1

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625940 is a reply to message #625938]

Thu, 16 October 2014 07:26

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Which part of DBMS_LOB.SUBSTR returns VARCHAR2 which in SQL is limited to 4000 you didn't understand? The only reason query ran for jobopening_id=1611569043 is that whole description is <= 4000 bytes, so subtrsinging 5000 for <= 4000 returns <= 4000. Other jobopening_id's have description > 4000 there for subtrsinging 5000 for > 4000 returns > 4000 which raises an error. Either change DBMS_LOB.SUBSTR to substring 4000 instead of 5000 or change DBMS_LOB.SUBSTR to plain SUBSTR. And in regards to SUBSTR not giving proper results - post test case.

SY.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625942 is a reply to message #625938]

Thu, 16 October 2014 07:54

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

This output is not giving complete data from the description and requirement column.

Set long 10000

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625976 is a reply to message #625942]

Fri, 17 October 2014 08:23

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Thu, 16 October 2014 08:54

Set long 10000

I thought about it, but then both queries DBMS_LOB.SUBSTR and SUBSTR would display partial description while OP stated "output is not giving complete data from the description and requirement column" for SUBSTR and giving complete results for DBMS_LOB.SUBSTR.

SY.

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #625980 is a reply to message #625976]

Fri, 17 October 2014 10:01

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes some of his sentences are inconsistent, this is why I quoted the specific sentence I answered, and its output ending with "<span style="font-fami" lead me to give this hint.

Report message to a moderator