Re: Stored Procedure Example

From: Hemant Deshpande <hdeshpande_at_novell.com>
Date: Mon, 06 Mar 2000 15:19:19 -0800
Message-ID: <38C43CF7.59B21299@novell.com>

couple of ways ...

Try this

1.

CREATE OR REPLACE FUNCTION spAddUser(inUserId    INTERGER,
                                     vcFirstName VARCHAR2,
                                     vcLastName  VARCHAR2,
                                     vcErrMessage VARCHAR2 DEFAULT 'NO ERROR')

RETURN NUMBER
IS
BEGIN
   IF(inUserId IS NULL) THEN

      VcErrorMessage := 'You must pass a valid UserId.';
      RETURN (-1);

   END IF;
   --
   BEGIN
      INSERT INTO tbUser VALUES(inUserId,vcFirstName,vcLastName);    EXCEPTION

      /* Any Exception When Inserting */
      /* SQLERRM Oracle Error Message */
      WHEN OTHERS THEN
         VcErrorMessage := SQLERRM;
         RETURN (-1);

   END;
   --
END spAddUser;
/

2.
CREATE OR REPLACE PROCEDURE spAddUser(inUserId INTERGER,

                                     vcFirstName VARCHAR2,
                                     vcLastName  VARCHAR2,
                                     vcErrMessage VARCHAR2 DEFAULT 'NO ERROR')

IS
BEGIN
   IF(inUserId IS NULL) THEN

      VcErrorMessage := 'You must pass a valid UserId.';    END IF;
   --
   BEGIN
      INSERT INTO tbUser VALUES(inUserId,vcFirstName,vcLastName);    EXCEPTION

      /* Any Exception When Inserting */
      /* SQLERRM Oracle Error Message */
      WHEN OTHERS THEN
         VcErrorMessage := SQLERRM;

   END;
   --
END spAddUser;
/

Louis Frolio wrote:

> 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
> @inUserId INTEGER = NOT NULL,
> @vcFirstName VARCHAR(35) = NULL,
> @vcLastName VARCHAR(35) = NULL,
> @vcErrorMessage VARCHAR(255) = 'NO ERROR.' OUTPUT
> AS
>
> SET @vcErrorMessage = 'NO ERROR.'
>
> IF (@inUserId IS NULL)
> BEGIN
> SET @vcErrorMessage = 'You must pass a valid UserId.'
> /* exit the procedure immediately */
> RETURN (-1)
> END
>
> INSERT INTO tbUser
> VALUES (@inUserId, @vcFirstName, @vcLastName)
>
> IF (@@ERROR <> 0)
> BEGIN
> SET @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 Mon Mar 06 2000 - 17:19:19 CST