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

0
votes

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.

2
sqloracleplsql
I guess you're looking for pipelined functions. – Zilog80
Please can you explain in more detail what you mean by "but there seems to be no way of adding logical conditions with them" in relation to Views? Can you give an example of the logical conditions you want to apply? – NickW
Or implicit resultsets: oracle-base.com/articles/12c/implicit-statement-results-12cr1 – Sayan Malakshinov
Or SQL Macros. – William Robertson

2 Answers

3
votes

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));
2
votes

The simplest way is to leave the function as it is and only call it properly:

create table my_table (id, memo) as
    select 1, 'some memo' from dual
/
create or replace function MyTableById (id int) return sys_refcursor is
    rc sys_refcursor;
begin
    open rc for 
        select * from my_table where id=MyTableById.id;
    return rc;
end;
/

var rc refcursor
exec :rc := MyTableById (1);
print rc

        ID MEMO     
---------- ---------
         1 some memo