ora-06502、ora-06512错误

iyoume2008

操作系统：Red hat 6.1
oracle：     11.2.0.3（双节点RAC）


最近运行一段sql代码，报错如下：
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: 在 line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:   
*Action:


sql语句如下：
select replace(c.partnumber,'P','') partnumber,
       dbms_lob.substr(a.thumbnail),
       a.shortdescription,
       dbms_lob.substr(a.longdescription),
       dbms_lob.substr(b.longdescription),
       a.auxdescription1,
       a.keyword
  from
  catentdesc a inner join catentry c on a.catentry_id =c.catentry_id
  left join xprd_catentryinfo b on a.catentry_id = c.catentry_id
order by c.partnumber;

请刘大帮忙分析，谢谢了

枕霜卧雪

How to Determine the Cause of ORA-6502 Errors


The error description is very generic, but we attempt to further define its meaning and some potential causes in this article.  We also show you how to debug and further narrow down the cause of this error.  

This is not the type of error for which you can contact Oracle Support and obtain an immediate solution.  The causes are unique to every customer's application and you are in a better position to debug this than we.  In the majority of cases, you will find that the problem lies within your application code, data source, or table definitions.  

The error message is:

   06502, 00000, "PL/SQL: numeric or value error%s"
   // *Cause:
   // *Action:

The first step when troubleshooting this problem is to identify each offending SQL statement and determine which application or program unit generates this statement.  Oracle provides an event tracing facility that can be used to identify these offending SQL statements.  Modify your init.ora file by setting the following event and restart your database.

   event="6502 trace name errorstack level 12"

When you rerun your application, the ora-6502 will be captured.  By examining this trace file, you are then able to identify the offending SQL statement, and with your knowledge of your application, you can relate this statement to the exact block of code within PL/SQL (or other programmatic interface) used to generate this statement.

Some observations to make at this point include:

o Is the same SQL statement always being executed when this error occurs?

o Do other SQL statements raise this error?

o Is the error intermittent or consistently reproducible?

Once you isolate the offending SQL statement, you are ready to set SQL tracing to capture the bind values.  This can be done at the instance level or at the session level.  



SQL> alter system set timed_statistics = true;
SQL> alter system set sql_trace=true;



If you narrow this down to one isolated program unit, it may be more appropriate to set this at the session level within the PL/SQL block.



create or replace
  begin
    execute_immediate('alter session set timed_statistics = true');
    execute_immediate('alter session set sql_trace = true');
    ...
    -- execute your PLSQL block
    -- execute the target SQL statement
  end;



After the program finishes, review the raw SQL trace file.  The raw trace file shows you the SQL statement plus the value of bind variables being bound to the placeholders in the statement.

Locate your SQL statement.  The following is an extract of a trace file.  Pay attention to the SQL statement, cursor number, bind number, and bind value.  In this example, the cursor number is 19 and is associated with parse number 19 and bind number 19.  You can clearly see the value being bound to the place holder is 'MIKE'.

==================================================
PARSING IN CURSOR #19 ...
SELECT ENAME FROM EMP WHERE ENAME = :1 ...
END OF STATEMENT
PARSE #19
BINDS #19
BIND 0:
   VALUE=MIKE
EXEC
WAIT
WAIT
===================================================

To summarize our progress thus far, we determined the SQL statement in question and the value being bound when the error occurs.

Review your PL/SQL code for invalid datatype assignments.  These could come in the form of an incorrect size or type assignment.  Compare the size of the PL/SQL variable you declared for this placeholder to the size of thevalue you have assigned it.  

If the size of the string is larger than the size you have declared for the variable, then increase the size of the variable to accommodate larger values. If you assigned an incompatible type, then you must recode using a compatible type.

The obvious example of this is assigning a number to a varchar2.




declare
x varchar2(10);
y number;
begin
y := 'mike';        -- valid ora-6502
x := 'abcdefghikl'    -- valid ora-6502        
...
end;



Next, compare your defined column sizes to the defined variable sizes that are part of your query.  If the size of the value being fetched is larger than your defined variable size, then either make your variable size larger or decrease the size of your column.



declare
v_ename varchar2(3); -- too small
begin
select ename into v_ename from emp where ename='MILLER';
end;



If your error cannot be isolated to a SQL statement but seems to occur at package initialization, then review your initialization code to see if you have a mismatch between the declared variable size or type and what is being assigned.

Review all areas of code where functions or procedures are called and parameters are passed.  Ensure that you declared the right amount of space and the appropriate type.

If you use any built-in SQL character or number conversion functions, then be careful about assigning spaces to a number.



DECLARE
my_number NUMBER(10);
BEGIN
my_number := ' ';
END;
/
DECLARE
*
ERROR at line 1:
ORA-6502: PL/SQL: numeric or value error: character to number conversion
error




Another potential cause for the error can be due to size of the values returned from the functions or  the size of the variable passed as OUT parameters.  So we need to carefully analyze such cases and check for proper sizes of the placeholders of returned values from functions.

For example:

Case 1 : Return Value larger than the receiving buffer



create or replace function ret return varchar2
as
begin
return 'asdf';
end;

declare
x varchar2(3);
begin
x:=ret;
end;



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

Case 2 : OUT parameters not large enough



create or replace PROCEDURE foo( bar OUT VARCHAR2 ) IS
BEGIN
    SELECT 'abcd' INTO bar FROM dual;
END;

declare
x varchar2(3);
begin
foo(x);
end;




Other Causes:

If you have a case where the same data set bound to the same SQL statement reproduces an intermittent ora-6502 error, then you may need to contact Oracle Support.  Provide the tracing you have done so far.

枕霜卧雪

这不是我的思路，是MOS上的文章。