Re: Error in Procedure

"Pawan" <pawanputrahanuman_at_yahoo.com> wrote in message news:84857442.0404220503.5a4fced5_at_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

You probably have a typo. I think you could do this much faster in one sql statements. The row by row as Tom Kyte says is slow by slow. Jim Received on Thu Apr 22 2004 - 08:51:33 CDT