I don't understand what Java has to do with it. Start taking things out and
see what starts working. If you do it in sqlplus what line number does it
break on?
Jim
"Pawan" <pawanputrahanuman_at_yahoo.com> wrote in message
news:84857442.0404221220.332e60b3_at_posting.google.com...
> 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 - 20:46:37 CDT
