Using "BEGIN" keyword improperly

user_4845747 0
Viewed 86

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?

plsql oracle sql
3 Answers

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;
user_15893790

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;
user_61863330

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

user_31377020
Related