Using a query in a loop in Oracle SQL

Question

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?

Littlefoot · Accepted Answer · 2021-11-14 20:39:35Z

2

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).

answered Nov 14, 2021 at 20:39

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vladislav Over a year ago

Thanks for the solution. However I tried this code on a finite loop and it prints out the values only when the procedure is finished. Is this the only way it works in Oracle?

Littlefoot Over a year ago

That's how DBMS_OUTPUT.PUT_LINE works. If you want to "trace" procedure's execution, change it so that it inserts result of the query into a separate table, and then query that table.

Collectives™ on Stack Overflow

Using a query in a loop in Oracle SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related