Convert SQL Server stored procedure to an Oracle stored procedure

Question

Converting my SQL Server stored procedure to an Oracle stored procedure throws some errors:

Error(13,10): PLS-00428: an INTO clause is expected in this SELECT statement

Error(36,13): PLS-00428: an INTO clause is expected in this SELECT statement

Error(59,16): PLS-00428: an INTO clause is expected in this SELECT statement

SQL Server stored procedure:

CREATE PROCEDURE [dbo].[proname]
    @pdate DATE,
    @p1 VARCHAR(10),
    @p2 INT
AS
BEGIN
   SET NOCOUNT ON;

    IF @p1 = 'District'
        SELECT 
            ROW_NUMBER() OVER (ORDER BY DueDateUnformatted ASC) AS id, 
            ActivityName, ActivityKey,
            WorkType, 
            DueDateUnformatted AS DueDate, 
            Priority, ComplianceDate,  AssignmentName,
            COUNT(EventKey) AS Total
        FROM  
            dbo.view1
        WHERE 
            DueDateUnformatted < DATEADD(DAY, 1, pdate) 
            AND parentEventKey IS NULL
            AND inspectiondate IS NULL
            AND DistrictKey = @p2
        GROUP BY
            ActivityName, ActivityKey, WorkType, DueDateUnformatted, 
            Priority, ComplianceDate, AssignmentName

    ELSE IF @p1 = 'Division'
        SELECT 
            ROW_NUMBER() OVER (ORDER BY DueDateUnformatted ASC) AS id, 
            ActivityName, ActivityKey,
            WorkType, 
            DueDateUnformatted AS DueDate, 
            Priority, ComplianceDate,  AssignmentName,
            COUNT(EventKey) AS Total
        FROM  
            dbo.view1
        WHERE 
            DueDateUnformatted < DATEADD(DAY, 1, pdate) 
            AND parentEventKey IS NULL
            AND inspectiondate IS NULL
            AND DistrictKey = @p2
        GROUP BY
            ActivityName, ActivityKey, WorkType, DueDateUnformatted, 
            Priority, ComplianceDate, AssignmentName

    ELSE IF @p1 = 'Company'
        SELECT 
            ROW_NUMBER() OVER (ORDER BY DueDateUnformatted ASC) AS id, 
            ActivityKey,
            WorkType, 
            DueDateUnformatted AS DueDate, 
            Priority, ComplianceDate,  AssignmentName,
            COUNT(EventKey) AS Total
        FROM  
            dbo.view1
        WHERE 
            DueDateUnformatted < DATEADD(DAY, 1, pdate) 
            AND parentEventKey IS NULL
            AND inspectiondate IS NULL
            AND CompanyKey = @p2
        GROUP BY
            ActivityName, ActivityKey, WorkType, DueDateUnformatted, 
            Priority, ComplianceDate, AssignmentName
END

Converted Oracle stored procedure:

CREATE OR REPLACE PROCEDURE procname
                (
                  pdate VARCHAR2,
                  p2 VARCHAR2,
                  p3 NUMBER
                )
                AS
                BEGIN
                   -- SET NOCOUNT ON added to prevent extra result sets from
                   -- interfering with SELECT statements.
                   IF p2 = 'District' THEN
                         SELECT ROW_NUMBER() OVER ( ORDER BY DueDateUnformatted ASC  ) id, 
                                ActivityName ,
                                ActivityKey ,
                                WorkType ,
                                DueDateUnformatted DueDate  ,
                                Priority ,
                                COUNT(EventKey) Total  
                           FROM view1
                           WHERE DueDateUnformatted < (TO_DATE(pdate)+1) --utils.dateadd('DAY', 1, v_end_date)
                                   AND parentEventKey IS NULL
                                   AND inspectiondate IS NULL
                                   AND DistrictKey = p3
                           GROUP BY ActivityName,ActivityKey,WorkType,DueDateUnformatted,Priority;
                   ELSE
                      IF p2 = 'Division' THEN
                            SELECT ROW_NUMBER() OVER ( ORDER BY DueDateUnformatted ASC  ) id  ,
                                   ActivityName ,
                                ActivityKey ,
                                WorkType ,
                                DueDateUnformatted DueDate  ,
                                Priority ,
                                COUNT(EventKey) Total 
                              FROM view1
                              WHERE DueDateUnformatted < (TO_DATE(pdate)+1)
                                      AND parentEventKey IS NULL
                                      AND inspectiondate IS NULL
                                      AND DivisionKey = p3
                              GROUP BY ActivityName,ActivityKey,WorkType,DueDateUnformatted,Priority;
                      ELSE
                         IF p2 = 'Company' THEN
                               SELECT ROW_NUMBER() OVER ( ORDER BY DueDateUnformatted ASC  ) id  ,
                                      ActivityName ,
                                ActivityKey ,
                                WorkType ,
                                DueDateUnformatted DueDate  ,
                                Priority ,
                                COUNT(EventKey) Total 
                                 FROM view1
                                 WHERE DueDateUnformatted < (TO_DATE(pdate)+1)
                                         AND parentEventKey IS NULL
                                         AND inspectiondate IS NULL
                                         AND CompanyKey = p3
                                 GROUP BY ActivityName,ActivityKey,WorkType,DueDateUnformatted,Priority;
                         END IF;
                      END IF;
                   END IF;
                END;

