Hi All,
Hope doing well,
Sir i am migrating my whole Sql database to PL/Sql Database i migrated my all tables successfully even some of the stored procedure also but one procedure i am not able to migrate.
Currently i am using Sql Developer 1.5.5.
and my procedure having output parameter so it is showing this message.
"Cannot translate unknown object unexpected AST node:
EXEC_PARAM_LIST: Line1: Column1"
Below is my stored procedure
ALTER PROCEDURE [dbo].[SP_GETEMPLOYEE](@OFFCOUNT OUTPUT INT, @ONCOUNT OUTPUT INT )
AS
BEGIN
DECLARE @COUNTEMPID INT=0, @EMPIDINDEX INT=1, @EMPID INT, @ONSTARTDATE DATE, @OFFSTARTDATE DATE, @OFFLOCATION INT=1,@ONLOCATION INT=2
DECLARE @EMPCOUNTTABLE AS TABLE(
ID INT IDENTITY,
EMPID INT
)
DECLARE @EMPONCOUNTTABLE AS TABLE(
ID INT IDENTITY,
EMPID INT
)
DECLARE @ONSHOREEMPTABLE AS TABLE(
ID INT IDENTITY,
ONSHOREEMPID INT
)
DECLARE @ONSHORETEMPTABLE AS TABLE(
EmployeeName varchar(50),
EmployeeId int
)
DECLARE @OFFSHORETEMPTABLE AS TABLE(
EmployeeName varchar(50),
EmployeeId int
)
dECLARE @OFFSHOREEMPTABLE AS TABLE(
ID INT IDENTITY,
OFFSHOREEMPID INT
)
INSERT INTO @EMPCOUNTTABLE(EMPID)SELECT DISTINCT(BI.EMPLOYEEID)FROM BillingInformation BI INNER JOIN ResourceMaster RM ON BI.EmployeeId = RM.EmployeeId WHERE LocationId=@OFFLOCATION and RM.IsActive = 1
SELECT @COUNTEMPID = COUNT(EMPID) FROM @EMPCOUNTTABLE
WHILE(@EMPIDINDEX <=@COUNTEMPID)
BEGIN
SET @EMPID=0
SET @ONSTARTDATE=NULL
SET @OFFSTARTDATE=NULL
SELECT @EMPID = EMPID FROM @EMPCOUNTTABLE WHERE ID=@EMPIDINDEX
IF @OFFLOCATION=1
BEGIN
SELECT @OFFSTARTDATE = MAX(ENDDATE) FROM BillingInformation WHERE EmployeeId=@EMPID AND LocationId=@OFFLOCATION
SELECT @ONSTARTDATE = MAX(ENDDATE) FROM BillingInformation WHERE EmployeeId=@EMPID AND LocationId=@ONLOCATION
IF @OFFSTARTDATE > @ONSTARTDATE
BEGIN
INSERT INTO @OFFSHOREEMPTABLE(OFFSHOREEMPID)SELECT @EMPID
INSERT INTO @OFFSHORETEMPTABLE(EmployeeName,EmployeeId) SELECT EmployeeName,EmployeeId FROM ResourceMaster RM where RM.EmployeeId =@EMPID and RM.IsActive=1
END
IF @ONSTARTDATE IS NULL
BEGIN
INSERT INTO @OFFSHOREEMPTABLE(OFFSHOREEMPID)SELECT @EMPID
INSERT INTO @OFFSHORETEMPTABLE(EmployeeName,EmployeeId) SELECT EmployeeName,EmployeeId FROM ResourceMaster RM where RM.EmployeeId =@EMPID and RM.IsActive=1
END
END
SET @EMPIDINDEX = @EMPIDINDEX+1
END
SET @EMPIDINDEX=1
SET @COUNTEMPID=0
INSERT INTO @EMPONCOUNTTABLE(EMPID)SELECT DISTINCT(BI.EMPLOYEEID)FROM BillingInformation BI inner join ResourceMaster RM on BI.EmployeeId = RM.EmployeeId WHERE BI.LocationId=@ONLOCATION and RM.IsActive=1
SELECT @COUNTEMPID = COUNT(EMPID) FROM @EMPONCOUNTTABLE
WHILE(@EMPIDINDEX <=@COUNTEMPID)
BEGIN
SET @EMPID=0
SET @ONSTARTDATE=NULL
SET @OFFSTARTDATE=NULL
SELECT @EMPID = EMPID FROM @EMPONCOUNTTABLE WHERE ID=@EMPIDINDEX
IF @ONLOCATION=2
BEGIN
SELECT @ONSTARTDATE = MAX(ENDDATE) FROM BillingInformation WHERE EmployeeId=@EMPID AND LocationId=@ONLOCATION
SELECT @OFFSTARTDATE = MAX(ENDDATE) FROM BillingInformation WHERE EmployeeId=@EMPID AND LocationId=@OFFLOCATION
IF @ONSTARTDATE > @OFFSTARTDATE
BEGIN
INSERT INTO @ONSHOREEMPTABLE(ONSHOREEMPID)SELECT @EMPID
INSERT INTO @ONSHORETEMPTABLE(EmployeeName,EmployeeId) SELECT EmployeeName,EmployeeId FROM ResourceMaster where ResourceMaster.EmployeeId =@EMPID
END
IF @OFFSTARTDATE IS NULL
BEGIN
INSERT INTO @ONSHOREEMPTABLE(ONSHOREEMPID)SELECT @EMPID
INSERT INTO @ONSHORETEMPTABLE(EmployeeName,EmployeeId) SELECT EmployeeName,EmployeeId FROM ResourceMaster where ResourceMaster.EmployeeId =@EMPID
END
END
SET @EMPIDINDEX = @EMPIDINDEX+1
END
SELECT @OFFCOUNT= COUNT(OFFSHOREEMPID) FROM @OFFSHOREEMPTABLE
SELECT @ONCOUNT= COUNT(ONSHOREEMPID) FROM @ONSHOREEMPTABLE
SELECT * FROM @OFFSHORETEMPTABLE
SELECT * FROM @ONSHORETEMPTABLE
RETURN
END
how to convert this procedure perfectly?
Thanks,