Error in Procedure

From: Pawan <pawanputrahanuman_at_yahoo.com>
Date: 22 Apr 2004 06:03:37 -0700
Message-ID: <84857442.0404220503.5a4fced5@posting.google.com>

Can you gurus please help me. I have aprocedure that runs fine in Oracle 8i but fails in Oracle 9i
Here is the procedure

---

CREATE OR REPLACE PROCEDURE APSP_UPDATE_USER_DATA IS CURSOR C1 IS
SELECT FIRST_NAME USER_FIRSTNAME, LAST_NAME USER_LASTNAME, EMAIL_ID USER_EMAIL, USER_SSO USER_SSO
FROM APST_USERS_ALL;

SSO_FIRSTNAME APST_USERS_ALL.FIRST_NAME%TYPE :=null;
SSO_LASTNAME APST_USERS_ALL.LAST_NAME%TYPE :=null;
SSO_EMAIL APST_USERS_ALL.EMAIL_ID%TYPE :=null;
SSO_UID APST_USERS_ALL.USER_SSO%TYPE :=null;

BEGIN

        FOR V_COUNTER IN C1
        LOOP

  BEGIN      SELECT FIRST_NAME, LAST_NAME, EMAIL_ID, GESSOUID INTO SSO_FIRSTNAME, SSO_LASTNAME, SSO_EMAIL, SSO_UID

                    FROM SMET_USER_DATA WHERE UPPER(GESSOUID) =

UPPER(V_COUNTER.USER_SSO);

     IF SSO_FIRSTNAME IS NOT NULL AND
                    UPPER(SSO_FIRSTNAME) !=
UPPER(V_COUNTER.USER_FIRSTNAME)
                    THEN UPDATE APST_USERS_ALL SET FIRST_NAME =
SSO_FIRSTNAME WHERE UPPER(USER_SSO) = UPPER(SSO_UID);
      END IF;

                    IF SSO_LASTNAME IS NOT NULL AND
                    UPPER(SSO_LASTNAME) !=
UPPER(V_COUNTER.USER_LASTNAME)
                    THEN UPDATE APST_USERS_ALL SET LAST_NAME =
SSO_LASTNAME WHERE UPPER(USER_SSO) = UPPER(SSO_UID);
      END IF;
                    IF SSO_EMAIL IS NOT NULL AND
                    UPPER(SSO_EMAIL) != UPPER(V_COUNTER.USER_EMAIL)
                    THEN UPDATE APST_USERS_ALL SET EMAIL_ID =
SSO_EMAIL WHERE UPPER(USER_SSO) = UPPER(SSO_UID);
      END IF;

   EXCEPTION
         WHEN NO_DATA_FOUND THEN
        NULL;

   END;
  END LOOP;
 COMMIT;
END APSP_UPDATE_USER_DATA;
/

---

The error message is
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   begin case declare end exit for goto if loop mod null pragma    raise return select update while with <an identifier>

---

Thanks in advance Received on Thu Apr 22 2004 - 08:03:37 CDT