Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article.
ORA-06502 Cause
The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things:
- A value is being assigned to a numeric variable, but the value is larger than what the variable can handle.
- A non-numeric value is being assigned to a numeric variable.
- A value of NULL is being assigned to a variable which has a NOT NULL constraint.
Let’s take a look at the solutions for each of these causes.
The solution for this error will depend on the cause.
Let’s see an example of each of the three causes mentioned above.
Solution 1: Value Larger than Variable (Number Precision Too Large)
In this example, we have some code that is setting a numeric variable to a value which is larger than what can be stored.
Let’s create this procedure which declares and then sets a variable:
CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
testNumber NUMBER(3);
BEGIN
testNumber := 4321;
END;If we compile it, it compiles with no errors.
Procedure TESTLARGENUMBER compiled
Now, let’s run the procedure.
EXEC TestLargeNumber;We get an error:
Error starting at line : 8 in command - EXEC TestLargeNumber Error report - ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "SYSTEM.TESTLARGENUMBER", line 5 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *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 error we’ve gotten is “ORA-06502: PL/SQL: numeric or value error: number precision too large”. It also includes an ORA-06512, but that error just mentions the next line the code is run from, as explained in this article on ORA-06512.
This is because our variable testNumber can only hold 3 digits, because it was declared as a NUMBER(3). But, the value we’re setting it to a few lines later is 4 digit long (4321).
So, the value is too large for the variable.
To resolve it, increase the size of your variable, or manipulate your value to fit the size of the variable (if possible).
In our example , we can change the size of the variable.
CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
testNumber NUMBER(4);
BEGIN
testNumber := 4321;
END;Procedure TESTLARGENUMBER compiled
Now, let’s run the procedure.
EXEC TestLargeNumber;PL/SQL procedure successfully completed.
The procedure runs successfully. We don’t get any output (because we didn’t code any in), but there are no errors.
Read more on the Oracle data types here.
Solution 2: Non-Numeric Value
Another way to find and resolve this error is by ensuring you’re not setting a numeric variable to a non-numeric value.
For example, take a look at this function.
CREATE OR REPLACE PROCEDURE TestNonNumeric
AS
testNumber NUMBER(4);
BEGIN
testNumber := 'Yes';
END;Procedure TESTNONNUMERIC compiled
The procedure compiles successfully. Now, let’s fun the function.
EXEC TestNonNumeric;Error starting at line : 8 in command - EXEC TestNonNumeric Error report - ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "SYSTEM.TESTNONNUMERIC", line 5 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *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 error we get is “ORA-06502: PL/SQL: numeric or value error: character to number conversion error”.
This happens because our variable testNumber is set to a NUMBER, but a few lines later, we’re setting it to a string value which cannot be converted to a number
To resolve this error:
- Ensure the value coming in is a number and not a string.
- Convert your string to a number using TO_NUMBER (the conversion might happen implicitly but this may help).
- Convert your string to the ASCII code that represents the string using the ASCII function.
- Change the data type of your variable (but check that your code is getting the right value first).
The solution you use will depend on your requirements.
Solution 3: NOT NULL Variable
This error can appear if you try to set a NULL value to a NOT NULL variable.
Let’s take a look at this code here:
CREATE OR REPLACE PROCEDURE TestNonNull
AS
testNumber NUMBER(4) NOT NULL := 10;
nullValue NUMBER(4) := NULL;
BEGIN
testNumber := nullValue;
END;Procedure TESTNONNULL compiled
Now, the reason we’re using a variable to store NULL and not just setting testNumber to NULL is because we get a different error in that case. Besides, it’s probably more likely that your NULL value will come from another system or a database table, rather than a hard-coded NULL value.
Let’s run this function now.
Error starting at line : 9 in command - EXEC TestNonNull Error report - ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYSTEM.TESTNONNULL", line 6 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *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.
We get the ORA-06502 error.
This error message doesn’t give us much more information. But, we can look at the code on line 6, as indicated by the message. We can see we have a variable that has a NOT NULL constraint, and the variable is NULL.
To be sure, we can output some text in our demo when it is null.
CREATE OR REPLACE PROCEDURE TestNonNull
AS
testNumber NUMBER(4) NOT NULL := 10;
nullValue NUMBER(4) := NULL;
BEGIN
IF (nullValue IS NULL) THEN
dbms_output.put_line('Value is null!');
ELSE
testNumber := nullValue;
END IF;
END;Now let’s call the procedure.
EXEC TestNonNull;Value is null!
The output shows the text message, indicating the value is null.
ORA-06502 character string buffer too small
This version of the error can occur if you set a character variable to a value larger than what it can hold.
When you declare character variables (CHAR, VARCHAR2, for example), you need to specify the maximum size of the value. If a value is assigned to this variable which is larger than that size, then this error will occur.
For example:
DECLARE
charValue VARCHAR2(5);
BEGIN
charValue := 'ABCDEF';
END;If I compile this code, I get an error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4
This happens because the variable is 5 characters long, and I’m setting it to a value which is 6 characters long.
You could also get this error when using CHAR data types.
DECLARE
charValue CHAR(5);
BEGIN
charValue := 'A';
charValue := charValue || 'B';
END;ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 5
This error happens because the CHAR data type uses the maximum number of characters. It has stored the value of A and added 4 space characters, up until its maximum value of 5.
When you try to concatenate a value of B to it, the resulting value is ‘A B’, which is 6 characters.
To resolve this, use a VARCHAR2 variable instead of a CHAR, and ensure the maximum size is enough for you.
ORA-06502: pl/sql: numeric or value error: null index table key value
Sometimes you might get this error message with the ORA-06502 error:
ORA-06502: pl/sql: numeric or value error: null index table key value
This means that either:
- Your index variable is not getting initialized, or
- Your index variable is getting set to NULL somewhere in the code.
Check your code to see that neither of these two situations are happening.
ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
You might also get this specific error message:
ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
This is caused by an attempt to SELECT, UPDATE, or INSERT data into a table using a PL/SQL type where a column does not have the same scale as the column in the table.
For example, you may have declared a variable in PL/SQL to be VARCHAR2(100), but your table is only a VARCHAR2(50) field. You may get this error then.
You may also get this error because some data types in PL/SQL have different lengths in SQL.
To resolve this, declare your variables as the same type as the SQL table:
type t_yourcol is table of yourtable.yourcol%TYPE;So, that’s how you resolve the ORA-06502 error.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Learn the cause and how to resolve the ORA-06502 error message in Oracle.
Description
When you encounter an ORA-06502 error, the following error message will appear:
- ORA-06502: PL/SQL: numeric or value error
Cause
You tried to execute a statement that resulted in an arithmetic, numeric, string, conversion, or constraint error.
The common reasons for this error are:
- You tried to assign a value to a numeric variable, but the value is larger than the variable can handle.
- You tried to assign a non-numeric value to a numeric variable and caused a conversion error.
Resolution
Let’s look at three options on how to resolve the ORA-06502 error:
Option #1 — Value too large
In our first option, this error occurs when you try to assign a value to a numeric variable, but the value is larger than the variable can handle.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by redefining the v_number variable as number(3).
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(3); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #2 — Conversion error
In our second option, this error occurs if you are trying to assign a non-numeric value to a numeric variable.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 'a'; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
In this example, the value of ‘a’ does not properly convert to a numeric value. You can correct this error by assigning the variable called v_number a proper numeric value.
SQL> CREATE OR REPLACE PROCEDURE TestProc
2 AS
3 v_number number(2);
4 BEGIN
5 v_number := ASCII('a');
6 END;
7 /
Procedure created.
And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #3 — Assigning NULL to a NOT NULL constrained variable
In our third option, this error occurs if you are trying to assign a NULL value to a NOT NULL constrained variable.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_non_nullable_variable VARCHAR2(30) NOT NULL := '5'; 4 v_null_variable VARCHAR2(30) := NULL; 5 BEGIN 6 v_non_nullable_variable := v_null_variable; 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line(SQLERRM); 10 END; 11 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
ORA-06502: PL/SQL: numeric or value error
In this example, you can not assign a NULL value to the variable called v_non_nullable_variable. You can correct this error removing NOT NULL from the variable declaration of the v_non_nullable_variable as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_non_nullable_variable VARCHAR2(30) := '5'; 4 v_null_variable VARCHAR2(30) := NULL; 5 BEGIN 6 v_non_nullable_variable := v_null_variable; 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line(SQLERRM); 10 END; 11 / Procedure created.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small error occurs when the length of the character string exceeds the length of the declared character type variable,. The value cannot be assigned to the variable if the size of the value passed in the database exceeds the size of the variable declared. The error ORA-06502: PL/SQL: numeric or value error: character string buffer too small would be thrown by the oracle. The error occurs because the output value saved in that variable is longer than it was declared.
The length of the string should not exceed the size of the data type declared in the variable. The string can be stored in the variable in this case. If the length of the character string exceeds the declared variable size, the character string cannot be saved. If the character is attempted to be assigned to the attribute, an exception would be thrown.
Exception
The error will be described as follows. The line number identifies the location of the error. The variable data size is larger than the value size. The following error has been thrown.
declare
empid varchar2(3);
begin
empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"Two ORA errors can be seen in the error stack trace. The first error code is shown alongside the error message. The second error code indicates which line the error happened on. The error indicates that the declared string variable’s size is insufficient in comparison to the value assigned to it.
Problem
The character string cannot be allocated if the length of the string exceeds the size of the declared data type variable. The error can be repeated in this scenario. The database is attempting to assign the variable a string. The error would be thrown since the string is longer than the variable’s length.
In the example below, the value has four characters. The variable is declared to be three characters long. The length of the string value exceeds the length of the declared variable. The error ORA-06502:PL/SQL: numeric or value error: character string buffer too small would be thrown if the value is assigned to a variable that is smaller in size.
declare
empid varchar2(3);
begin
empid := 'A101';
end;Output
declare
empid varchar2(3);
begin
empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"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.
Solution 1
The size of the value passed in Oracle PS./SQL exceeds the declared character data type size. To accommodate the value, the variable data type should be modified. The character data type’s size should be increased. If the size of the character data type is reached to maximum size of the data type, the different data type should be used to accommodate the larger value.
declare
empid varchar2(4);
begin
empid := 'A101';
end;Output
PL/SQL procedure successfully completed.Solution 2
It’s essential to double-check the PL/SQL value. It’s possible that the value was passed to the variable inappropriately or that there was an error in the method. The value will be stored in the variable if it is corrected.
declare
empid varchar2(4);
begin
empid := '101';
end;Output
PL/SQL procedure successfully completed.Solution 3
In most instances, the value assigned would be within the declared data type’s range. The length of the value sometimes reaches the declared data type size. We can’t adjust the data type size in this situation. The exception should be handled and taken action in the PL/SQL code.
declare
empid varchar2(3);
begin
empid := 'A101';
exception
WHEN OTHERS THEN
empid :=0;
end;Output
PL/SQL procedure successfully completed.ORA-06502 means that PL/SQL engine cannot convert a character-typed string into a number or a subset of arithmetic for overall evaluation. Mostly, it’s because of the following problems:
- Numeric Type Conversion
- Numeric Operator Precedence
A. Numeric Type Conversion
ORA-06502 tells you that PL/SQL engine cannot convert a string into a number. Which means, an arithmetic, numeric, string, conversion, or constraint error occurred. Let’s see a normal case first.
SQL> set serveroutput on;
SQL> declare
2 v_num number;
3 begin
4 v_num := 123;
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
The number is 123
PL/SQL procedure successfully completed.
A number 123 is assigned to variable V_NUM which accept only NUMBER type. So there’s no conversion needed. But what if we assign a string to the variable?
SQL> declare
2 v_num number;
3 begin
4 v_num := '123';
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
The number is 123
PL/SQL procedure successfully completed.
As you can see, PL/SQL engine converted the string into a number, then assigned it into the variable.
Now, let’s try some basic arithmetic expressions.
SQL> declare
2 v_num number;
3 begin
4 v_num := 2 + 2;
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
The number is 4
PL/SQL procedure successfully completed.
OK, the variable accepts value, the result of evaluation, no ORA-06502. What if we use it as a string?
SQL> declare
2 v_num number;
3 begin
4 v_num := '2 + 2';
5 dbms_output.put_line('The number is ' || v_num);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4
PL/SQL engine tried to convert the string into a number, but it failed with ORA-06502. This time, V_NUM cannot accept the result.
The solution to this type of error is to avoid implicit type conversion if possible.
B. Numeric Operator Precedence
To better understand ORA-06502, let’s see a more advanced topic about operator precedence in Oracle database. In the following example, we tried to output a string that concatenate an arithmetic.
SQL> begin
2 dbms_output.put_line('The number is ' || 2 + 2);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
ORA-06502 was thrown eventually. Since || (concatenation) and + (addition) operators are at the same level of operator precedence, PL/SQL engine will evaluate them in the order of presence.
First, it concatenated «The number is » and «2» into «The number is 2», which was successful, but when it tried to add the last value «2», it failed to convert the former string into a number and threw ORA-06502.
Solutions
1. Rearrange the Output
We should make PL/SQL engine deal with the numeric evaluation first, then the concatenation by rearranging the output.
SQL> begin
2 dbms_output.put_line(2 + 2 || ' is the number.');
3 end;
4 /
4 is the number.
PL/SQL procedure successfully completed.
This time, the expression is good because the order of presence of operators has been changed.
2. Override Operator Precedence
Beside rearranging the order of presence, how can we make the latter take the precedence over the former to fix the problem? Here is the trick for our PL/SQL block of codes.
SQL> begin
2 dbms_output.put_line('The number is ' || (2 + 2));
3 end;
4 /
The number is 4
PL/SQL procedure successfully completed.
As you can see, we used a parenthesis to override operator precedence. The evaluation will start from the highest precedence which is 2 + 2 numeric value inside the parentheses to the rest according to their operator precedence defined in Oracle. This is how we escape from ORA-06502.
In PL/SQL, if multiple parentheses are used in your expression, the evaluation will start from the inner to the outer.
A very similar error that you might see in your statements is ORA-01722: invalid number, which is also related to conversion issues of numeric values.
A common error to occur while using Oracle is the ORA-06502 error message. This is an error that results from a mistake in the arithmetic or the numeric value executed in a statement.
For example, this error message could result from attempting to execute a statement by assigning an integer a value greater than 99 when the variable is set to NUMBER(2). There are two primary methods that will be discussed to instruct on how to resolve the ORA-06502 error. The first will look at conversion errors, and the second will be directed towards inputting a value that is too large for the variable.
In a conversion error, the problem arises from attempting to assign a non-numeric value to a numeric variable. For instance, say you successfully completed a procedure but when going to execute it the ORA-06502 error message springs up. At this juncture, look back through the procedure and make sure that all numeric variables have exclusively numeric values. When using a non-numeric value, assign the error a proper numeric variable value via ASCII (American Standard Code for Information Interchange). This should resolve the ORA-06502.
The second way this error message will occur is if a value being assigned to a numeric variable exceeds the numerical size that the variable can function under. When going to create a procedure, say you assign the variable to not exceed 99, which could be input as ‘v_number number (2)’. If you then proceed to input a numeric value of 100 or higher (three digits and up), the allotted value of the variable will have been eclipsed and result in an error message. By either changing the numeric value to fall in the proper digit range, or by editing the variable to a different digit range to accommodate the value, the ORA-06502 will be resolved.
This error message is generally a quick fix, but there are some ways you can expedite the process (or avoid the error altogether). Make careful note of the error message in this case, as it will provide indication of where the error is emanating from and the cause (e.g., ‘number precision too large at line 5’). This alone should point you in the right direction. Furthermore, by staying meticulous when creating your Oracle procedures and keeping track of the simple concepts like variable size and numeric vs. non-numeric values, you can save yourself a lot of time and frustration. Taking these basic steps should aid in making your Oracle experience a stress-free and prosperous endeavor!
Businesses around the world, from local tech companies to multinational telecommunication giants, require a data management system that can be customized to suit their specific needs. Oracle has proven to be an industry leader in doing just that, providing one of the most comprehensive software suites available in the data management market. Consulting an Oracle partner firm is a beneficial route to implementing Oracle software across your business.
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
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;