Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008

    Unanswered: select from a cursor?

    ok, i'm new to Oracle, much more used to SQL Server. I have to replicate some functionality in Oracle, have been figuring it out as i go and am now a bit stuck on something.

    in our SQL Server environment we have a table function that i select from (as part of a larger proc). i.e.

    select col1
    from dbo.fn_myfunction()
    where col2 = @variable

    in our equivalent oracle environment that table function is actually a function that returns a SYS_REFCURSOR. so, i'm sure this a dumb newbie question but... how do i select from it to get the value i want?

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    You don't SELECT from a ref cursor, you FETCH from it:
       rc SYS_REFCURSOR;
       rc := dbo.fn_myfunction;
          fetch rc into variables
          exit when rc%notfound;
       end loop;
    However, you can build functions that return "tables" that you can select from, like this (fairly pointless) example:
    SQL> create type emp_obj is object (empno number, ename varchar2(10));
      2  /
    Type created.
    SQL> create type emp_tab is table of emp_obj;
      2  /
    Type created.
    SQL> create or replace function all_emps return emp_tab
      2  is
      3     l_emp_tab emp_tab := emp_tab();
      4     n integer := 0;
      5  begin
      6     for r in (select empno, ename from emp)
      7     loop
      8        l_emp_tab.extend;
      9        n := n + 1;
     10       l_emp_tab(n) := emp_obj(r.empno, r.ename);
     11     end loop;
     12     return l_emp_tab;
     13  end;
     14  /
    Function created.
    SQL> select * from table (all_emps);
         EMPNO ENAME
    ---------- ----------
          7369 SMITH
          7499 ALLEN
          7521 WARD
          7566 JONES
          7654 MARTIN
          7698 BLAKE
          7782 CLARK
          7788 SCOTT
          7839 KING
          7844 TURNER
          7902 FORD
          7934 MILLER

  3. #3
    Join Date
    Nov 2003
    Provided Answers: 23
    Have a look at the pipelined table functions (in the PL/SQL Manual)

    If you have one, you can use it like this:

    SELECT * FROM TABLE(myfunc(5));

  4. #4
    Join Date
    Oct 2014
    I think that*this information is*the best.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts