CODEWITHSUNDEEP
oracle-databaseselectplsqlref-cursor
Jirka Meluzin
Jirka Meluzin

Reputation: 320

How to select from refcursor

I'm trying to find better approach for selecting from REFCURSOR. Let's say there is a such function:

CREATE OR REPLACE FUNCTION get_batches_to_export(p_batch_type varchar2 := NULL) 
   RETURN SYS_REFCURSOR
AS
   o_cursor   SYS_REFCURSOR;
BEGIN
   OPEN o_cursor FOR
      SELECT batch_id
        FROM batches
       WHERE batch_type = p_batch_type OR p_batch_type IS NULL;
​
   RETURN o_cursor;
END;

Now I need to select content from this table using requral select (it is limitation of client application - NiFi usign JDBC connection - it supports only scalar types to be returned, deseralization of REFCURSOR fails; also direct select from source table is not possible)

I found this select, it creates XML from REFCURSOR and then extracts values from this XML:

SELECT extractvalue(value(batches_list),'ROW/BATCH_ID') batch_id
FROM table(xmlsequence(get_batches_to_export())) batches_list

Any idea how to avoid the XML part (the xmlsequence, extractvalue and value function calls)? There is also limitation I cannot create any other helper data types or objects - only select can be used.

Upvotes: 0

Views: 1174

Answers (0)

Related Questions