This discussion is archived
5 Replies Latest reply: May 8, 2006 7:52 AM by 32685 RSS

Refcursor Vs Pipelined

495612 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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