Re: Stored Procedure Example

From: Hemant Deshpande <hdeshpande_at_novell.com>
Date: 2000/03/06
Message-ID: <38C43CF7.59B21299_at_novell.com>#1/1

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
> _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 Mon Mar 06 2000 - 00:00:00 CET