2 Replies Latest reply: Sep 9, 2013 10:41 AM by rp0428 RSS

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

    Mikail

      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?

        • 2. Re: Selecting from a function that returns a sys refcursor or an alternative
          rp0428
          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))