Possible duplicate of an INTO clause is expected in this SELECT statement when working with PLSQL — Ken White

Littlefoot Littlefoot · Accepted Answer · 2018-03-01T06:07:28

You are missing the INTO clause in all your SELECT statements. In order to do that, you should first declare variables which will accept values you select. In my example, I declared two NUMBER datatype variables (the ones that will accept result of ROW_NUMBER and COUNT functions), while the others take datatype of appropriate view columns (that's why I used column%type syntax).

Note that WHERE clause must ensure that each SELECT returns at most one row, otherwise you'll get the TOO-MANY-ROWS (while, if it returns nothing, you'll have to handle NO-DATA-FOUND).

I included the IN keyword for procedure's parameters. It is the default, but - if you actually use it - there's no doubt what you plan to do with them.

The following code should, at least, compile. Though, the whole code is questionable because you do SELECT some values, but do nothing with them later.

CREATE OR REPLACE PROCEDURE procname (pdate IN VARCHAR2, p2 IN VARCHAR2, p3 IN NUMBER)
AS
   l_id             NUMBER;                                -- it is ROW_NUMBER
   l_activityname   view1.activityname%TYPE;
   l_activitykey    view1.activitykey%TYPE;
   l_worktype       view1.worktype%TYPE;
   l_duedate        view1.duedateunformatted%TYPE;
   l_priority       view1.priority%TYPE;
   l_total          NUMBER;                                     -- it is COUNT
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   IF p2 = 'District'
   THEN
        SELECT ROW_NUMBER () OVER (ORDER BY DueDateUnformatted ASC) id,
               ActivityName,
               ActivityKey,
               WorkType,
               DueDateUnformatted DueDate,
               Priority,
               COUNT (EventKey) Total
          INTO l_id,
               l_activityname,
               l_activitykey,
               l_worktype,
               l_duedate,
               l_priority,
               l_total
          FROM view1
         WHERE     DueDateUnformatted < (TO_DATE (pdate) + 1) --utils.dateadd('DAY', 1, v_end_date)
               AND parentEventKey IS NULL
               AND inspectiondate IS NULL
               AND DistrictKey = p3
      GROUP BY ActivityName,
               ActivityKey,
               WorkType,
               DueDateUnformatted,
               Priority;
   ELSE
      IF p2 = 'Division'
      THEN
           SELECT ROW_NUMBER () OVER (ORDER BY DueDateUnformatted ASC) id,
                  ActivityName,
                  ActivityKey,
                  WorkType,
                  DueDateUnformatted DueDate,
                  Priority,
                  COUNT (EventKey) Total
             INTO l_id,
                  l_activityname,
                  l_activitykey,
                  l_worktype,
                  l_duedate,
                  l_priority,
                  l_total
             FROM view1
            WHERE     DueDateUnformatted < (TO_DATE (pdate) + 1)
                  AND parentEventKey IS NULL
                  AND inspectiondate IS NULL
                  AND DivisionKey = p3
         GROUP BY ActivityName,
                  ActivityKey,
                  WorkType,
                  DueDateUnformatted,
                  Priority;
      ELSE
         IF p2 = 'Company'
         THEN
              SELECT ROW_NUMBER () OVER (ORDER BY DueDateUnformatted ASC) id,
                     ActivityName,
                     ActivityKey,
                     WorkType,
                     DueDateUnformatted DueDate,
                     Priority,
                     COUNT (EventKey) Total
                INTO l_id,
                     l_activityname,
                     l_activitykey,
                     l_worktype,
                     l_duedate,
                     l_priority,
                     l_total
                FROM view1
               WHERE     DueDateUnformatted < (TO_DATE (pdate) + 1)
                     AND parentEventKey IS NULL
                     AND inspectiondate IS NULL
                     AND CompanyKey = p3
            GROUP BY ActivityName,
                     ActivityKey,
                     WorkType,
                     DueDateUnformatted,
                     Priority;
         END IF;
      END IF;
   END IF;
END;

Convert SQL Server stored procedure to an Oracle stored procedure

1 Answers