Character to number conversion error ошибка числа или значения

Нельзя присвоить значение NULL переменной объявленной с ограничением NOT NULL.

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).

Здесь:

a varchar2(10) not null := '',

пустая строка интерпретируется как NULL. Тут подробнее, почему.

v_recx(1).a := 'BBB'; 

Эта строка будет скомпилирована, так как компилятор не проверяет присваиваемых значений во время компиляции. При выполнении блока, ещё до присваивания полю значения 'BBB', поля записи будут инициализированы, где и произойдёт попытка присвоить полю a с ограничением NOT NULL значения NULL.

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:

1. A value is being assigned to a numeric variable, but the value is larger than what the variable can handle.
2. A non-numeric value is being assigned to a numeric variable.
3. 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.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet 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:

1. Ensure the value coming in is a number and not a string.
2. Convert your string to a number using TO_NUMBER (the conversion might happen implicitly but this may help).
3. Convert your string to the ASCII code that represents the string using the ASCII function.
4. 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.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

is there any way to specify in a mybatis resultMap that a String should have 20 characters at most? or how to set the precission, like NUMBER(4,2) ?

for example I have a result map that looks like this:

<resultMap id="IMap" type="com.optsol.beans.Item">

    <result property="item_id" column="ITEM_ID" jdbcType="INTEGER" />
    <result property="item_desc" column="ITEM_DESC" jdbcType="VARCHAR" />
    <result property="min_qty" column="MIN_QTY" jdbcType="INTEGER" />
    <result property="max_qty" column="MAX_QTY" jdbcType="INTEGER" />
    <result property="abc" column="ABC" jdbcType="VARCHAR" />
    <result property="item_weight" column="ITEM_WEIGHT" jdbcType="DOUBLE" />
    <result property="size_cl" column="SIZE_CL" jdbcType="INTEGER" />
    <result property="weight_class" column="WEIGHT_CLASS" jdbcType="INTEGER" />
    <result property="exp_period" column="EXP_PERIOD" jdbcType="DOUBLE" />
    <result property="code" column="CODE" jdbcType="VARCHAR" />
    <result property="um" column="UM" jdbcType="VARCHAR" />
    <result property="img" column="IMG" jdbcType="VARCHAR" />
    <result property="conv_factor" column="CONV_FACTOR" jdbcType="DOUBLE" />
    <result property="vol" column="VOL" jdbcType="INTEGER" />
    <result property="info" column="INFO" jdbcType="VARCHAR" />
    <result property="statist_report" column="STATIST_PERIOD"
        jdbcType="INTEGER" />
    <result property="pack_parent" column="PACK_PARENT" jdbcType="INTEGER" />
    <result property="group_parent" column="GROUP_PARENT" jdbcType="INTEGER" />
    <result property="order_full" column="ORDER_FULL" jdbcType="INTEGER" />
    <result property="insDate" column="INSDATE" />
    <result property="updDate" column="UPDDATE" />
    <result property="insUser" column="INSUSER" jdbcType="VARCHAR" />
    <result property="updUser" column="UPDUSER" jdbcType="VARCHAR" />
</resultMap>

can I limit the number of digits of int to be only 1, show N elements after the «.» on a floating point number or limit the String size to a certain number?

the view I’m working on has this column structure:

