CODEWITHSUNDEEP
sqloracle-databaseplsql
TheHumblePedestrian
TheHumblePedestrian

Reputation: 163

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.

Upvotes: 3

Views: 2520

Answers (1)

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 6

Related Questions