Re: Trouble with a simple stored procedure..

Gary wrote:
> 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.
<snip>
> CREATE OR REPLACE PROCEDURE SCANA.GET_SCOOP_CREATOR_ID (
> creatorId OUT INTEGER,
> guid IN VARCHAR2,
> email IN VARCHAR2 ) IS

<snip>

You have to get away from having parameters (or variables) with names that might be confused with columns (or other objects). I'd suggest adding the prefix IN_ or OUT_ here but whatever coding standards your organization decides on doesn't really matter as long as there's something in place.

As for the procedural logic, you could select for the existing row and catch the exception side if it's not found ...

begin

   select creatorid into out_creatorid ... where guid = in_guid    update ...
   exception

     when no_data_found then
       insert ... returning creatorid into out_creatorid;

--
Richard Kuhler