oracle - 不能使用分数作为 oracle 中过程的参数

Question

我正在为我的项目编写一个存储过程。存储过程需要做的事情之一是更新值。该值的类型定义为number(6,3)。

我的存储过程看起来有点像这样:

 create procedure procedure_name(id number, length number)
    as a boolean;
BEGIN
update "tablename" tbl
    set "length" = length
where tbl."id" = id;  

    commit;   
END;

然后我调用该函数，如下所示:execute procedure_name(1, 0.8)

这给了我一个转换错误。我也尝试过十进制，但它仍然给出转换错误。我什至尝试将参数定义为: tablename.columnname%type 但它不起作用

我收到的错误是:

ORA-06502: PL/SQL: numerieke fout of fout in waarde : character to number conversion error.

ORA-06512: in regel 1

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

可以尝试使用以下代码片段来重新创建数据库:

CREATE TABLE "tablename" 
(    
    "id" NUMBER(12,0) NOT NULL ENABLE,  
    "length" NUMBER(6,3)
) SEGMENT CREATION IMMEDIATE 

有人知道为什么这不起作用吗？

亲切的问候

编辑

从我的应用程序调用该过程是有效的。直接在 SQL 中调用它失败，但使用 C# 调用该过程似乎可行。我的应用程序在 ASP.NET Core 上运行，我使用 OracleDbType.Double

映射 double 值

Answer 1

我可以重现该错误:

CREATE TABLE t (n NUMBER);
INSERT INTO t VALUES (10);

CREATE OR REPLACE PROCEDURE p (p_n NUMBER) AS
BEGIN 
  UPDATE t SET n=p_n;
END p;
/

-- ok:
exec p(8/10);
PL/SQL procedure successfully completed.

-- ok:
DECLARE
  y NUMBER := 0.8;
BEGIN
  p(y);
END;
/
PL/SQL procedure successfully completed.

--error:
exec p(0.8);

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
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.

(Oracle 11.2.0.4.0 EE 64 位)