From: "Tom Zamani" <tomz@redflex.com.au>
Subject: Re: Stored Procedure Example
Date: 2000/03/03
Message-ID: <89ne78$v5$2@perki.connect.com.au>#1/1
References: <mmEv4.20123$MZ2.309793@news1.wwck1.ri.home.com>
X-Priority: 3
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
X-Complaints-To: abuse@connect.com.au
X-Trace: perki.connect.com.au 952056872 997 203.63.133.169 (3 Mar 2000 04:14:32 GMT)
Organization: Customer of Connect.com.au Pty. Ltd.
X-MSMail-Priority: Normal
NNTP-Posting-Date: 3 Mar 2000 04:14:32 GMT
Newsgroups: comp.database.oracle,comp.databases.oracle,comp.databases.oracle.misc,comp.databases.oracle.server


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@home.com> wrote in message
news:mmEv4.20123$MZ2.309793@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@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
>
>