PL/SQL Select After Select Into in Oracle 11g

Question

I'm getting an error when running the query below in PL/SQL. I want to get multiple rows back in the final select statement. The beginning select into statements are to get various reference data Ids to be passed into the where clause of the final select.

declare
   v_statusIdActive NUMBER;
   v_requestTypeId NUMBER;
   v_licenseTypeId NUMBER; 
   v_licenseCategoryId NUMBER;

begin

   select RefStatusId 
   into v_statusIdActive
   from RefStatus
   where 
    StatusNameEn = 'Active'
    and rownum = 1;

   SELECT REQUESTTYPEID, LICENSETYPEID, LICENSECATEGORYID 
   INTO v_requestTypeId, v_licenseTypeId, v_licenseCategoryId
   FROM REQUEST
   WHERE
    REQUESTID = 78
    and rownum = 1;


  select * from FeeRequestMapping frm
    inner join Fee f on frm.FeeId = f.FeeId
  where
    frm.RequestTypeId = v_requestTypeId
    and frm.LicenseTypeId = v_licenseTypeId
    and frm.LicenseCategoryId = v_licenseCategoryId
    and frm.RefStatusId = v_statusIdActive;

  end;

The error is:

Error report - ORA-06550: line 24, column 7: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

What am I doing wrong?

The second SELECT statement doesn't have an INTO clause. Will this second SELECT return one row or multiple rows? — Bob Jarvis - Reinstate Monica
Are you expecting the last select to return a recordset? if so have you setup the proper RefCursor to do it? @BobJarvis you mean 3rd right? — xQbert
The last select statement should return multiple rows. I've edited the original question to give some background on what i'm trying to do. — AshesToAshes

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2016-02-10T16:09:46

Change your code so that the last SELECT is used as a cursor, as in:

declare
   v_statusIdActive NUMBER;
   v_requestTypeId NUMBER;
   v_licenseTypeId NUMBER; 
   v_licenseCategoryId NUMBER;

begin
  select RefStatusId 
    into v_statusIdActive
    from RefStatus
    where StatusNameEn = 'Active' and
          rownum = 1;

  SELECT REQUESTTYPEID, LICENSETYPEID, LICENSECATEGORYID 
    INTO v_requestTypeId, v_licenseTypeId, v_licenseCategoryId
    FROM REQUEST
    WHERE REQUESTID = 78 and
          rownum = 1;

  FOR aRow IN (select *
                 from FeeRequestMapping frm
                 inner join Fee f
                   on frm.FeeId = f.FeeId
                 where frm.RequestTypeId = v_requestTypeId and 
                       frm.LicenseTypeId = v_licenseTypeId and 
                       frm.LicenseCategoryId = v_licenseCategoryId and 
                       frm.RefStatusId = v_statusIdActive)
  LOOP
    NULL;  -- add processing for each 'aRow' returned by the cursor here
  END LOOP;
END;

Best of luck.

PL/SQL Select After Select Into in Oracle 11g

1 Answers