Re: Stored Procedure Example

From: Hemant Deshpande <hdeshpande_at_novell.com>
Date: 2000/03/06
Message-ID: <38C43D73.43001C1C@novell.com>#1/1

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 - 00:00:00 CST