Re: Trouble with a simple stored procedure..

Gary wrote:

> I'm trying to create the following SP but it fails. Can anyone help?
>
> Thanks,
> Gary
>
>
> CREATE OR REPLACE PROCEDURE SCANA.GET_SCOOP_CREATOR_ID (
> creatorId OUT INTEGER,
> guid IN VARCHAR2,
> email IN VARCHAR2 ) IS
>
> BEGIN
> IF NOT EXISTS (SELECT CREATORID FROM ScoopCreators WHERE GUID =
> guid) BEGIN
> INSERT INTO SCOOPCREATORS (CREATORID, GUID, EMAIL)
> VALUES(SCANA.SEQ_SCOOPCREATORS.NEXTVAL, guid, email);
> ELSE
> UPDATE SCOOPCREATORS SET EMAIL=email WHERE GUID=guid
> END
>
> SELECT CREATORID INTO creatorId FROM ScoopCreators WHERE GUID =
> guid
> END;
> /

Where are you getting your syntax? Richard gave you a solution but you just can't do things in Oracle like PL/SQL is TSQL or like Oracle is SQL Server. The architectures are completely different.

And your code ... IF NOT EXISTS? ... IF without END IF; ... DML statements without semicolons at the end? END of block statements without semicolons at the end? Column names and variables with the same name? My students half-way through the first quarter wouldn't make such elementary mistakes.

My suggestion: Purchase a copy of Tom Kyte's book "Expert one-on-one Oracle." The first three chapters specifically address those that are coming to Oracle from SQL Server.

Oh and take a class on Oracle.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)