Trouble with a simple stored procedure..

From: Gary <gary_at_designercontrols.com>
Date: 19 Nov 2004 11:32:54 -0800
Message-ID: <c542a6f3.0411191132.1e78a835@posting.google.com>

Hello, I'm a seasoned SQL Server developer and new to Oracle. I am trying to write a simple SP that detects if a record exists for a particular GUID.

If not, it creates the record with a new id, the GUID and an e-mail address. If it does already exist, it updates the e-mail address. Either way, it returns the id back to the caller.

The table looks like this..

CREATE TABLE SCANA.SCOOPCREATORS
(

    SCOOPID NUMBER(1)     NOT NULL,
    GUID    VARCHAR2(36)  NOT NULL,
    EMAIL   VARCHAR2(500) NOT NULL,

    CONSTRAINT SYS_IOT_TOP_193763
    PRIMARY KEY (SCOOPID) ENABLE VALIDATE )

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;
/ Received on Fri Nov 19 2004 - 13:32:54 CST