TheHumblePedestrian TheHumblePedestrian - 21 days ago 5
SQL Question

Why am I receiving PLS-00103 when attempting to create this simple function?

I am trying to create this function:

create or replace function g(sN int) return char(3) as
t char(3);
begin
select pt into t from (
select pTT as pt, pC
from ple
order by pC asc
) where sN <= pC and rownum <= 1;

return t;
end;
/


I receive this following errors:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/31 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
; is authid as cluster order using external varying character
large deterministic parallel_enable pipelined aggregate
result_cache accessible

2/9 PLS-00103: Encountered the symbol "CHAR" when expecting one of
the following:
, from into bulk
The symbol "," was substituted for "CHAR" to continue.


LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
. ( , * % & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk

11/8 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
end not pragma final instantiable order overriding static
member constructor map


My questions:


  1. In general, why do these errors happen or signify?

  2. More specifically, why am I receiving this error?



My research:
There are a number of questions involving PLS-00103 on SO, but none of them seem to fit my function.

Some problems that I have seen on SO that caused PLS-00103 are:


  1. The accidental use of a reserved word, like max or min, as a variable name.

  2. The incorrect use of looping structure keywords, like using EXIT LOOP to end a
    loop instead END LOOP.

  3. Incorrectly assigning a value to a variable, like x + 9 = x.



Of course, this isn't a comprehensive list of the problems I've indicated as PLS-00103, but I don't think my function applies to any of the ones I have seen.

Answer

Parameters can only specify the base type, not precision, scale, length etc.

return char(3)

should be

return char

or perhaps better,

return ple.ptt%type

btw char is almost never a good idea. It doesn't avoid some overhead of variable length strings as some people seem to think, and it doesn't ensure that values such as 3-letter ISO currency codes will have the expected number of letters. All it will do is add blank spaces to the end of non-null values, sometimes when you don't expect it to, leading to obscure bugs. It was added solely for ANSI compatibility reasons, and you are really not supposed to use it in developing new systems using Oracle.