5 Replies Latest reply: May 8, 2006 9:52 AM by 32685 RSS

    Refcursor Vs Pipelined

    495612
      Hi all,

      I write a function and a procedure to access given number of columns from a table
      returning sys_refcursor. I need this function to modiy using pipelined method.
      "How to write this function using pipelined method?"
      
      create or replace function fewcols(n in number,t in varchar2)
      return sys_refcursor is
      str varchar2(1000);
      opt  sys_refcursor;
      begin
      for i in (select column_name from all_tab_cols
                  where table_name = t and column_id<=n) loop
      str:=str || i.column_name || ',';
      end loop;
      open opt for 'select ' || substr(str,1,length(str)-1) ||' from ' || t;
      return opt;
      end;
      /
      
      Function created.
      
      SQL> select fewcols(4,'EMP') from dual;
      
      FEWCOLS(4,'EMP')
      --------------------
      CURSOR STATEMENT : 1
      
      CURSOR STATEMENT : 1
      
        EMPNO ENAME      JOB           MGR
      ------- ---------- --------- -------
         7369 SMITH      CLERK        7902
         7499 ALLEN      SALESMAN     7698
         7521 WARD       SALESMAN     7698
         7566 JONES      MANAGER      7839
         7654 MARTIN     SALESMAN     7698
         7698 BLAKE      MANAGER      7839
         7782 CLARK      MANAGER      7839
         7788 SCOTT      ANALYST      7566
         7839 KING       PRESIDENT
         7844 TURNER     SALESMAN     7698
         7876 ADAMS      CLERK        7788
         7900 JAMES      CLERK        7698
         7902 FORD       ANALYST      7566
         7934 MILLER     CLERK        7782
      
      14 rows selected.
      Thanks in advance...
        • 1. Re: Refcursor Vs Pipelined
          Avinash Tripathi
          Hi Jameel,

          The limitation with the pipelined function is that its return type must be a supported collection type such as nested table , varray or object type. In your case the columns are not fixed because table can be change dynamically. So we can not create any specific object type. Please have a look on this link:

          http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i52932

          One solution could be, making an object which has only one member as varchar and pipe all columns concatenated (like NAME||AGE||SEX INTO str) into a string


          By the way what you are trying to achieve by doing so (If you can directly write a query)?
          Please specify your exact requirement. We might give another solution without using pipelined function.

          Regards

          Message was edited by:
          Avinash Tripathi
          • 2. Re: Refcursor Vs Pipelined
            495612
            Hi avinash,

            I want to write this function using pipelined method. But failed to collect
            rowtype in PIPE ROW( ??rowtype??) rather as u said concatenate columns.

            Is it possible?
            • 3. Re: Refcursor Vs Pipelined
              Avinash Tripathi
              Hi,
              you can do it like that :
                1  create or replace type   DEMO_OBJECT
                2  as object
                3  (
                4     STR VARCHAR2(500)
                5* )
              SQL>/

              Type created.


                1  CREATE OR REPLACE TYPE DEMO_TABLE
                2* AS TABLE OF DEMO_OBJECT
              SQL>/

              Type created.

              "afiedt.buf" 34 lines, 699 characters

                1  CREATE OR REPLACE FUNCTION
                2  DEMO_FUNC(v_no_of_columns NUMBER,v_table_name VARCHAR2)
                3  RETURN  demo_table   PIPELINED
                4  AS
                5  v_str varchar2(500);
                6  str   VARCHAR2(200) :=NULL;
                7  v_cur sys_refcursor;
                8    BEGIN
                9      for i in (select column_name from all_tab_cols
              10              where table_name = v_table_name and column_id<=v_no_of_columns)
              11      loop
              12      str := str || i.column_name || '  ||';
              13       end loop;
              14     str := SUBSTR(str,1,LENGTH(str)-4);
              15  OPEN v_cur for 'SELECT '||str|| ' FROM '||v_table_name ;
              16  LOOP
              17  FETCH v_cur INTO  v_str;
              18  EXIT WHEN v_cur%NOTFOUND;
              19            PIPE ROW (DEMO_OBJECT(v_str));
              20  END LOOP;
              21   RETURN;
              22   EXCEPTION
              23     WHEN OTHERS THEN
              24     DBMS_OUTPUT.PUT_LINE (SQLERRM);
              25     return;
              26* END;
              27  /

              Function created.

              SQL>
              SQL>SELECT * FROM TABLE (DEMO_FUNC(3,'EMP'));

              STR
              --------------------------------------------------------------------------------------
              7369SMITHCLERK
              7499ALLENSALESMAN
              7521WARDSALESMAN
              7566JONESMANAGER
              7654MARKSALESMAN
              7698BLAKEMANAGER
              7782CLARKMANAGER
              7788SCOTTANALYST
              7839KINGPRESIDENT
              7844TURNERSALESMAN
              7876ADAMSCLERK

              STR
              ------------------------------------------------------------------------------------------
              7900JAMESCLERK
              7902FORDANALYST
              7892EDWARDMANAGER
              8560ROSESR EXEC
              7893KENNYSALESMAN
              4125TOMCLERK

              17 rows selected.
              Regards
              • 4. Re: Refcursor Vs Pipelined
                495612
                Thank you Avinash,
                
                Can we collect rowtype attribute in ROW function using pipelined?
                 
                I did the almost same code as you sent, but the result seems in one string.
                Whereas I am expecting this result:
                
                SQL>SELECT * FROM TABLE (DEMO_FUNC(3,'EMP'));
                
                " and expected output would be...."
                
                  EMPNO ENAME      JOB
                ------- ---------- ---------
                   7369 SMITH      CLERK
                   7499 ALLEN      SALESMAN
                   7521 WARD       SALESMAN
                   7566 JONES      MANAGER
                   7654 MARTIN     SALESMAN
                   7698 BLAKE      MANAGER
                   7782 CLARK      MANAGER
                   7788 SCOTT      ANALYST
                   7839 KING       PRESIDENT
                   7844 TURNER     SALESMAN
                   7876 ADAMS      CLERK
                   7900 JAMES      CLERK
                   7902 FORD       ANALYST
                   7934 MILLER     CLERK
                
                14 rows selected.
                Anyways thanks again for your efforts Avinash.
                • 5. Re: Refcursor Vs Pipelined
                  32685
                  Hello Jameel

                  %ROWTYPE is an attribute only available in PL/SQL. A pipelined function has to return a type that is defined at the schema level and so is visible to the SQL engine, as Avinash has pointed out. Pipelined functions are not designed to be dynamic in this way. You can control the content of the result set but not the format.

                  If you want to generate the result set you are looking for there, and you need both the format and the content to be dynamic, you will need to return a ref cursor rather than using a pipelined function. You have to use the right tool for the right job.

                  HTH

                  David