Using CURSOR expression in WITH Clause in query Oracle 12.1

I have a strange issue where the data showing up in Cursor is incorrect. Below is the table and the query. But when I replace the CURSOR (Select * from H) with CURSOR( actual query of H ) , it works

   Tab1              Tab2
Col1  Col2        Col3  Col4
----------        ----------
1     A            1     A
2     B            2     B
3     C            6     F
4     D            7     G
5     E            8     H

WITH  H AS (SELECT * FROM tab1
              WHERE NOT EXISTS (SELECT 1 FROM tab2 WHERE col1 = col3)
             )
       K AS (SELECT count(*) cnt FROM H) 
 SELECT cnt, CURSOR(SELECT * FROM H) result FROM K;

Output of above query give Cnt = 3 and cursor has data as below which is incorrect. But when you just run Select * from H , you get the correct output i.e. 3, 4, 5 rows

Col1  col2
---   ---
 1     A
 2     B

Answer

As per this article Subquery refactoring either uses a global temporary table or an inline view.

In your case, it may be using a global temporary table and I guess the CURSOR keyword does something implicit with the global temporary table.

Try using the INLINE hint and see if it works

WITH  H AS (SELECT  /*+ INLINE */ a.* FROM tab1 a
          WHERE NOT EXISTS (SELECT 1 FROM tab2 WHERE col1 = col3)
         )