Using "BEGIN" keyword improperly

Question

I am getting an error in the code below. I have added an arror <------ where the problem is.

The error message says that a THEN is expected, but when I use THEN, then it says that a BEGIN is expected.

Error(27,6): PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     then and or The symbol "then" was substituted for "BEGIN" to continue. 
Error(30,6): PLS-00103: Encountered the symbol "END" when expecting one of the following:     , ; return returning

What am I doing wrong?

    create or replace PROCEDURE "sp_updateUserPassword"(newUserPwd IN VARCHAR2, curIsoUserUID IN NUMBER)
IS

curUserID NUMBER;
userDateCreated DATE;
oldUserPwd VARCHAR2(255);

BEGIN   
  
  SELECT ISOUID INTO curUserID FROM ISOUSERS WHERE ISOUID=curIsoUserUID;
  SELECT DATECREATE INTO userDateCreated FROM ISOUSERS WHERE ISOUID=curIsoUserUID;
  SELECT PASSWORD INTO oldUserPwd FROM ISOUSERS WHERE ISOUID=curIsoUserUID;
  
  IF(newUserPwd = oldUserPwd)
    THEN
      raise_application_error(-20000, 'The new password must be different from the previous password');
      RETURN;
  END IF;
    
  IF NOT EXISTS
                (
                  SELECT ISOUID 
                  FROM OLDUSERPASSWORDS 
                  WHERE ISOUID=curIsoUserUID
                )
     
     BEGIN           <------------ Error is here
        INSERT INTO OLDUSERPASSWORDS(ISOUID, DATECREATE, DATELASTCHANGE, CURRENTPASS, OLDPASS) 
        VALUES(curUserID, userDateCreated, SYSDATE, newUserPwd, oldUserPwd)
     END;
       

/*raise_application_error(-20000, 'TEST');*/
END "sp_updateUserPassword";

Update

Corrected the code, so it now looks like this:

   IF NOT EXISTS
                (
                  SELECT ISOUID 
                  FROM OLDUSERPASSWORDS 
                  WHERE ISOUID=curIsoUserUID
                )
    THEN     
      BEGIN           
          INSERT INTO OLDUSERPASSWORDS(ISOUID, DATECREATE, DATELASTCHANGE, CURRENTPASS, OLDPASS) 
          VALUES(curUserID, userDateCreated, SYSDATE, newUserPwd, oldUserPwd)
      END;
  END IF;

I am getting this error:

Error(30,7): PL/SQL: ORA-00933: SQL command not properly ended
Error(31,7): PLS-00103: Encountered the symbol "IF" when expecting one of the following:     ; <an identifier> <a double-quoted delimited-identifier>

Perhaps it's just a minor error that I am missing?

If one of these answers solved your problem, please don't forget to mark it as accepted to let the community know this issue is resolved. — SandPiper, Oct 08 '17 at 13:33

Tony Chiboucas · Answer 1 · 2017-10-06T14:08:00.890

PL/SQL Syntax Error for IF THEN ELSE

Correct syntax is:

IF condition THEN
  statements
END IF;

Your code is effectively:

IF condition THEN
  BEGIN
    statements
  END;

Corrected

IF NOT EXISTS
(
    SELECT ISOUID 
    FROM OLDUSERPASSWORDS 
    WHERE ISOUID=curIsoUserUID
)
THEN

    BEGIN           <------------ Error is NOT here
        INSERT INTO OLDUSERPASSWORDS(ISOUID, DATECREATE, DATELASTCHANGE, CURRENTPASS, OLDPASS) 
        VALUES(curUserID, userDateCreated, SYSDATE, newUserPwd, oldUserPwd);
    END;
END IF;

score 2 · Answer 2 · answered Oct 06 '17 at 05:06

You are getting your first error because you need to add a THEN clause before the BEGIN block to make the syntax of the ID statement complete and correct.

You are getting the second error:

Error(30,7): PL/SQL: ORA-00933: SQL command not properly ended

because you have not completed your INSERT statement. Terminate it with a semicolon and that should fix that error.

   IF NOT EXISTS
                (
                  SELECT ISOUID 
                  FROM OLDUSERPASSWORDS 
                  WHERE ISOUID=curIsoUserUID
                )
    THEN     
      BEGIN           
          INSERT INTO OLDUSERPASSWORDS(ISOUID, DATECREATE, DATELASTCHANGE, CURRENTPASS, OLDPASS) 
          VALUES(curUserID, userDateCreated, SYSDATE, newUserPwd, oldUserPwd);
      END;
  END IF;

score 1 · Answer 3 · answered Oct 05 '17 at 22:13

1

You forgot the THEN before the BEGIN, that goes with the IF NOT EXISTS.

answered Oct 05 '17 at 22:13

alzee

1,393
1
10
21

I'm not sure where the then should be? Looking at this similar situation, there is no THEN? https://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exist – MOR_SNOW Oct 05 '17 at 22:18
1

You have an `IF` in `IF NOT EXISTS` so you need a `THEN` to go with it. You should have a `THEN` on the line before the `BEGIN` where your error is. – alzee Oct 05 '17 at 22:25
1

@MOR_SNOW, [that stackoverflow issue](https://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exist) is for MSSQL syntax, but you're using PL/SQL here. – Tony Chiboucas Oct 05 '17 at 22:27

Using "BEGIN" keyword improperly

Update

3 Answers3

PL/SQL Syntax Error for IF THEN ELSE

Corrected