I converted the following SP (SQL Server) to Oracle. It doesn't compile. Any suggestions ?????
ALTER PROCEDURE [dbo].[Calendar_GetByDate]
(
@DueDate datetime,
@StaffId int,
@IsForMonth bit,
@SubpoennaString nvarchar(max),
@ActivityString nvarchar(max),
@TrainingString nvarchar(max),
@RequestString nvarchar(max),
@OtherString nvarchar(max),
@CaseOffenseString nvarchar(max),
@CaseIndividualString nvarchar(max),
@CaseInvestigationString nvarchar(max),
@CommunicationLogString nvarchar(max),
@CrimeSceneString nvarchar(max),
@CalenderDisplayList nvarchar(100),
@CalenderCaseId nvarchar(max)=null
)
AS
BEGIN
BEGIN TRY
Declare @dateStr as nvarchar(100)
--select @IsForMonth
If @IsForMonth = 1
Begin
SET @dateStr = 'month'
END
ELSE
BEGIN
SET @dateStr = 'day'
END
DECLARE @str AS nvarchar(max)
SET @str = '
(SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' + @SubpoennaString + ' as nvarchar(max)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN SubPoena_Master WITH (NOLOCK) ON [Calendar].EntityId = SubPoena_Master.SPID
Inner JOIN dbo.fnSplit(''' + @CalenderDisplayList + ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 3 AND datediff(' + @dateStr + ',[Calendar].[DueDate],''' + cast(@DueDate as nvarchar(20)) + ''') = 0 AND [StaffId] = ' + cast(@StaffId as nvarchar(10)) + '))
UNION
(SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' + @ActivityString + ' as nvarchar(max)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN Activity WITH (NOLOCK) ON [Calendar].EntityId = Activity.ActivityId
Inner JOIN dbo.fnSplit(''' + @CalenderDisplayList + ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 4 AND datediff(' + @dateStr + ',[Calendar].[DueDate],''' + cast(@DueDate as nvarchar(20)) + ''') = 0 AND [StaffId] = ' + cast(@StaffId as nvarchar(10)) + '))
UNION
(SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' + @CaseOffenseString + ' as nvarchar(max)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN CaseOffense WITH (NOLOCK) ON CaseOffense.CaseOffId = [Calendar].EntityId
Inner JOIN dbo.fnSplit(''' + @CalenderDisplayList + ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 5 AND datediff(' + @dateStr + ',[Calendar].[DueDate],''' + cast(@DueDate as nvarchar(20)) + ''') = 0 AND [StaffId] = ' + cast(@StaffId as nvarchar(10)) + ')'
IF NOT @CalenderCaseId IS NULL
BEGIN
SET @str = @str + ' AND [Calendar].CaseId = ''' + @CalenderCaseId + ''''
END
SET @str = @str + ')
UNION
(SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' + @CaseIndividualString + ' as nvarchar(max)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN IndividualMaster WITH (NOLOCK) ON IndividualMaster.IndividualId = [Calendar].EntityId
Inner JOIN dbo.fnSplit(''' + @CalenderDisplayList + ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 6 AND datediff(' + @dateStr + ',[Calendar].[DueDate],''' + cast(@DueDate as nvarchar(20)) + ''') = 0 AND [StaffId] = ' + cast(@StaffId as nvarchar(10)) + ')'
IF NOT @CalenderCaseId IS NULL
BEGIN
SET @str = @str + ' AND [Calendar].CaseId = ''' + @CalenderCaseId + ''''
END
SET @str = @str + ')
开发者_如何学C UNION
(SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' + @CaseInvestigationString + ' as nvarchar(max)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN CaseInvestigation WITH (NOLOCK) ON [Calendar].EntityId = CaseInvestigation.InvestigationId
Inner JOIN dbo.fnSplit(''' + @CalenderDisplayList + ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 7 AND datediff(' + @dateStr + ',[Calendar].[DueDate],''' + cast(@DueDate as nvarchar(20)) + ''') = 0 AND [StaffId] = ' + cast(@StaffId as nvarchar(10)) + ')'
IF NOT @CalenderCaseId IS NULL
BEGIN
SET @str = @str + ' AND [Calendar].CaseId = ''' + @CalenderCaseId + ''''
END
SET @str = @str + ')
UNION
(SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' + @CommunicationLogString + ' as nvarchar(max)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN CommunicationLog WITH (NOLOCK) ON [Calendar].EntityId = CommunicationLog.LogId
Inner JOIN dbo.fnSplit(''' + @CalenderDisplayList + ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 8 AND datediff(' + @dateStr + ',[Calendar].[DueDate],''' + cast(@DueDate as nvarchar(20)) + ''') = 0 AND [StaffId] = ' + cast(@StaffId as nvarchar(10)) + ')'
IF NOT @CalenderCaseId IS NULL
BEGIN
SET @str = @str + ' AND [Calendar].CaseId = ''' + @CalenderCaseId + ''''
END
SET @str = @str + ')
UNION
(SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' + @CrimeSceneString + ' as nvarchar(max)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN Scene WITH (NOLOCK) ON [Calendar].EntityId = Scene.CrimeSceneId
Inner JOIN dbo.fnSplit(''' + @CalenderDisplayList + ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 9 AND datediff(' + @dateStr + ',[Calendar].[DueDate],''' + cast(@DueDate as nvarchar(20)) + ''') = 0 AND [StaffId] = ' + cast(@StaffId as nvarchar(10)) + ')'
IF NOT @CalenderCaseId IS NULL
BEGIN
SET @str = @str + ' AND [Calendar].CaseId = ''' + @CalenderCaseId + ''''
END
SET @str = @str + ')
ORDER BY [Calendar].[DueDate]
'
--select @str
exec (@str)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() as ErrorMessage;
END CATCH
RETURN
END
Stored proc FOR ORACLE
CREATE OR REPLACE PROCEDURE ADMIN.Calendar_GetByDate(DueDate IN TIMESTAMP,
StaffId IN NUMBER,
IsForMonth IN NUMBER,
SubpoennaString IN nvarchar2,
ActivityString IN nvarchar2,
TrainingString IN nvarchar2,
RequestString IN nvarchar2,
OtherString IN nvarchar2,
CaseOffenseString IN nvarchar2,
CaseIndividualString IN nvarchar2,
CaseInvestigationString IN nvarchar2,
CommunicationLogString IN nvarchar2,
CrimeSceneString IN nvarchar2,
CalenderDisplayList IN NVARCHAR2,
CalenderCaseId IN nvarchar2 DEFAULT null, v_refcur OUT SYS_REFCURSOR)
as
dateStr NVARCHAR2(100);
str nvarchar2(32000);
SWV_VarStr varchar2(32000);
str1 varchar2(32000);
BEGIN
BEGIN
If IsForMonth = 1 then
dateStr := 'month';
ELSE
dateStr := 'day';
end if;
str := ' SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' || SubpoennaString || ' as nvarchar2(4000)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN SubPoena_Master WITH (NOLOCK) ON [Calendar].EntityId = SubPoena_Master.SPID
Inner JOIN dbo.fnSplit(''' || CalenderDisplayList || ''', '','') as FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 3 AND datediff(' || dateStr || ',[Calendar].[DueDate],''' || SUBSTR(cast(DueDate as NVARCHAR2),1,20) || ''') = 0 AND StaffId = ' || SUBSTR(cast(StaffId as NVARCHAR2),1,10) || ')) UNION (SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' || ActivityString || 'as nvarchar2(4000)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN Activity WITH (NOLOCK) ON [Calendar].EntityId = Activity.ActivityId
Inner JOIN dbo.fnSplit(''' || CalenderDisplayList || ''','','') AS FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 4 AND datediff(' || dateStr || ',[Calendar].[DueDate],''' || SUBSTR(cast(DueDate as NVARCHAR2),1,20) || ''') = 0 AND StaffId = ' || SUBSTR(cast(StaffId as NVARCHAR2),1,10) || ')) UNION (SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' || CaseOffenseString || 'as nvarchar2(4000)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN CaseOffense WITH (NOLOCK) ON CaseOffense.CaseOffId = [Calendar].EntityId
Inner JOIN dbo.fnSplit(''' || CalenderDisplayList || ''','','') AS FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 5 AND datediff(' || dateStr || ',[Calendar].[DueDate],''' || SUBSTR(cast(DueDate as NVARCHAR2),1,20) || ''') = 0 AND StaffId = ' || SUBSTR(cast(StaffId as NVARCHAR2),1,10) || ')';
IF NOT CalenderCaseId IS NULL then
str := str || ' AND [Calendar].CaseId = ''' || CalenderCaseId || '''';
end if;
str := str || 'UNION (SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' || CaseIndividualString || 'as nvarchar2(4000)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN IndividualMaster WITH (NOLOCK) ON IndividualMaster.IndividualId = [Calendar].EntityId
Inner JOIN dbo.fnSplit(''' || CalenderDisplayList || ''','','') AS FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 6 AND datediff(' || dateStr || '[Calendar].[DueDate],''' || SUBSTR(cast(DueDate as NVARCHAR2),1,20) || ''') = 0 AND StaffId = ' || SUBSTR(cast(StaffId as NVARCHAR2),1,10) || ')';
IF NOT CalenderCaseId IS NULL then
str := str || ' AND [Calendar].CaseId = ''' || CalenderCaseId || '''';
end if;
str := str || 'UNION (SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' || CaseInvestigationString || ' as nvarchar2(4000)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN CaseInvestigation WITH (NOLOCK) ON [Calendar].EntityId = CaseInvestigation.InvestigationId
Inner JOIN dbo.fnSplit(''' || CalenderDisplayList || ''','','') AS FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 7 AND datediff(' || dateStr || ',[Calendar].[DueDate],''' || SUBSTR(cast(DueDate as NVARCHAR2),1,20) || ''') = 0 AND [StaffId] = ' || SUBSTR(cast(StaffId as NVARCHAR2),1,10) || ')';
IF NOT CalenderCaseId IS NULL then
str := str || ' AND [Calendar].CaseId = ''' || CalenderCaseId || '''';
end if;
str1 := 'UNION (SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' || CommunicationLogString || ' as nvarchar2(4000)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN CommunicationLog WITH (NOLOCK) ON [Calendar].EntityId = CommunicationLog.LogId
Inner JOIN dbo.fnSplit(''' || CalenderDisplayList || ''','','') AS FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 8 AND datediff(' || dateStr || ',[Calendar].[DueDate],''' || SUBSTR(cast(DueDate as NVARCHAR2),1,20) || ''') = 0 AND [StaffId] = ' || SUBSTR(cast(StaffId as NVARCHAR2),1,10) || ')';
IF NOT CalenderCaseId IS NULL then
str1 := str1 || ' AND [Calendar].CaseId = ''' || CalenderCaseId || '''';
end if;
str1 := str1 || 'UNION (SELECT [Calendar].[CalendarId], [Calendar].[EntityTypeId],
[Calendar].[EntityId], [Calendar].[StaffId], [Calendar].[CaseId],
[Calendar].[DueDate], [Calendar].[IsActive], [Calendar].[IsCaseCalendar],
[Calendar].[LastUpdatedBy], [Calendar].[LastUpdateDate] , cast(' || CrimeSceneString || ' as nvarchar2(4000)) AS Description
FROM [Calendar] WITH (NOLOCK)
LEFT JOIN Scene WITH (NOLOCK) ON [Calendar].EntityId = Scene.CrimeSceneId
Inner JOIN dbo.fnSplit(''' || CalenderDisplayList || ''','','') AS FN on FN.ItemValue = [Calendar].EntityTypeId
WHERE ([Calendar].EntityTypeId = 9 AND datediff(' || dateStr || ',[Calendar].[DueDate],''' || SUBSTR(cast(DueDate as NVARCHAR2),1,20) || ''') = 0 AND [StaffId] = ' || SUBSTR(cast(StaffId as NVARCHAR2),1,10) || ')';
IF NOT CalenderCaseId IS NULL then
str1 := str1 || ' AND [Calendar].CaseId = ''' || CalenderCaseId || '''';
end if;
str1 := str1 || ') ORDER BY [Calendar].[DueDate]';
--select @str
SWV_VarStr := str || str1;
EXECUTE IMMEDIATE SWV_VarStr;
END;
RETURN;
END;
/
You haven't converted the SQL to Oracle:
- No such function as DATEDIFF in Oracle
- WITH (NOLOCK) isn't valid in Oracle
- [TableName].[ColumnName] syntax isn't valid in Oracle
When you want to migrate TSQL to Oracle, I advise that you first rewrite your SQL code in the most Orcale friendly way.
First observation is, that you declare all string parameters as varchar(max). What would you do, if your code has to run on SQL-Server-2005. Would you chose varchar(8000), nvarchar(4000), text or ntext?
Would varchar(4000) or nvarchar(4000) be aceptable ?
This is not a question of length, but a question of datatypes. Do you need ugly CLOB parameters or can you go with nice varchar2?
Good news: Oracle doesn't care about the length of varchar2 parameters. You don't play the game. We just increased the column size of column_x by 10 ( we don't want to waste storage you know) and now you can adjust these 10 stored procedures using the column.
Second Better remove all square brackets [] from your TSQL, if you depend on them, you surely will have some trouble, when migrating to Oracle.
Third Ask yourself tree times, If you really want to use dynamic sql. After a short glance at your code, I think I would try to rewrite it as direct TSQ, perhaps using some if else logic. You probably ask why. The answer is for the sake of performance. I only mention the keywords: bind variables, context, shared pool. Oracle has a limited resource, called shared pool, which tends to become a bottleneck, when you migrate dynamic sql from sql-server the easy straight forward way. Believe me better tune your SQL not to use dynamic sql than to take care of Oracle's special needs.
Forth Use constructs which are similar in both systems
- analytical functions
- common table expressions (referred to as Subquery Factoring in Oracle documentation)
精彩评论