Thanks Jim. I know that it is much faster to do it in a single SQL.
But unfortunately this procedure is part of a java application and new
requirements are forcing us to change the procedure. We tested the
procedure in Oracle 8i but fails in 9i.
I am not able to find the typo.
Any help will be appreciated.
Thanks
Pawan
"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<F5Qhc.2510$_L6.415029_at_attbi_s53>...
> "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 - 15:20:29 CDT