('ITEM_ID','NUMBER(10)','No',null,1,null,'NO','NO','NO');
('ITEM_DESC','VARCHAR2(80)','No',null,2,null,'NO','NO','NO');
('MIN_QTY','NUMBER(10)','Yes',null,3,null,'NO','NO','NO');
('MAX_QTY','NUMBER(10)','Yes',null,4,null,'NO','NO','NO');
('ABC','VARCHAR2(1)','No',null,5,null,'NO','NO','NO');
('ITEM_WEIGHT','NUMBER(10,3)','Yes',null,6,null,'NO','NO','NO');
('SIZE_CL','NUMBER(2)','Yes',null,7,null,'NO','NO','NO');
('WEIGHT_CLASS','NUMBER(2)','Yes',null,8,null,'NO','NO','NO');
('EXP_PERIOD','NUMBER(10,3)','Yes',null,9,null,'NO','NO','NO');
('CODE','VARCHAR2(20)','Yes',null,10,null,'NO','NO','NO');
('UM','VARCHAR2(15)','No',null,11,null,'NO','NO','NO');
('IMG','VARCHAR2(100)','Yes',null,12,null,'NO','NO','NO');
('CONV_FACTOR','NUMBER(10,4)','Yes',null,13,null,'NO','NO','NO');
('VOL','NUMBER(10)','Yes',null,14,null,'NO','NO','NO');
('INFO','VARCHAR2(100)','Yes',null,15,null,'NO','NO','NO');
('STATIST_PERIOD','NUMBER(5)','Yes',null,16,null,'NO','NO','NO');
('PACK_PARENT','NUMBER(10)','Yes',null,17,null,'NO','NO','NO');
('GROUP_PARENT','NUMBER(10)','Yes',null,18,null,'NO','NO','NO');
('ORDER_FULL','NUMBER(1)','No',null,19,null,'NO','NO','NO');
('INSDATE','DATE','No',null,20,null,'NO','NO','NO');
('UPDDATE','DATE','No',null,21,null,'NO','NO','NO');
('INSUSER','VARCHAR2(4000)','Yes',null,22,null,'NO','NO','NO');
('UPDUSER','VARCHAR2(4000)','Yes',null,23,null,'NO','NO','NO');

Edit 1: Whenever I try to call a stored procedure to add a new item in this view… I’ll get this error:

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

I made a class for testing purpose that tries to call the add method:

 private ItemService iService = new ItemService(Item.class);
 @Test
  public void addProcTest() {
    Map<String, Object> paramMap = new HashMap<String, Object>();      
      paramMap.put("p_item_desc", "theo3");
      paramMap.put("p_min_qty", 1);
      paramMap.put("p_max_qty", 2);
      paramMap.put("p_abc", "A");
      paramMap.put("p_item_weight", 2);
      paramMap.put("p_size_cl", 1);
      paramMap.put("p_weight_class", 1);
      paramMap.put("p_exp_period", 2);
      paramMap.put("p_code", "theo");
      paramMap.put("p_um", "MP");
      paramMap.put("p_conv_factor",2.0);
      paramMap.put("p_vol", 2);
      paramMap.put("p_info", "theo2");
      paramMap.put("p_pack_parent", 105249);
      paramMap.put("p_group_parent", 0);
      paramMap.put("p_order_full", 0);
      paramMap.put("p_statist_period", 0);      
      paramMap.put("p_do_it", 1);
      iService.addItem(paramMap);
  }

call of the stored procedure in the ItemMapper.xml:

<update id="addItem" parameterType="java.util.Map"
    statementType="CALLABLE">

    {call
    SSLS_WMS.PKG_ITEM_ADMIN.PCREATEITEM(
    #{p_item_desc,jdbcType=VARCHAR},
    #{p_min_qty,jdbcType=INTEGER},
    #{p_max_qty,jdbcType=INTEGER},
    #{p_abc,jdbcType=VARCHAR},
    #{p_item_weight,jdbcType=DOUBLE},
    #{p_size_cl,jdbcType=INTEGER},
    #{p_weight_class,jdbcType=INTEGER},
    #{p_exp_period,jdbcType=DOUBLE},
    #{p_code,jdbcType=VARCHAR},
    #{p_um,jdbcType=VARCHAR},
    #{p_conv_factor,jdbcType=DOUBLE},
    #{p_vol,jdbcType=INTEGER},
    #{p_info,jdbcType=VARCHAR},
    #{p_pack_parent,jdbcType=INTEGER},
    #{p_group_parent,jdbcType=INTEGER},
    #{p_order_full,jdbcType=INTEGER},
    #{p_statist_period,jdbcType=INTEGER},
    #{p_do_it, jdbcType=INTEGER}
    )
    }
</update>

and last is the stored procedure:

