CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
S B
S B

Reputation: 309

can't use fraction as parameter for procedure in oracle

I'm writing a stored procedure for my project. One of the things the stored procedure needs to do is to update a value. the type of this value is defined as number(6,3).

my stored procedure looks somewhat like this:

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

    commit;   
END;

Then I call the function like: execute procedure_name(1, 0.8)

This gives me a conversion error. I also tried decimal but it still gives a conversion error. I even tried to define the parameter as this: tablename.columnname%type but it didn't work

The error I receive is:

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

ORA-06512: in regel 1

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

One could try to use the following fragment of code to recreate the database:

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

Does anyone have an idea why this is not working?

Kind regards

EDIT

Calling the procedure from my application works. Directly calling it in SQL fails but calling the procedure using C# seems to work. My application runs on ASP.NET Core and I'm mapping the double value with OracleDbType.Double

Upvotes: 3

Views: 361

Answers (1)

wolφi
wolφi

Reputation: 8361

I can reproduce the error:

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 64bit)

Upvotes: 2

Related Questions