pipelined function with cursor parameter oracle

Question

for example if i have this kind of function

function test_pipe(p_source in t_cursor)
return t_tab
pipelined
as --some code goes here

t_cursor is a ref cursor. i know i can call this function like

select * from table(test_pipe(cursor(select 1 from dual)));

but what if i declare cursor in a package and want to pass it as an argument. something like this.

procedure test is
v_ct pls_integer;
cursor main_cur is select 1 from dual;
begin
select count(*) into v_ct from table(test_pipe(main_cur));
--some code
end;

And i get main_cur invalid identifier-- pl/sql:ORA00904 error. How should i code to be able to pass the main_cur as an argument to test_pipe?

Answer 1

cursor main_cur is select 1 from dual;

A cursor is a pointer used to fetch rows from a result set.

So, when you do table(test_pipe(main_cur)), you are not passing a rowsource to the pipelined function. you need to first fetch the rows and then pass the rowsource.

Test Case:

SQL> CREATE or replace TYPE target_table_row
  2  AS
  3    OBJECT
  4    ( EMPNO NUMBER(4) ,
  5      ENAME VARCHAR2(10)
  6      )
  7  /

Type created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> CREATE or replace TYPE target_table_rows
  2  AS
  3    TABLE OF target_table_row;
  4  /

Type created.

SQL>
SQL> sho err
No errors.
SQL>

Pipeline function

SQL> CREATE OR REPLACE FUNCTION pipelined_fx(
  2      p_cursor IN SYS_REFCURSOR)
  3    RETURN target_table_rows PIPELINED PARALLEL_ENABLE(
  4      PARTITION p_cursor BY ANY)
  5  IS
  6  TYPE cursor_ntt
  7  IS
  8    TABLE OF emp%ROWTYPE;
  9    nt_src_data cursor_ntt;
 10  BEGIN
 11    LOOP
 12      FETCH p_cursor BULK COLLECT INTO nt_src_data LIMIT 100;
 13      FOR i IN 1 .. nt_src_data.COUNT
 14      LOOP
 15        PIPE ROW (target_table_row( nt_src_data(i).empno, nt_src_data(i).ename ));
 16      END LOOP;
 17      EXIT
 18    WHEN p_cursor%NOTFOUND;
 19    END LOOP;
 20    CLOSE p_cursor;
 21    RETURN;
 22  END pipelined_fx;
 23  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>

Now, let's test the pipelined function:

SQL> DECLARE
  2    rc SYS_REFCURSOR;
  3    num NUMBER;
  4  BEGIN
  5    OPEN RC FOR SELECT * FROM emp;
  6    SELECT count(*) INTO num FROM TABLE(pipelined_fx(rc));
  7    DBMS_OUTPUT.PUT_LINE( num || ' rows in total.' );
  8  END;
  9  /
14 rows in total.

PL/SQL procedure successfully completed.

SQL>

Answer 2

A cursor expression is equivalent to a ref cursor. An explicit cursor is a different and not interchangeable; you can do some swapping between ref cursors and cursor variables with the dbms_sql package but not with an explicit cursor like this.

The closest I can see to what you seem to want is to have a cursor variable that opens the same query with the `open for syntax:

procedure test is
  v_ct pls_integer;
  main_cur t_cursor;
begin
  open main_cur for select 1 from dual;
  select count(*) into v_ct from table(test_pipe(main_cur));
  close main_cur;
  dbms_output.put_line('Count is: ' || v_ct);
  --some code
end test;

But that isn't quite the same thing, so might not be suitable. I'm not sure why you would want to do anything with an explicit cursor other than loop over it though.

---

Straying into XY territory here as this is nothing to do with what you originally asked, but from comments you seem to want to be able to aggregate the data in the cursor; you could do that with an analytic count instead. As a very simple example, if your cursor query was doing:

select trace, col1
from t42
order by trace, col1;

then you could add another column that counts each trace value:

select trace, col1,
  count(col1) over (partition by trace) as trace_count
from t42
order by trace, col1;

You could then refer to column in your cursor loop. Or if you wanted to only loop over the rows where the count is one, your cursor could use that as a subquery:

select trace, col1
from (
  select trace, col1,
    count(col1) over (partition by trace) as trace_count
  from t42
)
where trace_count = 1
order by trace, col1;

SQL Fiddle demo.