This discussion is archived
13 Replies Latest reply: Feb 17, 2013 7:35 PM by Sg049 RSS

Function to return RefCursor

Sg049 Explorer
Currently Being Moderated
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE     11.2.0.1.0     Production"
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Below code compiled successfully.
  create or replace function emp_info_by_dept(p_deptno in scott.emp.deptno%type)
   return scott.emp%ROWTYPE
   as
   l_rec scott.emp%ROWTYPE;
   BEGIN
   select * INTO l_rec from scott.emp
   where deptno=emp_info_by_dept.p_deptno
   and rownum=1;
   return l_rec;
   end;
How can i call call this function? when i use this this function in select statement like below
  select emp_info_by_dept(10) from dual;

Getting error

"ORA-06553: PLS-801: internal error [55018]
06553. 00000 -  "PLS-%s: %s" "
There are other ways to create a function with return type by using collections and procedures with out put as ref cursor.But i want to know what is wrong with this function?

Can some one please explain me?


Thanks,
Sg
  • 1. Re: Function to return RefCursor
    sb92075 Guru
    Currently Being Moderated
    Sg049 wrote:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    "CORE     11.2.0.1.0     Production"
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production


    Below code compiled successfully.
    create or replace function emp_info_by_dept(p_deptno in scott.emp.deptno%type)
    return scott.emp%ROWTYPE
    as
    l_rec scott.emp%ROWTYPE;
    BEGIN
    select * INTO l_rec from scott.emp
    where deptno=emp_info_by_dept.p_deptno
    and rownum=1;
    return l_rec;
    end;
    How can i call call this function? when i use this this function in select statement like below
    select emp_info_by_dept(10) from dual;
    
    Getting error
    
    "ORA-06553: PLS-801: internal error [55018]
    06553. 00000 -  "PLS-%s: %s" "
    There are other ways to create a function with return type by using collections and procedures with out put as ref cursor.But i want to know what is wrong with this function?

    Can some one please explain me?


    Thanks,
    Sg
    plain SQL (SELECT statement) knows NOTHING about PL/SQL datatypes!
  • 2. Re: Function to return RefCursor
    759013 Newbie
    Currently Being Moderated
    SQL can only use a native function. Your returned function is not native meaning its returned data type is unknown to SQL.

    Edited by: Greenwich on Feb 15, 2013 4:31 PM
  • 3. Re: Function to return RefCursor
    thomaso Journeyer
    Currently Being Moderated
    You should look at Pipelined Table Functions.
    It may be what you are trying to do.

    http://docs.oracle.com/cd/B13789_01/appdev.101/b10800/dcitblfns.htm
    http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

    hth
    T.
  • 4. Re: Function to return RefCursor
    rp0428 Guru
    Currently Being Moderated
    >
    return scott.emp%ROWTYPE
    >
    You are returning a PL/SQL type. You can't use PL/SQL types in a SQL query.

    Use a pipelined function to get the entire result set
    -- 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))
    
    EMPNO     ENAME     JOB     MGR     HIREDATE     SAL     COMM     DEPTNO
    7369     smith     CLERK     7902     12/17/1980     800          20
    7566     jones     MANAGER     7839     4/2/1981     2975          20
    7788     scott     ANALYST     7566     4/19/1987     3000     999     20
    7876     adams     CLERK     7788     5/23/1987     1100     888     20
    7902     ford     ANALYST     7566     12/3/1981     3000     777     20
  • 5. Re: Function to return RefCursor
    Stew Ashton Expert
    Currently Being Moderated
    When the next version of the Oracle database comes out, install it, then put your function in a package and try again...

    Regards, Stew
  • 6. Re: Function to return RefCursor
    Sg049 Explorer
    Currently Being Moderated
    Hi rp,

    We do not need to create a ref cursor and cursor variable to return the emp row type,but now i know the another way of returning the pipelined function. Here is my way
    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)
      );
      
      
      create or replace type emp_tab_ntt is table of emp_scalar_type;
      
      
      create or replace function get_emp_info(p_deptno in emp.deptno%TYPE) 
      return emp_tab_ntt PIPELINED
      as
      l_emp_tab emp_tab_ntt:=emp_tab_ntt();
      begin 
      select emp_scalar_type(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
     bulk collect into l_emp_tab from emp where deptno=p_deptno;
     for i in 1..l_emp_tab.count loop
      pipe row(emp_scalar_type(l_emp_tab(i).EMPNO,l_emp_tab(i).ENAME,l_emp_tab(i).JOB,l_emp_tab(i).MGR,l_emp_tab(i).HIREDATE,l_emp_tab(i).SAL,l_emp_tab(i).COMM,l_emp_tab(i).DEPTNO));
      end loop;
      end;
    Am i populating the collection fully first and then piping the rows? am i making use of PIPELINED function?

    Creating table function is more easy and less code. which function is better if i write a code like this....
       create or replace function get_emp_info_TF(p_deptno in emp.deptno%TYPE) 
      return emp_tab_ntt 
      as
      l_emp_tab emp_tab_ntt:=emp_tab_ntt();
      begin 
      select emp_scalar_type(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
     bulk collect into l_emp_tab from emp where deptno=p_deptno;
     return l_emp_tab;
      end;
    what i don't understand is can't i write the BULK collect and PIPE ROW statement like below?
    select emp_scalar_type(*)
     bulk collect into l_emp_tab from emp where deptno=p_deptno;
    
    pipe row(emp_scalar_type(l_emp_tab(i)));
    can some one clarify me why do i need to use a LOOP for the PIPELINED function to return the row?

    Edited by: Sg049 on Feb 17, 2013 11:14 AM
  • 7. Re: Function to return RefCursor
    Sg049 Explorer
    Currently Being Moderated
    Is next version 12c? Is oracle implementing this feature in next version?
  • 8. Re: Function to return RefCursor
    rp0428 Guru
    Currently Being Moderated
    >
    Am i populating the collection fully first and then piping the rows? am i making use of PIPELINED function?
    >
    Yes - that is a PIPELINED function. But it is not scaleable, especially since you have no LIMIT clause on the BULK COLLECT. You also are using expensive PGA memory to hold the collection and if you are going to pipe the rows there is no need to have and populate a collection.
    >
    Creating table function is more easy and less code. which function is better if i write a code like this....
    >
    Sure - you can create a SQL collection of SQL types and return the collection. But that isn't as efficient of scalable as returning one row at a time like a CURSOR or PIPELINED function would do. And you can't call your function from SQL. A PIPELINED function can be called from SQL just as if it were a table.
    >
    what i don't understand is can't i write the BULK collect and PIPE ROW statement like below?
    select emp_scalar_type(*)
     bulk collect into l_emp_tab from emp where deptno=p_deptno;
     
    pipe row(emp_scalar_type(l_emp_tab(i)));
    can some one clarify me why do i need to use a LOOP for the PIPELINED function to return the row?
    >
    You DON'T need to use a loop in a PIPELINED function to return a row; you use a loop to return mulpitle rows. A PIPELINED function returns results ONE ROW AT A TIME just like a cursor FETCH so if you need to return multiple rows the function needs to PIPE multiple rows.

    You can't use 'l_emp_tab(i)' unless 'i' has a value and your collection has been populated. If you just need the 3rd row you could just
    pipe row(emp_scalar_type(l_emp_tab(3)));
    If you want you can do something like this without a loop.
    . . .
    PIPE ROW () .  . .;
    . . .
    PIPE ROW () .  . .;
    . . .
    PIPE ROW () .  . .;
    . . .
    PIPE ROW () .  . .;
    . . .
    PIPE ROW () .  . .;
  • 9. Re: Function to return RefCursor
    Sg049 Explorer
    Currently Being Moderated
    where can i include LIMIT key word in select * BULK COLLECT... statement?
  • 10. Re: Function to return RefCursor
    rp0428 Guru
    Currently Being Moderated
    >
    where can i include LIMIT key word in select * BULK COLLECT... statement?
    >
    Compare this example to my previous one
    The FETCH does a BULK COLLECT of all data into 'v'. It will either get all the data or none if there isn't any.
    
    The LOOP construct would be used when you have a LIMIT clause so that Oracle would 'loop' back to
    get the next set of records. Run this example in the SCOTT schema and you will see how the LIMIT clause works.
    I have 14 records in my EMP table.
    
    DECLARE
      CURSOR c1 IS (SELECT * FROM emp);
      TYPE typ_tbl IS TABLE OF c1%rowtype;
      v typ_tbl;
    BEGIN
      OPEN c1;
      LOOP                                                 --Loop added
        FETCH c1 BULK COLLECT INTO v LIMIT 3; -- process 3 records at a time
            -- process the first 3 max records
           DBMS_OUTPUT.PUT_LINE('Processing ' || v.COUNT || ' records.');
            FOR i IN v.first..v.last LOOP
                DBMS_OUTPUT.PUT_LINE(v(i).empno);
            END LOOP;  
        EXIT WHEN c1%NOTFOUND; 
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('All done');
    END;
    
    In the FOR loop you would do any processing of the nested table you want to do
    and could use a FORALL to do an INSERT into another table.
  • 11. Re: Function to return RefCursor
    Sg049 Explorer
    Currently Being Moderated
    So i have to use cursor to use the "LIMIT" key word as shown below?
    Declare
    Cursor defenition;
    Begin
    Opening Cursor;
    LOOP
    Fetch Cursor BULK COLLECT into Cursor_Variable LIMIT Number;
    - - - - 
    - - - - 
     -  - --
    end Loop;
    end;
    I can not use LIMIT in select statement correct?

    Thanks for the Help and making me under stand this concept.


    Thanks,
    sg
  • 12. Re: Function to return RefCursor
    rp0428 Guru
    Currently Being Moderated
    >
    So i have to use cursor to use the "LIMIT" key word as shown below?
    . . .
    I can not use LIMIT in select statement correct?
    >
    You can answer that for yourself in you think about it.

    A SELECT statement returns ALL rows of the result set. You can use BULK COLLECT INTO to populate a collection but you will get them ALL.
  • 13. Re: Function to return RefCursor
    Sg049 Explorer
    Currently Being Moderated
    Thanks RP.

    I guess i am some what clear about using BULK COLLECT in collections.

Legend

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