Using a query in a loop in Oracle SQL-CodePudding

I need to create a seemingly simple procedure in Oracle SQL, yet I struggle to do it. It has to run the same query over and over again in an infinite loop. And the results of that query should be printed out. I used sys_refcursor for this and wrote this procedure:

CREATE OR REPLACE PROCEDURE writer
AS
    rc sys_refcursor;
BEGIN
    LOOP
        open rc for 'select (select username from v$session where sid=a.sid) blocker, a.sid, (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2';
        dbms_sql.return_result(rc);
        close rc;
        SYS.dbms_session.sleep(1);
    END LOOP;
END;

Which is incorrect, because as I learned you can't open the same cursor several times. So how should I approach this? Is there a way to do it with cursor, or maybe some other method?

CodePudding user response：

I don't think you need a refcursor; an ordinary procedure would do.

First, create it:

SQL> create or replace procedure writer as
  2  begin
  3    for cur_r in (
  4                  select (select username from v$session where sid=a.sid) blocker,
  5                          a.sid sid_a,
  6                         (select username from v$session where sid=b.sid) blockee,
  7                          b.sid sid_b
  8                  from v$lock a, v$lock b
  9                  where a.block = 1
 10                    and b.request > 0
 11                    and a.id1 = b.id1
 12                    and a.id2 = b.id2
 13                  )
 14    loop
 15      dbms_output.put_line(cur_r.blocker ||' - '|| cur_r.sid_a ||' - '||
 16                           cur_r.blockee ||' - '|| cur_r.sid_b);
 17    end loop;
 18  end;
 19  /

Procedure created.

Does it work?

SQL> exec writer

PL/SQL procedure successfully completed.

Well ... yes; though, query doesn't return anything in my database.

Finally, an infinite loop:

SQL> begin
  2    loop
  3      writer;
  4    end loop;
  5  end;
  6  /
^C

(it was running until I terminated it with Ctrl C).