Converting T-SQL proc into Oracle
878314Jul 27 2011 — edited Jul 27 2011I 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