PROCEDURE pCreateItem (
                p_ITEM_DESC         item.ITEM_DESC%type,
                p_MIN_QTY           item.MIN_QTY%type,
                p_MAX_QTY           item.MAX_QTY%type,
                p_ABC               item.ABC%type,
                p_ITEM_WEIGHT       item.ITEM_WEIGHT%type,
                p_SIZE_CL           item.SIZE_CL%type,
                p_WEIGHT_CLASS      item.WEIGHT_CLASS%type,
                p_EXP_PERIOD        item.EXP_PERIOD%type,
                p_CODE              item.CODE%type,
                p_UM                item_pack.UM%type,
                p_IMG               item_pack.IMG%type,
                p_CONV_FACTOR       item_pack.CONV_FACTOR%type,
                p_VOL               item_pack.VOL%type,
                p_INFO              item_pack.INFO%type,
                p_PackParent        item_pack.pack_id%type,
                p_GroupParent       item_pack.pack_id%type,
                p_order_full        item.order_full%type default 0,
                p_statist_period    item.STATIST_PERIOD%type default 3,
                p_do_it             PLS_INTEGER DEFAULT 1
);

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:

1. Numeric Type Conversion
2. 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.

ORA-06502: PL/SQL: numeric or value error: character to number conversion error occurs when a character value is assigned to a numeric variable in the oracle PL/SQL code. When a non-numeric value is assigned to a numeric datatype variable, the character cannot be converted to a number. A numeric data type variable cannot be assigned a character value. If you try to assign character to number, the conversion error ORA-06502: PL/SQL: numeric or value error: character to number conversion error will be thrown.

The number conversion error happens when the character value is assigned to a number variable. It is not possible to convert the non-numeric character value to a numeric number. The data type of the variable should be changed to character or a numeric value should be assigned. The assigned value and the declared datatype must be same to store the value. Otherwise, the character to number conversion error ORA-06502: PL/SQL: numeric or value error: character to number conversion error would be shown.

Exception

If you run the above code in Oracle, you will get the stack trace error shown below. The numeric data type is attempted to be assigned to the character value.

declare
    empid numeric(4);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"

Problem

A character employee id is assigned to variable empid in the example below. The empid’s data type is a number with a size of four. The oracle error is thrown if the character value is assigned to the number data type variable.

declare
    empid numeric(4);
begin
    empid := 'A101';
end;

Output

declare
    empid numeric(4);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
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 data type of the variable should be changed to character data type. The character data type will store both the alphabet and the value of a number. The character data type is shown in the example below.

declare
    empid varchar(4);
begin
    empid := 'A101';
end;

Output

PL/SQL procedure successfully completed.

Solution 2

A number should be used as the value. Make sure the origin of the value. This variable receives the value incorrectly. If the code bug exists and is fixed, the value assigned to the variable would be a number value. The numeric value will be stored in the number data type variable.

declare
    empid numeric(4);
begin
    empid := 101;
end;

Output

PL/SQL procedure successfully completed.

Solution 3

Handling the exception from PL/SQL code is another method for dealing with this error. If an error occurs, handle it and take a different action for the value.

declare
    empid numeric(4);
begin
    empid := 'A101';
exception
    WHEN OTHERS THEN
        empid :=0;
end;

Output

PL/SQL procedure successfully completed.

Good Afternoon Community,

I’m Using Oracle DB 12c 

I’m trying to use To_Char to Convert Number to Char to add Formatting But I Can Not

Figure out Character to Number Conversion Error on Line 24   Commented and Highlighted in Red Below

——————————————————————————————————————-

Error report —

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 24

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.

——————————————————————————————————————

DECLARE

     v_subtotal number;

     v_original_subtotal number;

     v_P10_RECPT_NMBR1 VARCHAR2(25) := ‘9000000294’;

     v_P10_RECPT_NMBR2 VARCHAR2(25) := ‘9000000059’;

     v_P10_SubTotal Number(11,2)    := 3111.59;

     v_SubTotal_Error char(1)       := ‘T’;

