Saturday, 22 August 2015

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 error analysis

1. The cause of the problem
Recently when performing some operations oracle, and will always encounter this error: ORA-06502: PL / SQL: numeric or value error: character string buffer too small error as follows:

ORA-00604: Recursive SQL Level 1 errors
ORA-06502: PL / SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7

2. The official explanation
See the document on the official explanation:
ORA-06502: PL / SQL: numeric or value errorstring

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger. than 99 to a variable declared NUMBER (2).
Action: Change the data, how it is manipulated, or how it is declared so that values ​​do not violate constraints.

The official explanation is: to stored data in the database (including data, strings, etc.) does not meet the definition of the field (such as length, constraints, etc.), for example, that you want to store a null value to a non-empty fields, As deposited three more digits to NUMBER (2) field, etc.
The official solution is: change the type of data, or length, and so on.

3. Description of the problem
However, in practice, we do upgrade, or do EXPDP / IMPDP time, is not our own business data, also reported this error, in which case, how do I know where the data change it,

A few examples:

Example 1. The last time was doing oracle upgrade, rose to 11.2.0.1.6 from 11.2.0.1.0 time in the final run upgrade script catcpu.sql when you encounter this error:

SQL> / ***************************** PACKAGE BODY **************** ʱ??
SQL> / *** DBMS_EXPFIL: Package to manage a Expression Engine *** /
SQL> / *** All procedures are defined with definer rights *** /
SQL> / *********************************************** ʱ??
SQL> create or replace package body dbms_expfil wrapped
 a000000
abcd
abcd
f5t1PSNPJhKkNUFecK3LJ0wJChaaSsloMeCeeC3pud2dm7 + rVQUjJz6UkBKiymIRTD47p8N
 + dIIc0OU7IlN6zUWPsEgm9hnXXIeGgSQFJU // nCdeBSWO7VQUXXQwvWTkRaQX + VcQUdld5As
 cx9z + 2uhSovZ8svraprK7VAh2cy8bqQBlS + 1P4mkrsCSbvlsRSSqN + XgZbZqgUDSzZFfRbc0
.
.
/

create or replace package body dbms_expfil wrapped
Line 1 error:
ORA-00604: Recursive SQL Level 1 errors
ORA-06502: PL / SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7

In this case, oracle itself is obviously something went wrong, we can not change anything.

Example 2: In doing expdp / impdp when importing and exporting, reported this error:

expdp SCOTT directory=RIM dumpfile=expdpSCOTT.dmp logfile=expdpSCOTT.log exclude=TABLE:\"IN \(\'ABC\',\'DEF\',\'GHI\',\'JKL\',\'MNO\'\)\" 

Export: Release 11.2.0.1.0 - Production on Friday July 27 2012 17:43:11
Copyright (c) 1982, 2009, Oracle and / or its affiliates. All rights reserved.
  
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_02 for user SIMANG_D
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20150822103744" and "KUPC$S_1_20150822103744" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

impdp SCOTT directory=RIM dumpfile=expdpSCOTT.dmp logfile=impdpSCOTT.log include=TABLE:\"IN \(\'ABC\',\'DEF\',\'GHI\',\'JKL\',\'MNO\'\)\" 

Import: Release 11.2.0.1.0 - Production on Friday July 27 2012 18:09:11
  
Copyright (c) 1982, 2009, Oracle and / or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_02 for user SIMANG_D
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20150822103744" and "KUPC$S_1_20150822103744" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

In this case, it looks like oracle in the creation of this task, because ORA-06502 errors can not be created.

4. Problem Solving
In both cases, and user data nothing but an ORA-06502 error, indicating ORACLE itself has a system table when you insert data does not meet the length requirement, but reported this mistake.
Gathering information: According to investigation materials, oracle has an implicit parameter "_system_trig_enabled" is used to control the system triggers for some of the trigger events storage system, but this table (currently do not know which table) is itself a problem, resulting in data can not be inserted.

Solution: Along the idea, as long as we put this parameter off, let the system log is not saved in question goes on the table, like a (of course there are the risks implied, is currently unknown):

Solution:

col name for a30;
col value for a10;
col deflt for a10;
col type for a20;
col description for a50;
select a.ksppinm name,b.ksppstvl value,
b.ksppstdf deflt,decode(a.ksppity, 1,'boolean', 2,'string', 3,'number', 4,'file', a.ksppity) type,a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx and
a.ksppinm like '\_%' escape '\' and
a.ksppinm  like '%_system_trig_enabled%'
order by name;


SQL> alter system set "_system_trig_enabled"=FALSE;

After complete the activity Enabled the trigger.

After the change, do the above operation, the problem is solved!

5. Summary
    Depending on the problem there are two kinds of solutions
       A. If the user data, change the user table, or data
       B. if the oracle system tables, use the following statement system triggers off system:


alter system set "_system_trig_enabled" = false;

No comments: