PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function pragma procedure

Question

i know the same answer is asked before, but i'm just staring blind on my code. what's wrong with my function???

other posts say it's missing a ; but i just can't find it.

FUNCTION checkIBAN
( p_IBAN in varchar2 )
RETURN varchar2
is
v_landcode  varchar2(2);
v_lengte    number(2);
v_omgezettelandcode varchar2;
v_teller    number(2) DEFAULT 1;
n           number(9);
d           varchar2;
BEGIN
v_landcode := SUBSTRING(p_IBAN, 1, 2);
select lengte
into v_lengte
from IBAN
where code = v_landcode;
if p_IBAN.LENGTH != v_lengte
    then return 'F';
end if;
v_omgezettelandcode := SUBSTRING(p_IBAN, 5) || SUBSTRING(p_IBAN, 1, 4);
WHILE v_teller < v_omgezettelandcode.LENGTH LOOP
    select getal
    into SUBSTRING(v_omgezettelandcode, v_lengte, v_lengte)
    from abc
    where SUBSTRING(v_omgezettelandcode, v_lengte, v_lengte) = letter;
    v_teller := v_teller + 1;
END LOOP;
d := v_omgezettelandcode;
n := SUBSTRING(d, 1, 9);
d := SUBSTRING(d, 10);
n := n/97;
WHILE d.LENGTH > 7 LOOP
    n := n || SUBSTRING(d, 1, 7);
    d := SUBSTRING(d, 8);
    n := n/97;
END LOOP;
n := n || d;
if n/97 = 1
    then return 'T';
    else return 'F';
end if;
END checkIBAN;

Answer 1

Another potential solution for those using DbVisualizer, (and maybe other tools?). This is what solved this problem for me.

Add these two lines to your code, like so:

--/ (all your code) /

Answer 2

thank you all, eventually i turned it over al little bit, now it works.

CREATE OR REPLACE FUNCTION checkIBAN
(p_IBAN in varchar2)
RETURN varchar2
is
v_landcode          varchar2(2);
v_lengte            number(2);
v_omgezettelandcode varchar2(32767);  -- max possible size for a VARCHAR2 var
v_teller            number(2) DEFAULT 1;
n                   number(9);
d                   varchar2(32767);
s                   VARCHAR2(32767);
v_omgezet           varchar2(32767);
v_number            varchar2(32767);
BEGIN
v_landcode := SUBSTR(p_IBAN, 1, 2);

select lengte
into v_lengte
from IBAN
where code = v_landcode;

if LENGTH(p_IBAN) != v_lengte then
return 'F';
end if;

v_omgezettelandcode := SUBSTR(p_IBAN, 5) || SUBSTR(p_IBAN, 1, 4);

while v_teller < LENGTH(v_omgezettelandcode) LOOP
    if SUBSTR(v_omgezettelandcode, v_teller, v_teller) in (0, 1, 2, 3, 4, 5, 6,     7, 8, 9)
        then v_omgezet := v_omgezet || SUBSTR(v_omgezettelandcode,     v_teller, v_teller);
    else 
        select getal
        into v_number
        from abc
        where letter = SUBSTR(v_omgezettelandcode, v_teller, v_teller);
        v_omgezet := v_omgezet || v_number;
    end if;
end loop;

d := v_omgezet;
n := SUBSTR(d, 1, 9);
d := SUBSTR(d, 10);
n := n/97;

WHILE LENGTH(d) > 7 LOOP
n := n || SUBSTR(d, 1, 7);
d := SUBSTR(d, 8);
n := n/97;
END LOOP;

n := n || d;

if n/97 = 1
then return 'T';
else return 'F';
end if;
END checkIBAN;

Answer 3

SUBSTRING is not a function in Oracle - you're looking for SUBSTR.

A variable such as d cannot be declared as VARCHAR2 - it must be given a length. Note that this is different from a parameter, such as p_IBAN, or a return value declaration - in both cases a length is not required (or even allowed).

@wweicker correctly points out that you cannot SELECT into a SUBSTR, and must instead use a variable.

