In this article I show simple trick how to add extra filter to already opened cursor in PL/SQL.

When cursor is opened in PL/SQL there is no way back to modify it. You need to execute/fetch data. So how to filter out the data easily for such cursor.

Let’s prepare dummy data. Procedure pro_read_data just opens cursor and returns it.

CREATE TABLE test_tbl
(
  id NUMBER
);

INSERT INTO test_tbl
SELECT level ok FROM DUAL
CONNECT BY LEVEL < 10;

COMMIT;

SELECT * FROM test_tbl;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

CREATE OR REPLACE PROCEDURE pro_read_data
(
  p_cur out SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_cur FOR 'SELECT * FROM test_tbl';
END;
/

Here is example of impossible to do in PL/SQL. Already opened cursor can’t be used with TABLE operator to filter out data.

DECLARE
  l_cur SYS_REFCURSOR;
  l_cnt NUMBER;
BEGIN
  pro_read_data(l_cur);
  
  SELECT count(*)
    INTO l_cnt
    FROM TABLE(l_cur)
   WHERE id=10;
  
  CLOSE l_cur;
END;
/

PL/SQL: ORA-22905: cannot access rows from a non-nested table item

However you can transform already opened cursor to xmltype and then add your extra filter once all data are fetched by xmltype to filter out what you want :o)

set serveroutput on
DECLARE
  l_cur   SYS_REFCURSOR;
  l_cnt   NUMBER;
BEGIN
  pro_read_data(l_cur);
  
  SELECT count(*) 
    INTO l_cnt
    FROM TABLE(xmlsequence(xmltype(l_cur).extract('ROWSET/ROW'))) a
    WHERE extractvalue(value(a), 'ROW/ID')=1;
  
  dbms_output.put_line('Rows: ' || l_cnt);
  
  CLOSE l_cur;
END;
/

Rows: 1

PL/SQL procedure successfully completed.

Have a fun 🙂

  Tomasz