Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Converting T-SQL proc into Oracle

878314Jul 27 2011 — edited Jul 27 2011
I need help in converting the following stored procedure into PL/SQL code. Basically I need to know corresponding function names for the various T-SQL constructs below. The parameter @PROC_NUM is passed by the caller with value @@PROCID which is the T-SQL global variable for the ID of the current stored procedure being executed by SQL Server. Thanks in advance for all the help.

CREATE PROCEDURE [dbo].MARS_MPPM_HANDLE_ERROR
@PROC_NUM INT
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ERROR_NUMBER INT
DECLARE @ERROR_SEVERITY INT
DECLARE @ERROR_STATE INT
DECLARE @ERROR_PROCEDURE Varchar(1000)
DECLARE @ERROR_LINE INT
DECLARE @ERROR_MESSAGE Varchar(8000)
DECLARE @PROC_NAME Varchar(255)

SELECT
@ERROR_NUMBER = ERROR_NUMBER(),
@ERROR_SEVERITY = ERROR_SEVERITY(),
@ERROR_STATE = ERROR_STATE(),
@ERROR_PROCEDURE = ERROR_PROCEDURE(),
@ERROR_LINE = ERROR_LINE(),
@ERROR_MESSAGE = ERROR_MESSAGE(),
@PROC_NAME = OBJECT_NAME( @PROC_NUM )

IF 50000 = @ERROR_NUMBER
BEGIN
RAISERROR( '%s -> %s', 16, 1, @PROC_NAME, @ERROR_MESSAGE )
END
ELSE
BEGIN
RAISERROR( '%s -> Error Number = %d, Error Severity = %d, Error State = %d, Error Procedure = %s, Error Line = %d, Error Message = %s',
16,
1,
@PROC_NAME,
@ERROR_NUMBER,
@ERROR_SEVERITY,
@ERROR_STATE,
@ERROR_PROCEDURE,
@ERROR_LINE,
@ERROR_MESSAGE )
END
END
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 24 2011
Added on Jul 27 2011
4 comments
477 views