Migrate Sql Server Stored Procedure to Oracle PL/SQL

0
votes

The below stored procedure works for SQL Server (before I made some oracle specific changes). Now this stored procedure must work for Oracle too.

The Oracle Sql Developer which I use complains at the line after the "OPEN CURSOR_ FOR"

Somehow Oracle does not like that I 'group' both select statements and the union and do on this whole result an order by...

What do I wrong? I only know a bit about oracle stored procedures...

create or replace
PROCEDURE GetWorkflowStatusForMatrix(
    p_ApplicationId IN varchar2,    
    CURSOR_ OUT sys_refcursor
)
AS
BEGIN   
    OPEN CURSOR_ FOR
    (
  select ApplicationId || ModuleId || UNIT_ID as StatusKey, UNIT_ID, ApplicationId, ModuleId, Owner, "Level", Action, "Comment", LastModifiedUser, LastModifiedDate 
    from WorkflowStatus where ApplicationId = p_ApplicationId

    union

    select distinct e.ApplicationId + WorkflowId + UnitId as StatusKey, UnitId, e.ApplicationId, WorkflowId, w.Owner, 'Level1', 'Working', EventType, UserId, EventDateTime as LastModifiedDate
    from EventLog e
    join WorkflowStatus w on w.ApplicationId = e.ApplicationId and w.ModuleId = e.WorkflowId and w.UNIT_ID = e.UnitId
    where e.ApplicationId = p_ApplicationId and w.Owner <> '' and w.Action = 'Created'
    )
    order by LastModifiedDate DESC;
END;
2
sql-serveroraclestored-procedures
Are you sure you want to add the Id's in the second section of the query, not concatenate them as you did in the first? - David Aldridge

2 Answers

0
votes

You need to read up on how to Define Cursors in Oracle - this will help you out..

http://www.techonthenet.com/oracle/cursors/declare.php

0
votes
create or replace
PROCEDURE GetWorkflowStatusForMatrix(
    p_ApplicationId IN varchar2,    
    CURSOR_ OUT sys_refcursor
)
AS
BEGIN   
  OPEN CURSOR_ FOR
    select ApplicationId || ModuleId || UNIT_ID as StatusKey, UNIT_ID, ApplicationId, ModuleId, Owner, "Level", Action, "Comment", LastModifiedUser, LastModifiedDate 
  from WorkflowStatus where ApplicationId = p_ApplicationId
  union
  select distinct e.ApplicationId + WorkflowId + UnitId as StatusKey, UnitId, e.ApplicationId, WorkflowId, w.Owner, 'Level1', 'Working', EventType, UserId, EventDateTime as LastModifiedDate
from EventLog e
join WorkflowStatus w on w.ApplicationId = e.ApplicationId and w.ModuleId = e.WorkflowId and w.UNIT_ID = e.UnitId
where e.ApplicationId = p_ApplicationId and w.Owner <> '' and w.Action = 'Created'

order by LastModifiedDate DESC;
END;