Re: Stored Procedure Example

From: Tom Zamani <tomz_at_redflex.com.au>
Date: 2000/03/03
Message-ID: <89ne78$v5$2@perki.connect.com.au>#1/1

Procedures do not return values the way that functions do(return x) I could say Error handeling in Oracle is the best, You can almost do anything.
In oracle functions can not update database, I think in oracle 8i they can but I have not done that.
Have a look at the modification I have made to you procedure, you will underestand what I have done.
Tom

CREATE or replace procedure spAddUser(

                    inUserId   in     INTEGER default 0,
                    vcFirstName  in   VARCHAR(35)  default null,
                    vcLastName    in  VARCHAR(35)  :=null,
                    vcErrorMessage out VARCHAR(255))

   AS
App_Erro exception;
begin

    vcErrorMessage := 'NO ERROR.'

   IF (inUserId IS NULL) then
    BEGIN

      SET vcErrorMessage = 'You must pass a valid UserId.'
      /* exit the procedure immediately */
        Rasie App_Error;
     END

   INSERT INTO tbUser
   VALUES (inUserId, vcFirstName, vcLastName)

    IF (ERROR <> 0)

     BEGIN
       SET vcErrorMessage = 'spAddUser, Pos 1, [Error Inserting Record].'
       /* exit the procedure immediately */
        Rasie App_Error;
     END

exception when
App_Error then
vcErrorMessage :='Found Error'
End;

Louis Frolio <frolio_at_home.com> wrote in message news:mmEv4.20123$MZ2.309793_at_news1.wwck1.ri.home.com...
> 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 Fri Mar 03 2000 - 00:00:00 CST