How can I return a SELECT query with an oracle function?

Question

I need to create a function that allows me to return the same result as a SELECT query and that contains pl/sql code.

I tried something really simple :

create or replace FUNCTION test
  RETURN SYS_REFCURSOR
IS
  l_rc SYS_REFCURSOR;
BEGIN
  OPEN l_rc
   FOR SELECT *
         FROM my_table;
  RETURN l_rc;
END;

But when I call my function with SELECT test from dual;, I get all result from my_table in a single cell instead of having each columns separated.

Is there a way of doing what I want ?

Ideally, I want a view but there seems to be no way of adding logical conditions with them.

Answer 1

the function has to be pipelined. For example :

    TYPE MyType IS RECORD(ID NUMBER);   
    TYPE MyTableType IS TABLE OF MyType;       
    
    Function MyFunction(Arguments) return MyTableType pipelined is     
        Cursor Cur is select * from whetever;
        R Cur%rowtype;
    Begin
        Open cur;
        loop
            fetch Cur into R;
            exit when Cur%notfound;
            pipe row(R);
        End loop;
        Close cur;
    End MyFunction;

Then you can call it via :

    select * from table(MyFunction(Arguments));