What's wrong with IF ELSIF?

Question

    DECLARE DBName VARCHAR(100); ApplicationURL VARCHAR(100);

BEGIN
select NAME into DBName FROM v$database;  

  IF DBName = 'WAMDEV' THEN ApplicationURL := 'http://srpwam10:9080/maximo';
      ELSIF  DBName ='WAMDEVPJ' THEN ApplicationURL := 'http://srpwam10:9080/maximo';
      ELSIF  DBName = 'WAMTST' THEN ApplicationURL := 'http://wamtest/maximo'
      ELSIF  DBName = 'WAMTSTPJ' THEN ApplicationURL := 'http://wamtest/maximo'
      ELSIF  DBName = 'WAMQA' THEN ApplicationURL := 'http://wamqa/maximo'
      ELSIF  DBName = 'WAMQAPJ'  THEN ApplicationURL := 'http://wamqa/maximo'
      ELSE  DBName = 'WAMP' THEN ApplicationURL := 'http://wam/maximo'
    END IF ;
    DBMS_OUTPUT.PUT_LINE(ApplicationURL)
    END

It gives me error: ORA-06550: line 9, column 7: PLS-00103: Encountered the symbol "ELSIF" when expecting one of the following:

& = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
1. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.

; terminates statements, meaning your if terminates after the first "then", leaving elsif a naked/illegal usage. — Marc B
@MarcB - not quite in PL/SQL; it's complaining about the ELSIF on line 9 because there isn't a semicolon at the end of line 8 — Alex Poole

kevinskio kevinskio · Accepted Answer · 2016-06-17T18:27:03

added the semi colons you were missing

DECLARE
  DBNAME         VARCHAR(100);
  APPLICATIONURL VARCHAR(100);

BEGIN
  SELECT NAME INTO DBNAME FROM V$DATABASE;

  IF DBNAME = 'WAMDEV' THEN
    APPLICATIONURL := 'http://srpwam10:9080/maximo';
  ELSIF DBNAME = 'WAMDEVPJ' THEN
    APPLICATIONURL := 'http://srpwam10:9080/maximo';
  ELSIF DBNAME = 'WAMTST' THEN
    APPLICATIONURL := 'http://wamtest/maximo';
  ELSIF DBNAME = 'WAMTSTPJ' THEN
    APPLICATIONURL := 'http://wamtest/maximo';
  ELSIF DBNAME = 'WAMQA' THEN
    APPLICATIONURL := 'http://wamqa/maximo';
  ELSIF DBNAME = 'WAMQAPJ' THEN
    APPLICATIONURL := 'http://wamqa/maximo';
  ELSE
    DBNAME := 'WAMP';
    APPLICATIONURL := 'http://wam/maximo';
  END IF;
  DBMS_OUTPUT.PUT_LINE(APPLICATIONURL);
END;

A CASE statement could also be used and in some eyes is a cleaner look.

What's wrong with IF ELSIF?

1 Answers