Error numeric overflow oracle when call execute immediate

Question

I define a procedure do parse a formula (type value String) to a value number: Some case value in formula can execute or raise error numeric overflow. Code :

DECLARE
    formula VARCHAR2(1000) := '1111111111 * 2';
    val NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'BEGIN :res := '||formula||'; END;' USING OUT val;
    DBMS_OUTPUT.PUT_LINE ( 'formula = ' || formula );
    DBMS_OUTPUT.PUT_LINE ( 'val = ' || val );
END;

In this case then raise error numeric overflow. When i increase value operands more/less than 10 character 1 then it working right.

I try to check a few values and see most of the error if the value in the formula contains the number 1. Is this the reason?

Thank guys!

Answer 1

When you multiply integers in PL/SQL it's actually using pls_integers.

There is an upper limit for pls_integer 2,147,483,647 and multiplication used in the example of yours 2,222,222,222 exceeds this and thus result in numeric overflow.

From the offcial docs and also from ask Tom site:

A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception, even if you assign the result to a NUMBER data type.

To get rid of this we can make one of the values in the expression a number.

Case1: with CAST

DECLARE
    formula VARCHAR2(1000) := 'cast(1111111111 as number) * 2';
    val NUMBER;
BEGIN
    EXECUTE IMMEDIATE
    'BEGIN '||
    ' :res := '||formula||';'||
    'END;' USING OUT val;
    DBMS_OUTPUT.PUT_LINE ( 'formula = ' || formula );
    DBMS_OUTPUT.PUT_LINE ( 'val = ' || val );
END;
/

Case2: by making one of the expression to decimal and Oracle will implicitly convert it to number

DECLARE
    formula VARCHAR2(1000) := '1111111111 * 2.0';
    val NUMBER;
BEGIN
    EXECUTE IMMEDIATE
    'BEGIN '||
    ' :res := '||formula||';'||
    'END;' USING OUT val;
    DBMS_OUTPUT.PUT_LINE ( 'formula = ' || formula );
    DBMS_OUTPUT.PUT_LINE ( 'val = ' || val );
END;
/