When these errors are corrected I think your function should look something like:

CREATE OR REPLACE FUNCTION checkIBAN
    (p_IBAN in varchar2)
  RETURN varchar2
is
  v_landcode          varchar2(2);
  v_lengte            number(2);
  v_omgezettelandcode varchar2(32767);  -- max possible size for a VARCHAR2 var
  v_teller            number(2) DEFAULT 1;
  n                   number(9);
  d                   varchar2(32767);
  s                   VARCHAR2(32767);
BEGIN
  v_landcode := SUBSTR(p_IBAN, 1, 2);

  select lengte
    into v_lengte
    from IBAN
    where code = v_landcode;

  if p_IBAN.LENGTH != v_lengte then
    return 'F';
  end if;

  v_omgezettelandcode := SUBSTR(p_IBAN, 5) || SUBSTR(p_IBAN, 1, 4);

  WHILE v_teller < v_omgezettelandcode.LENGTH LOOP
    select getal
      into s
      from abc
      where SUBSTR(v_omgezettelandcode, v_lengte, v_lengte) = letter;

    v_omgezettelandcode := SUBSTR(vomgezettelandcode, 1, v_lengte-1) ||
                           letter ||
                           SUBSTR(vomgezettelandcode, v_lengte+LENGTH(letter));

    v_teller := v_teller + 1;
  END LOOP;

  d := v_omgezettelandcode;
  n := SUBSTR(d, 1, 9);
  d := SUBSTR(d, 10);
  n := n/97;

  WHILE d.LENGTH > 7 LOOP
    n := n || SUBSTR(d, 1, 7);
    d := SUBSTR(d, 8);
    n := n/97;
  END LOOP;

  n := n || d;

  if n/97 = 1
    then return 'T';
    else return 'F';
  end if;
END checkIBAN;

Best of luck.

Share and enjoy.

Answer 4

You need to use CREATE OR REPLACE FUNCTION instead of just FUNCTION

ex.

CREATE OR REPLACE FUNCTION checkIBAN
( p_IBAN in varchar2 )
RETURN varchar2
is
  v_landcode  varchar2(2);
  v_lengte    number(2);
  v_omgezettelandcode varchar2;
  v_teller    number(2) DEFAULT 1;
  n           number(9);
  d           varchar2;
BEGIN
  v_landcode := SUBSTRING(p_IBAN, 1, 2);

  select lengte
  into v_lengte
  from IBAN
  where code = v_landcode;

  if p_IBAN.LENGTH != v_lengte
    then return 'F';
  end if;

  v_omgezettelandcode := SUBSTRING(p_IBAN, 5) || SUBSTRING(p_IBAN, 1, 4);

  WHILE v_teller < v_omgezettelandcode.LENGTH LOOP
    select getal
    into SUBSTRING(v_omgezettelandcode, v_lengte, v_lengte)
    from abc
    where SUBSTRING(v_omgezettelandcode, v_lengte, v_lengte) = letter;
    v_teller := v_teller + 1;
  END LOOP;

  d := v_omgezettelandcode;
  n := SUBSTRING(d, 1, 9);
  d := SUBSTRING(d, 10);
  n := n/97;

  WHILE d.LENGTH > 7 LOOP
    n := n || SUBSTRING(d, 1, 7);
    d := SUBSTRING(d, 8);
    n := n/97;
  END LOOP;

  n := n || d;

  if n/97 = 1
    then return 'T';
    else return 'F';
  end if;

END checkIBAN;

There is another error as well. Where you have:

select getal
into SUBSTRING(v_omgezettelandcode, v_lengte, v_lengte)
from abc
where SUBSTRING(v_omgezettelandcode, v_lengte, v_lengte) = letter;

You use INTO you must specify a variable. You can't specify the built in function 'SUBSTRING' to "select into"

ex.

select getal
into SOME_LOCAL_VARIABLE_NAME
from abc
where SUBSTRING(v_omgezettelandcode, v_lengte, v_lengte) = letter;