Convert a SQL Server stored procedure to oracle

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)