Stored Procedure Example

From: Louis Frolio <frolio_at_home.com>
Date: 2000/03/03
Message-ID: <mmEv4.20123$MZ2.309793_at_news1.wwck1.ri.home.com>#1/1

Greetings All, I have been a SQL DBA for some time now. However, and thankfully, my company is
moving to Oracle8i on Solaris. My current SQL server system has many stored procedures that have to
be converted to Oracle PL/SQL. I have not had a problem with the simple procedures, however, in some
of my procedures I do error trapping and I don't know if Oracle handles errors the same way. Below is
some code of a very trivial procedure written for SQL Server, I would appreciate it if someone out there
familiar with T-SQL and Microsoft PL/SQL could convert it to an Oracle stored procedure. I appreciate
any help that any of you might be able provide.

Regards, Louis
frolio_at_home.com

  CREATE PROCEDURE spAddUser

_at_inUserId INTEGER = NOT NULL,
_at_vcFirstName VARCHAR(35) = NULL,
_at_vcLastName VARCHAR(35) = NULL,
_at_vcErrorMessage VARCHAR(255) = 'NO ERROR.' OUTPUT
  AS

  SET _at_vcErrorMessage = 'NO ERROR.'

  IF (_at_inUserId IS NULL)
   BEGIN

     SET _at_vcErrorMessage = 'You must pass a valid UserId.'
     /* exit the procedure immediately */
     RETURN (-1)

   END   INSERT INTO tbUser
  VALUES (_at_inUserId, @vcFirstName, @vcLastName)

   IF (_at__at_ERROR <> 0)
    BEGIN

      SET _at_vcErrorMessage = 'spAddUser, Pos 1, [Error Inserting Record].'
      /* exit the procedure immediately */
      RETURN(-1)

    END   /* exit the procedure gracefully */
  RETURN(0)   /* Grant Permisstions */
  GRANT EXECUTE ON spAddUser TO WebUser
  GO Received on Fri Mar 03 2000 - 00:00:00 CET