Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored Procedure Example
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
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
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