| sql server stored procedure to oracle [message #64113] |
Fri, 17 December 2004 01:28 |
Emilia
Messages: 13
Registered: December 2004
|
Junior Member |
|
|
How to migrate the following stored procedure from MS SQL Server to ORACLE?
CREATE PROCEDURE spCXDB_checkConsultant
@consultant_ID int,
@Object_ID int
AS
SET XACT_ABORT ON
SELECT
consult
FROM
CXDBConsult
WHERE
Object_ID = @Object_ID AND
( consultant_ID = @consultant_ID OR consultant_ID IN (
SELECT
groupuid
FROM
sysmembers
WHERE
memberuid = @consultant_ID ) )
IF @@ROWCOUNT = 0
RETURN -1
RETURN 0
Oracle Migration Workbench
CREATE OR REPLACE FUNCTION SA.SPCXDB_CHECKCONSULTANT(
consultant_ID IN NUMBER DEFAULT NULL,
Object_ID IN NUMBER DEFAULT NULL,
RC1 IN OUT Omwb_emulation.globalPkg.RCT1)
RETURN INTEGER
AS
consultant_ID_ NUMBER(10,0) := consultant_ID;
Object_ID_ NUMBER(10,0) := Object_ID;
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
NULL;
/*[[SPCONV-ERR(11)]]:(set XACT_ABORT) Manual conversion required*/
OPEN RC1 FOR
SELECT consult
FROM CXDBConsult
WHERE Object_ID = SPCXDB_CHECKCONSULTANT.Object_ID_
AND
(consultant_ID = SPCXDB_CHECKCONSULTANT.consultant_ID_
OR consultant_ID IN (
SELECT groupuid
FROM sysmembers
WHERE memberuid = SPCXDB_CHECKCONSULTANT.consultant_ID_ ));
BEGIN
StoO_error := 0;
SELECT 1 INTO StoO_rowcnt FROM DUAL
WHERE EXISTS (
SELECT consult
FROM CXDBConsult
WHERE Object_ID = SPCXDB_CHECKCONSULTANT.Object_ID_
AND
(consultant_ID = SPCXDB_CHECKCONSULTANT.consultant_ID_
OR consultant_ID IN (
SELECT groupuid
FROM sysmembers
WHERE memberuid = SPCXDB_CHECKCONSULTANT.consultant_ID_
)) );
EXCEPTION
WHEN NO_DATA_FOUND THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
/*[[SPCONV-ERR(24)]]:('TABLE') Source TABLE or VIEW DEMODMS.DBO.SYSMEMBERS not found*/
IF StoO_rowcnt = 0 THEN
RETURN -1;
END IF;
RETURN 0;
END SPCXDB_CHECKCONSULTANT;
Problem table sysmembers.
Thank's for you help
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
