Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored Procedure Example
Try this ....
1.
CREATE OR REPLACE FUNCTION spAddUser(inUserId INTERGER, vcFirstName VARCHAR2, vcLastName VARCHAR2, vcErrMessage VARCHAR2 DEFAULT 'NO ERROR')RETURN NUMBER
VcErrorMessage := 'You must pass a valid UserId.'; RETURN (-1);
/* Any Exception When Inserting */ /* SQLERRM Oracle Error Message */ WHEN OTHERS THEN VcErrorMessage := SQLERRM; RETURN (-1);
2.
CREATE OR REPLACE PROCEDURE spAddUser(inUserId INTERGER,
vcFirstName VARCHAR2, vcLastName VARCHAR2, vcErrMessage VARCHAR2 DEFAULT 'NO ERROR')IS
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;
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
![]() |
![]() |