CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Suranga Premakumara
Suranga Premakumara

Reputation: 23

how to write multiple select queries in one procedure body

this is my pl/sql code for oracle 11g XE. how can i fix this i want to run both select queries at one time help me please....they are work as single procedures.

CREATE OR REPLACE PACKAGE myproc
as
  PROCEDURE TestProc (p_recordset OUT SYS_REFCURSOR, lock_s OUT SYS_REFCURSOR);
END myproc;
/

CREATE OR REPLACE PACKAGE BODY myproc
IS
  PROCEDURE TestProc (p_recordset OUT SYS_REFCURSOR, lock_s OUT SYS_REFCURSOR)
  AS
  BEGIN
    OPEN p_recordset FOR
     select a.TABLESPACE_NAME as Tablespace,
            round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) AS Percentages
       from (select TABLESPACE_NAME, sum(BYTES) BYTES
               from sys.dba_data_files
              group by TABLESPACE_NAME) a,
            (select TABLESPACE_NAME, sum(BYTES) BYTES
               from sys.dba_free_space
              group by TABLESPACE_NAME) b
      where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
      order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

     SELECT vh.sid locking_sid,
            vw.sid waiter_sid,
            vs.status status,
            vs.program program_holding,
            vsw.program program_waiting
       FROM v$lock vh, v$lock vw, v$session vs, v$session vsw
      WHERE (vh.id1, vh.id2) IN (SELECT id1, id2
                                   FROM v$lock
                                  WHERE request = 0
                                 INTERSECT
                                 SELECT id1, id2
                                   FROM v$lock WHERE lmode = 0)
        AND vh.id1 = vw.id1
        AND vh.id2 = vw.id2
        AND vh.request = 0
        AND vw.lmode = 0
        AND vh.sid = vs.sid
        AND vw.sid = vsw.sid;
  end;
end;
/

The error message is :-

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY MYPROC:<br/>

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/4     PLS-00428: an INTO clause is expected in this SELECT statement
SQL>

Upvotes: 0

Views: 3129

Answers (2)

Justin Cave
Justin Cave

Reputation: 231851

If you have a SELECT statement in a procedure, you need to do something with the results.

  • If you know that you are fetching exactly one row, you can do a SELECT INTO one or more local variables.
  • If you are selecting multiple rows, you can do a SELECT BULK COLLECT INTO a collection or set of collections that you have defined.
  • You can use the SELECT statement to open a cursor that you either return from your stored procedure (as a SYS_REFCURSOR output parameter) or that you loop through in your procedure.

Incidentally, unless you have some need to be backward compatible with ancient versions of Oracle, the second query is going to be far more self-explanatory if you use the dba_waiters view rather than joining to v$lock twice.

Upvotes: 0

Luke Woodward
Luke Woodward

Reputation: 65054

The error means that you are SELECTing some data, but not doing anything with it. I suspect you want to put this data into your lock_s procedure parameter, which is presently unused. In such case, all you need to do is to add the line

OPEN lock_s FOR

immediately above the second query. (You've managed this with p_recordset for the first one, I'm not sure why you haven't for the second one.)

Upvotes: 1

Related Questions