Oracle Add filter to already opened cursor in PL/SQL

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.