BEGIN

     select Abs(Nvl(Sum(subtotal),0)) into v_subtotal

     from receiptdetail

     where refunded_recpt_nmbr = v_P10_RECPT_NMBR1;

     select Abs(nvl(subtotal,0)) into v_original_subtotal

     from receiptdetail

     where receiptnumber = v_P10_RECPT_NMBR2;

     —CHECK FOR POSITIVE NUMBERS — REFUNDS HAVE TO BE ENTERED WITH NEGATIVE AMOUNT—-P10_SUBTOTAL,’,’,’,») > 0

     IF TO_CHAR(v_P10_SubTotal, ‘99,999.99’) > ‘0’

     THEN v_SubTotal_Error := ‘X’;

     —check for excessive refund amounts

     ElsIF

———-Error Lies here on Line 24   ((ABS(To_Char(v_P10_SubTotal,’99,999.99′))  +  to_char(v_subtotal,’99,999.99′))  >   to_char(v_original_subtotal, ‘99,999.99’))

     Then v_SubTotal_Error := ‘Y’;

     End If;

      Dbms_Output.put_line(v_subtotal);

     Dbms_Output.put_line(v_original_subtotal);

     Dbms_Output.put_line(v_SubTotal_Error);

     Dbms_Output.put_line(v_P10_RECPT_NMBR1);

     Dbms_Output.put_line(v_P10_RECPT_NMBR2);

     Dbms_Output.put_line(v_P10_SubTotal);

END;

Thank You in Regards,

DSteele41

How to Resolve ORA-06502: PL/SQL: numeric or value error: character to number conversion error

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:

1. Numeric Type Conversion
2. 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.

Источник

ORA-6502: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Good Afternoon Community,

I’m Using Oracle DB 12c

I’m trying to use To_Char to Convert Number to Char to add Formatting But I Can Not

Figure out Character to Number Conversion Error on Line 24 Commented and Highlighted in Red Below

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 24

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

*Action: Change the data, how it is manipulated, or how it is declared so

that values do not violate constraints.

v_P10_RECPT_NMBR1 VARCHAR2(25) := ‘9000000294’;

v_P10_RECPT_NMBR2 VARCHAR2(25) := ‘9000000059’;

v_P10_SubTotal Number(11,2) := 3111.59;

v_SubTotal_Error char(1) := ‘T’;

select Abs(Nvl(Sum(subtotal),0)) into v_subtotal

where refunded_recpt_nmbr = v_P10_RECPT_NMBR1;

select Abs(nvl(subtotal,0)) into v_original_subtotal

where receiptnumber = v_P10_RECPT_NMBR2;

—CHECK FOR POSITIVE NUMBERS — REFUNDS HAVE TO BE ENTERED WITH NEGATIVE AMOUNT—-P10_SUBTOTAL,’,’,’,») > 0

IF TO_CHAR(v_P10_SubTotal, ‘99,999.99’) > ‘0’

THEN v_SubTotal_Error := ‘X’;

—check for excessive refund amounts

———-Error Lies here on Line 24 ((ABS(To_Char(v_P10_SubTotal,’99,999.99′)) + to_char(v_subtotal,’99,999.99′)) > to_char(v_original_subtotal, ‘99,999.99’))

Источник

Oracle / PLSQL: ORA-06502 Error Message

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:

1. You tried to assign a value to a numeric variable, but the value is larger than the variable can handle.
2. 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:

This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:

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).

And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.

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:

This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:

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.

And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.

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:

This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:

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:

Источник

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at «APPS.FND_NUMBER», line 20

Why I am getting this error

Answers

Because something like this is probably happening in your code:

Because in your programme fnd_number you have code that generates the error. Without you posting that code all that can be said for definite is that it breaks a rule based on the following:

Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Which can be found here (amongst many other places)

But what Hoek has suggested is correct , you can search for that in your code too

select sum (fnd_number.canonical_to_number (target.screen_entry_value))

where —absence_attendance_types2.input_value_id= target.input_value_id

Its happening only when I comment this table and its condition

Источник

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

hi am in oracle database 11g i have the following block

v_emp varchar2 := to_number(:vinput);

select v_emp from dual;

am geting error ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Answers

How can we reproduce what you report?

post code won’t compile.

