CODEWITHSUNDEEP
sqloracle-databaseplsql
Natty
Natty

Reputation: 539

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

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.

Upvotes: 3

Views: 10834

Answers (2)

0xdb
0xdb

Reputation: 3697

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

Upvotes: 3

Thomas Carlton
Thomas Carlton

Reputation: 5958

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

Upvotes: 6

Related Questions