0

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?

  • 2
    That's not the way to go about things. You probably want to check in the function and raise an exception – Philᵀᴹ Nov 7 '16 at 17:39
  • 1
    Realistically, you can't. You can create a subtype that documents the fact that you're only expecting a number(2) but a caller can happily pass in any numeric value. You could check the value passed in and throw an error if it doesn't meet whatever requirements you have. – Justin Cave Nov 7 '16 at 17:40
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.