This discussion is archived
2 Replies Latest reply: Sep 9, 2013 8:41 AM by rp0428 RSS

Selecting from a function that returns a sys refcursor or an alternative

Mikail Newbie
Currently Being Moderated

I have a query that returns a resultset of three columns, namely SSN,PAID_YEAR and PAID_TOTAL. From this query I can:

  1. Create a view and then query it.
  2. Create a function and return resultset

If I go the first way a simple query like the following takes more than 20 seconds:

SELECT PAID_YEAR,PAID_TOTAL FROM VIEW_1 WHERE SSN=12345678912882;

I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

If I go the second way I can send a parameter and make the engine look only for those rows that match the condition and return the recordset. But I do not know how to then SELECT from that returned resultset. I took a look at pipelined tables but didn't quite get how to benefit them. So my ultimate question is if it's somehow possible to select from the resultset that is returned from a function like this:

SELECT * FROM FUNCTION_1(12132323232).

If yes, then how, if no, what would be an alternative way?

  • 1. Re: Selecting from a function that returns a sys refcursor or an alternative
    Hoek Guru
    Currently Being Moderated

    You can query a pipelined function using the TABLE function.

    Here are some articles that should get you going:

    ORACLE-BASE - Oracle Pipelined Table Functions

    pipelined functions in oracle 9i

    improving performance with pipelined table functions

  • 2. Re: Selecting from a function that returns a sys refcursor or an alternative
    rp0428 Guru
    Currently Being Moderated
    I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

    No - you don't 'know that' because it isn't true. Just check the explain plan for yourself. Oracle can still use any appropriate indexes so that only the rows needed are returned.

    So my ultimate question is if it's somehow possible to select from the resultset that is returned from a function like this:

    SELECT * FROM FUNCTION_1(12132323232).

    No - you can't do it like that. You have to use the TABLE function to treat the function result set as a table:

    'SELECT * FROM TABLE(FUNCTION_1(12132323232)).

    -- type to match emp record
    create or replace type emp_scalar_type as object
      (EMPNO NUMBER(4) ,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7, 2),
       COMM NUMBER(7, 2),
       DEPTNO NUMBER(2)
      )
      /

    -- table of emp records
    create or replace type emp_table_type as table of emp_scalar_type
      /

    -- pipelined function


    create or replace function get_emp( p_deptno in number )
      return emp_table_type
      PIPELINED
      as
       TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
        emp_cv EmpCurTyp;
        l_rec  emp%rowtype;
      begin
        open emp_cv for select * from emp where deptno = p_deptno;
        loop
          fetch emp_cv into l_rec;
          exit when (emp_cv%notfound);
          pipe row( emp_scalar_type( l_rec.empno, LOWER(l_rec.ename),
              l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal, l_rec.comm, l_rec.deptno ) );
        end loop;
        return;
      end;
      /
     
    select * from table(get_emp(20))

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points