What is actual content of «:vinput»?

what data do you enter for the bind variable :vinput?

First read the Frequently Asked Questions thread on —>

I take it you have no knowledge of PL/SQL correct? If so then please read the documentation on

But based on the simple code you wrote, you first have define a size on VARCHAR2 variable, then when you the executable section runs, you have SQL statement WITHOUT an INTO clause and that will fail since you are inside PL/SQL.

here’s a simple code snippet similar to what you want to do using SQL* Plus as I assume you want to receive input from the command line:

Specify the Varchar2 character length :

v_emp VARCHAR2(100) := to_number(:vinput);

SELECT v_emp INTO v_emp FROM dual;

i enter tz0151200 when i user select regexp_replace(‘PM0151200′,'[^[:digit:]]’) from dual; it got replace is there another way i can convert varchar to number without doing replacing also

Line 2: Why are you trying to convert a value from what ever the input value is to a NUMBER, to then assign it to a VARCHAR2 variable?

. Line 02. contains syntax errors. You need to specify a length for VARCHAR2, for example: VARCHAR2(32767) — This example is the max size for VARCHAR2() definition in 11g.

. If :vinput contains non-numeric values it will fail, as expected. Trying to convert a character string to a NUMBER rightly give the error ORA-

. Change line 2. to be the same data length as :vinput.

Line 4. What are you hoping to achieve by this line? PL/SQL expects you to «SELECT. INTO . FROM. «. and then you can do something with what ever is in the INTO variable

What sort of data is «tz01512200»?

. The use of «tz». is that denoting the acronym for «TIMEZONE»?

The REGEXP_REPLACE suggests it might be representing a time string of some sort as the regexp_replace include the acronym PM, as in AM or PM along with same digits in what you say is in :vinput.

As no-one except you knows exactly what you are trying to do, we are currently just guessing. Here’s a way in sqlplus, to mimic what you might possibly be trying to do:

Источник

Adblock
detector

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:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error occurs when a character value is assigned to a numeric variable in the oracle PL/SQL code. When a non-numeric value is assigned to a numeric datatype variable, the character cannot be converted to a number. A numeric data type variable cannot be assigned a character value. If you try to assign character to number, the conversion error ORA-06502: PL/SQL: numeric or value error: character to number conversion error will be thrown.

The number conversion error happens when the character value is assigned to a number variable. It is not possible to convert the non-numeric character value to a numeric number. The data type of the variable should be changed to character or a numeric value should be assigned. The assigned value and the declared datatype must be same to store the value. Otherwise, the character to number conversion error ORA-06502: PL/SQL: numeric or value error: character to number conversion error would be shown.

Exception

If you run the above code in Oracle, you will get the stack trace error shown below. The numeric data type is attempted to be assigned to the character value.

declare
    empid numeric(4);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"

Problem

A character employee id is assigned to variable empid in the example below. The empid’s data type is a number with a size of four. The oracle error is thrown if the character value is assigned to the number data type variable.

declare
    empid numeric(4);
begin
    empid := 'A101';
end;

Output

declare
    empid numeric(4);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
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 data type of the variable should be changed to character data type. The character data type will store both the alphabet and the value of a number. The character data type is shown in the example below.

declare
    empid varchar(4);
begin
    empid := 'A101';
end;

Output

PL/SQL procedure successfully completed.

Solution 2

A number should be used as the value. Make sure the origin of the value. This variable receives the value incorrectly. If the code bug exists and is fixed, the value assigned to the variable would be a number value. The numeric value will be stored in the number data type variable.

declare
    empid numeric(4);
begin
    empid := 101;
end;

Output

PL/SQL procedure successfully completed.

Solution 3

Handling the exception from PL/SQL code is another method for dealing with this error. If an error occurs, handle it and take a different action for the value.

declare
    empid numeric(4);
begin
    empid := 'A101';
exception
    WHEN OTHERS THEN
        empid :=0;
end;

Output

PL/SQL procedure successfully completed.

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:

1. You tried to assign a value to a numeric variable, but the value is larger than the variable can handle.
2. 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.