13 Replies Latest reply: Feb 17, 2013 9:35 PM by Sg049 RSS

    Function to return RefCursor

    Sg049
      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
          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
            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
              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
                >
                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
                  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
                    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
                      Is next version 12c? Is oracle implementing this feature in next version?
                      • 8. Re: Function to return RefCursor
                        rp0428
                        >
                        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
                          where can i include LIMIT key word in select * BULK COLLECT... statement?
                          • 10. Re: Function to return RefCursor
                            rp0428
                            >
                            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
                              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
                                >
                                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
                                  Thanks RP.

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