Oracle Function Accepting Precision 2 Number

functionsoracle

I'm trying to define an Oracle function that only accepts a NUMBER with a precision of, at most, 2 digits:

create or replace FUNCTION ftest(x NUMBER(2)) RETURN NUMBER IS
  BEGIN
    RETURN 42; -- body of function is not relevant for this question
  END;

However, it does not compile:

Error(1,24): PLS-00103: Encountered the symbol "(" when expecting one of 
       the following:     := . ) , @ % default character The symbol ":=" 
         was substituted for "(" to continue.

How can I fix ftest's definition to only allow an input NUMBER of precision 2?

Best Answer

in oracle plsql function or procedure prototype you can just specify datatype and not length or precision and so you want control length or precision you should handle it in your function or procedure body for example:

create or replace FUNCTION ftest(x NUMBER) RETURN NUMBER IS
BEGIN
    if length(x) > 2 then
    return;
    end if;
    RETURN 42; -- body of function is not relevant for this question
END;

Or

create or replace FUNCTION ftest(x NUMBER) RETURN NUMBER IS
t number(2);
BEGIN

    t:=x;
    RETURN 42; -- body of function is not relevant for this question
    EXCEPTION WHEN VALUE_ERROR THEN
    RETURN -1;

END;
Related Question