CODEWITHSUNDEEP
oracle-databaseplsql
c_steidl
c_steidl

Reputation: 107

PL/SQL - define precision and scale for number parameter of stored procedure

I have a stored procedure that inserts the values from parameters into a table. One of the parameters should allow numbers with a precision and a scale to allow calling the procedure the following way: EXECUTE operation(10, 10, 2.5). The problem is there seems to be no way to define such things for parameters in PL/SQL. It works when I set the type of the parameter to VARCHAR2 and call to_number afterwards but this would alter the way of calling the procedure because of the apostrophes.

So is there a way to pass a number in a format such as NUMBER(8,2) to a procedure? Thanks in advance.

Upvotes: 2

Views: 1535

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

I'm not sure I understand the problem.

What's wrong with

create procedure operation (par_1 in number,
                            par_2 in number,
                            par_3 in number
                           )
as ...

It accepts (10, 10, 2.5). So ...?

Demonstration:

SQL> create table test (a number, b number, c number);

Table created.

SQL> create or replace procedure operation
  2    (par_1 in number, par_2 in number, par_3 in number)
  3  as
  4  begin
  5    insert into test (a, b, c) values (par_1, par_2, par_3);
  6  end;
  7  /

Procedure created.

SQL> exec operation (10, 10, 2.5);

PL/SQL procedure successfully completed.

SQL> select * from test;

         A          B          C
---------- ---------- ----------
        10         10        2,5

SQL>

Upvotes: 2

Related Questions