调用execute immediate时出现数字溢出oracle错误

Question

我定义了一个过程，将公式(类型值String(解析为数值：公式中的某些大小写值可能会执行或引发错误数字溢出。代码：

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;

在这种情况下，会引发错误数字溢出。当我增加值操作数大于/小于10个字符1时，它工作正常。

如果公式中的值包含数字1，我会尝试检查一些值，并查看大部分错误。这是原因吗？

谢谢大家！

Answer 1

当您在PL/SQL中乘以整数时，实际上是在使用pls_integers。

pls_integer2147483647有一个上限，您的示例2222222222中使用的乘法超过了这个上限，因此导致数字溢出。

来自官方文档，也来自ask Tom网站：

如果两个PLS_INTEGER值溢出PLS_INTEGER范围，则即使将结果分配给NUMBER数据类型，也会引发溢出异常。

为了消除这种情况，我们可以将表达式中的一个值设为数字。

案例1：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：通过将其中一个表达式设为十进制，Oracle将隐式将其转换为数字

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;
/