Function dont return decimal places

Question

I have an Oracle function that performs calculations, but didn't return the decimal of the result.

CREATE OR REPLACE Function MY_TESTE
   RETURN DECIMAL
   IS
BEGIN

  RETURN 1/3;

END;

If I executed the function

SELECT MY_TESTE 
  FROM DUAL

It return the value 0 with any decimal places. But it expect to return 0.3333 Any ideas ?

Not familiar with Oracle SQL, but in T-SQL the return type of a division is the same as the type of the arguments. That is, integer. — Mr Lister, Jul 17 '13 at 12:40

score 5 · Answer 1 · answered Jul 17 '13 at 13:05

DECIMAL is declared in the package SYS.STANDARD as a subtype of NUMBER (as are most numeric types in Oracle), specifically as NUMBER(38, 0), which means that DECIMAL values are not able to have digits to the right of the decimal point. In order to have DECIMAL values with numbers to the right of the decimal point they have to be declared as such, similar to

dValue  DECIMAL(38, 4)

However, this won't help if you want to return such a value from a function, because you can't have precision specifiers on a function return type.

In order to have a DECIMAL value with digits to the right of the decimal place returned from a function you need to declare a subtype of DECIMAL that specifies the correct number of decimal places and use that subtype as the return type of your function. Here's an example:

DECLARE 
  SUBTYPE DEC_10_3 IS DECIMAL(10, 3);

  FUNCTION TEST1 RETURN DECIMAL
  IS
    d  DECIMAL := 1/3;
  BEGIN
    RETURN d;
  END TEST1;

  FUNCTION TEST2 RETURN DECIMAL
  IS
    d  DECIMAL(10, 3) := 1/3;
  BEGIN
    RETURN d;
  END TEST2;

  FUNCTION TEST3 RETURN DEC_10_3 IS
    d DEC_10_3 := 1/3;
  BEGIN
    RETURN d;
  END TEST3;
BEGIN
  -- Test statements here
  DBMS_OUTPUT.PUT_LINE('TEST1=' || TEST1);
  DBMS_OUTPUT.PUT_LINE('TEST2=' || TEST2);
  DBMS_OUTPUT.PUT_LINE('TEST3=' || TEST3);
END;

Running the above will produce

TEST1=0
TEST2=0
TEST3=.333

Share and enjoy.

score 3 · Answer 2 · edited May 23 '17 at 12:08

3

Change the return type to NUMBER

Here you have explained the difference between DECIMAL and NUMBER datatype in Oracle: decimal(s,p) or number(s,p)?

edited May 23 '17 at 12:08

Community

1
1

answered Jul 17 '13 at 12:49

kpater87

1,190
12
31

The major problem is that i can declare the return value with decimal places, like CREATE OR REPLACE Function MY_TESTE RETURN DECIMAL(18,4) IS BEGIN RETURN 1/3; END; – Rui Soares Jul 17 '13 at 14:40
So the answer of @Bob Jarvis is what you need. – kpater87 Jul 17 '13 at 14:43

score 0 · Answer 3 · answered Jul 17 '13 at 12:57

0

in Oracle DECIMAL is like integer by default for storage division result you need decimal(10, 3) for example

declare
    l_res1 decimal(10, 3);
    l_res2 decimal;
begin

    l_res1 := 1 / 3;
    dbms_output.put_line(l_res1);

    l_res2 := 1 / 3;
    dbms_output.put_line(l_res2);
end;

but you cant use decimal(10, 3) as returned type in you function so change decimal to number

good luck

answered Jul 17 '13 at 12:57

Galbarad

461
3
16

The major problem is that i can declare the return value with decimal places, like CREATE OR REPLACE Function MY_TESTE RETURN DECIMAL(18,4) IS BEGIN RETURN 1/3; END; – Rui Soares Jul 17 '13 at 14:16
try to use this function in select ;) – Galbarad Jul 17 '13 at 14:27
@RuiSoares - I suspect you meant '...**can't** declare the return value with decimal place...". That's why in my answer I declared a subtype of DECIMAL, then had one of the functions return that subtype. It's a bit awkward, but in PL/SQL that's how it has to be done. Share and enjoy. – Bob Jarvis - Слава Україні Jul 17 '13 at 22:53
subtype is great solution, I forget about this feature ) – Galbarad Jul 18 '13 at 08:05

Alex Poole · Answer 4 · 2013-07-17T14:59:24.707

Bob Jarvis has explained the problem with using DECIMAL. You can use TRUNC (or ROUND, depending on how you want it to behave) and return a NUMBER that's still restricted to the required number of decimal places:

create or replace function my_test return number is
begin
  return trunc(1/3, 4);
end;
/

select my_test from dual;

   MY_TEST
----------
    0.3333

The difference between TRUNC and ROUND is better demonstrated with a different fraction:

create or replace function my_test return number is
begin
  return trunc(2/3, 4);
end;
/

select my_test from dual;

   MY_TEST
----------
    0.6666 

create or replace function my_test return number is
begin
  return round(2/3, 4);
end;
/

select my_test from dual;

FUNCTION MY_TEST compiled
   MY_TEST
----------
    0.6667

Notice the last version rounds the last digit up in this version.

Function dont return decimal places

4 Answers4