1 2 Previous Next 27 Replies Latest reply: Feb 26, 2013 7:26 AM by BluShadow RSS

    How to pipeline a function with a dynamic number of columns?

    122922
      Hi everyone,

      I'm trying to figure out how to write a piplined function that generates a dynamic SQL statement from its inputs, executes the query, and returns the results of the query in the pipeline. The number and names of the columns in the dynamic query is unknown number until the function is invoked.

      I suspect that DBMS_SQL is involved, but can't quite figure out how to construct a row using it that I can use PIPE ROW on. I also can't figure out what data type the function should return (ANYDATASET?)

      pseudo-PLSQL follows:

      create function myfunction ( param1 varchar2)
      return anydataset pipelined
      as
      query_string := <... do stuff with param1 ...>

      < -- >
      open a cursor for query_string
      determine the number of columns
      read a row
      PIPE ROW it
      <--->


      Can what I'm trying to do be done?

      Thanks,

      Keith
        • 2. Re: How to pipeline a function with a dynamic number of columns?
          Billy~Verreynne
          This sounds very weird.. why would you want to use a pipeline table function as interface between dynamic SQL and the output it returns? It makes little sense performance wise to do this (introducing a lot of additional PL/SQL context switching and overheads) - never mind that it is not possible for a pipeline table to return rows with "dynamic cols".

          A pipeline does not support a RTTI interface.

          The usual and correct methods to deal with dynamic SQL and RTTI interfaces are:
          - from a client, using REF CURSORS
          - from within PL/SQL, using DBMS_SQL

          Pipeline tables are a very neat feature in Oracle. But I've found that a large majority of times when developers are using it, they are using it for the wrong reasons. The biggest advantage of a pipeline table function are:
          - the ability to "multithread" PL/SQL cursor processing loops using PQ (making PL/SQL cursor loops very scalable)
          - the ability to inject PL/SQL logic into a system without changing client code (the client still queries via SQL a view that sits ontop of PL/SQL code)

          If these do not feature in the reason why you are using a pipeline table function, think again. And make sure that your reasons are valid.
          • 3. Re: How to pipeline a function with a dynamic number of columns?
            ascheffer
            I don't know what a RTTI interface means, but it is possible get a dynamic number of columns with a pipelined function using the "interface method", see http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dcitblfns.htm#i1005159
            SQL> select * from table( NColPipe.show( 'test' ) );

            test1      test2      test3
            ---------- ---------- ----------
            row: 0     row: 0     row: 0
            row: 1     row: 1     row: 1

            SQL> select * from table( NColPipe.show( 'one' ) );

            one
            ----------
            0
            1

            SQL> select * from table( NColPipe.show( 'two' ) );

            one        two
            ---------- ----------
            0          row: 0
            1          row: 1

            SQL>
            • 4. Re: How to pipeline a function with a dynamic number of columns?
              Billy~Verreynne
              > I don't know what a RTTI interface means,

              RTTI interface means Run Time Type Information interface. Common term when dealing with objects (classes) and determining what properties they have at run-time.

              Within the database context, that usually means getting a cursor pointer (or handle) and then dynamically determine at run-time the contents of that cursor. How many columns? Names of columns? Data types of columns? Etc.

              Within the PL/SQL context, this is done via the DBMS_SQL API.

              > but it is
              possible get a dynamic number of columns with a
              pipelined function using the "interface method", see
              http://download-uk.oracle.com/docs/cd/B19306_01/appdev
              .102/b14289/dcitblfns.htm#i1005159

              That is not how I read it...

              A pipeline table function returns a collection of a SQL data type. That type must be known at parsing and compilation type. That type cannot be created and specified at run-time. The type must exist upfront at compilation time.

              What is dynamic in this aspect is rerturning sub-classes of the a parent class, where the pipeline table function return instantiated children of a parent class.

              E.g. the abstract parent data type called TAnimal exist and have been subclassed into the abstract type TMammel which in turn has a finalised subclass call THorse.

              The pipeline table function is coded to return a collection of TAnimals (abstract class). At runtime it can however return any finalised class such a THorse - which may not even have existed (as a definition in the database) at the time the pipeline table function was coded and compiled.

              If you differ on this, please provide concrete examples.

              >
              SQL> select * from table( NColPipe.show( 'test' ) );

              test1 test2 test3
              ---------- ---------- ----------
              row: 0 row: 0 row: 0
              row: 1 row: 1 row: 1

              SQL> select * from table( NColPipe.show( 'one' ) );

              one
              ----------
              0
              1

              SQL> select * from table( NColPipe.show( 'two' ) );

              one two
              ---------- ----------
              0 row: 0
              1 row: 1

              SQL>

              Can you backup this example with the actual source code use to run it?
              • 5. Re: How to pipeline a function with a dynamic number of columns?
                ascheffer
                The following works on 10R2
                create or replace type NColPipe as object 
                (
                  l_parm varchar2(10),   -- The parameter given to the table function
                  rows_requested number, -- The parameter given to the table function
                  ret_type anytype,      -- The return type of the table function
                  rows_returned number,  -- The number of rows currently returned by the table function
                  static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 2 )
                  return number,
                  static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 2 )
                  return number,
                  static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
                  return number,
                  member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
                  return number,
                  member function ODCITableClose( self in NColPipe )
                  return number,
                  static function show( p_parm in varchar2, p_rows_req in number := 2 )
                  return anydataset pipelined using NColPipe
                );
                /

                create or replace type body NColPipe as
                  static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 2 )
                  return number
                  is
                    atyp anytype;
                  begin
                    anytype.begincreate( dbms_types.typecode_object, atyp );
                    if p_parm = 'one'
                    then
                      atyp.addattr( 'one'
                                  , dbms_types.typecode_varchar2
                                  , null
                                  , null
                                  , 10
                                  , null
                                  , null
                                  );
                    elsif p_parm = 'two'
                    then
                      atyp.addattr( 'one'
                                  , dbms_types.typecode_varchar2
                                  , null
                                  , null
                                  , 10
                                  , null
                                  , null
                                  );
                      atyp.addattr( 'two'
                                  , dbms_types.typecode_varchar2
                                  , null
                                  , null
                                  , 10
                                  , null
                                  , null
                                  );
                    else
                      atyp.addattr( p_parm || '1'
                                  , dbms_types.typecode_varchar2
                                  , null
                                  , null
                                  , 10
                                  , null
                                  , null
                                  );
                      atyp.addattr( p_parm || '2'
                                  , dbms_types.typecode_varchar2
                                  , null
                                  , null
                                  , 10
                                  , null
                                  , null
                                  );
                      atyp.addattr( p_parm || '3'
                                  , dbms_types.typecode_number
                                  , 10
                                  , 0
                                  , null
                                  , null
                                  , null
                                  );
                    end if;
                    atyp.endcreate;
                    anytype.begincreate( dbms_types.typecode_table, rtype );
                    rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
                    rtype.endcreate();
                    return odciconst.success;
                  exception
                    when others then
                      return odciconst.error;
                  end;  
                --
                  static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 2 )
                  return number
                  is
                    elem_typ sys.anytype;
                    prec pls_integer;
                    scale pls_integer;
                    len pls_integer;
                    csid pls_integer;
                    csfrm pls_integer;
                    tc pls_integer;
                    aname varchar2(30);
                  begin
                    tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
                    sctx := NColPipe( p_parm, p_rows_req, elem_typ, 0 );
                    return odciconst.success;
                  end;
                --
                  static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
                  return number
                  is
                  begin
                    return odciconst.success;
                  end;
                --
                  member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
                  return number
                  is
                  begin
                    anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
                    for i in self.rows_returned + 1 .. self.rows_requested
                    loop
                      outset.addinstance;
                      outset.piecewise();
                      if self.l_parm = 'one'
                      then
                        outset.setvarchar2( to_char( i ) );
                      elsif self.l_parm = 'two'
                      then
                        outset.setvarchar2( to_char( i ) );
                        outset.setvarchar2( 'row: ' || to_char( i ) );
                      else
                        outset.setvarchar2( 'row: ' || to_char( i ) );
                        outset.setvarchar2( 'row: ' || to_char( i ) );
                        outset.setnumber( i );
                      end if;
                      self.rows_returned := self.rows_returned + 1;
                    end loop;
                    outset.endcreate;
                    return odciconst.success;
                  end;
                -- 
                  member function ODCITableClose( self in NColPipe )
                  return number
                  is
                  begin
                    return odciconst.success;
                  end;
                end;
                /
                And to use it
                SQL> select * from table( NColPipe.show( 'test', 3 ) );

                test1      test2           test3
                ---------- ---------- ----------
                row: 1     row: 1              1
                row: 2     row: 2              2
                row: 3     row: 3              3

                SQL>  select * from table( NColPipe.show( 'two', 5 ) );

                one        two
                ---------- ----------
                1          row: 1
                2          row: 2
                3          row: 3
                4          row: 4
                5          row: 5

                SQL> select * from table( NColPipe.show( 'one' ) );

                one
                ----------
                1
                2

                SQL>
                Anton
                • 7. Re: How to pipeline a function with a dynamic number of columns?
                  Billy~Verreynne
                  Fricken excellent! I rate this the best new stuff I've read here in a very long time. Thanks. I love being educated on new things in Oracle.

                  Those static functions are the actual RTTI interface I was refering too. I had no idea Oracle exposed those now. Hmm.. this opens all kinds of interesting possibilities... :-)
                  • 8. Re: How to pipeline a function with a dynamic number of columns?
                    122922
                    Interesting stuff! It's going to take me awhile to digest it.

                    For what it's worth, I was trying to build a pivoting function that would take a MYTABLE table like this:
                     YEAR CITY         X          Y
                    ----- --- ---------- ----------
                     2000 BAL         95         96
                     2000 BOS        101        101
                     2001 BAL         92         94
                     2001 BOS        101        101
                     2002 BAL         98         98
                     2002 BOS         98         99
                     2003 BAL         95         96
                     2003 BOS        105        104
                    and allow me to do something like:
                    CREATE VIEW MYPIVOT 
                    AS
                    SELECT * 
                    FROM TABLE (PIVOT(MYTABLE, [with other params]))
                    and get the following view MYPIVOT on the table:
                    YEAR    BOS_X    BOS_Y    BAL_X    BAL_Y
                    ----  -------  -------  -------  -------
                    2000      101      101       95       96
                    2001      101      101       92       94
                    2002       98       99       98       98
                    2003      105      104       95       96
                    Where the number of distinct CITY values will vary over time. I am able to build the query I need dynamically, but since the CITY data values in the original table change, the columns are not necessarily static from invocation to invocation. Therefore I didn't want to just create a view using the dynamic SQL once, because it may need to be created next time I need to access the view. I wanted to be able to access the pivoted data on demand.

                    I couldn't do was be able to execute the query and treat it as a pipelined function, hence my original question. I'll dig into the code above to see if it does what I wanted, but if someone has a better suggestion on how to approach this, I'd be interested in hearing it.
                    • 9. Re: How to pipeline a function with a dynamic number of columns?
                      Billy~Verreynne
                      Still do not understand why you simply use a REF CURSOR. It has a RTTI interface already. You do not have to code such a RTTI interface manually in PL/SQL. Simply pass the ref cursor to the client and it can call the describe API to get the layout of the cursor (if need be). Most clients have query rendering classes that can automatically render that ref cursor contents as a grid display without any additional programming interaction (besides you setting the grid object's display properties like fonts and colours and so on).

                      In PL/SQL, you can do the same thing using DBMS_SQL. Again, the RTTI is there. No need to re-invent the wheel. You get the names of the columns (plus data types) and can then fetch rows and render them (e.g. using the HTP package to render it as HTML or UTL_FILE to write it to CSV, etc.)

                      In both cases you create a fully dynamic SQL statement (with bind variables hopefully). You execute it. As simple as that.

                      Pipeline functions are ideal for doing data transformations too complex to do in SQL (among other uses). But where is the data transformation in a pivot? The complexity in a pivot is constructing the SQL. Once that is done, the SQL itself performs the pivot without any additional intervention needed. So I fail to see why you would want to do that by using a pipeline table function, or what a pipeline table function buys you that is more effective (and faster) than either a REF CURSOR or DBMS_SQL.

                      And if the client software cannot deal with a ref cursor, then it is IMO seriously flawed - passing cursor handles to client software is the norm in database client-server.

                      Remember that pipeline table functions usually have cursor fetch loops. Which means a lot of context switching between PL/SQL and SQL - thus a performance impact. Compare that to native SQL and the latter is by default a lot faster.
                      • 10. Re: How to pipeline a function with a dynamic number of columns?
                        27876
                        if XML output to the client is acceptable, you could do something like below. You can convert any query into its corresponding XML, and even apply XSLT to convert to other desired formats.

                        10GR2 also takes any arbitrary REF CURSOR as input and converts it into an XML that you could use if you have a ref cursor to start with.
                        SQL> CREATE OR REPLACE TYPE clob_table IS TABLE OF CLOB ;
                          2  /

                        Type created.

                        SQL> CREATE OR REPLACE FUNCTION dyn_rows(p_query IN VARCHAR2) RETURN clob_table PIPELINED IS
                          2      l_xml XMLTYPE ;
                          3  BEGIN
                          4      l_xml := XMLTYPE(dbms_xmlgen.getxml(p_query)) ;
                          5      FOR rec IN (SELECT *
                          6                  FROM   TABLE(xmlsequence(l_xml.extract('/ROWSET/ROW'))))
                          7      LOOP
                          8          PIPE ROW (rec.column_value.getCLOBVal()) ;
                          9      END LOOP;
                        10  END;
                        11  /

                        Function created.

                        SQL> SHOW ERRORS
                        No errors.
                        SQL> SELECT * FROM TABLE(dyn_rows('select * from emp')) ;

                        COLUMN_VALUE
                        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        <ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>17-DEC-1980</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW>
                        <ROW><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>20-FEB-1981</HIREDATE><SAL>1600</SAL><COMM>300</COMM><DEPTNO>30</DEPTNO></ROW>
                        <ROW><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>22-FEB-1981</HIREDATE><SAL>1250</SAL><COMM>500</COMM><DEPTNO>30</DEPTNO></ROW>
                        <ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO></ROW>
                        <ROW><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>28-SEP-1981</HIREDATE><SAL>1250</SAL><COMM>1400</COMM><DEPTNO>30</DEPTNO></ROW>
                        <ROW><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>01-MAY-1981</HIREDATE><SAL>2850</SAL><DEPTNO>30</DEPTNO></ROW>
                        <ROW><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>09-JUN-1981</HIREDATE><SAL>2450</SAL><DEPTNO>10</DEPTNO></ROW>
                        <ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO></ROW>
                        <ROW><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><HIREDATE>17-NOV-1981</HIREDATE><SAL>5000</SAL><DEPTNO>10</DEPTNO></ROW>
                        <ROW><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>08-SEP-1981</HIREDATE><SAL>1500</SAL><COMM>0</COMM><DEPTNO>30</DEPTNO></ROW>
                        <ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO></ROW>
                        <ROW><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><MGR>7698</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>950</SAL><DEPTNO>30</DEPTNO></ROW>
                        <ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO></ROW>
                        <ROW><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIREDATE>23-JAN-1982</HIREDATE><SAL>1300</SAL><DEPTNO>10</DEPTNO></ROW>

                        14 rows selected.

                        SQL> disconnect
                        Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                        With the Partitioning, OLAP and Data Mining options
                        SQL>
                        • 11. Re: How to pipeline a function with a dynamic number of columns?
                          ascheffer
                          See the Amis Technology blog, Pivot dynamic data with pipelined function and Dynamic SQL Pivoting - Stealing Anton's Thunder for some examples of how to use pipelined functions for pivoting.
                          <br>
                          Anton
                          • 12. Re: How to pipeline a function with a dynamic number of columns?
                            431127
                            Billy Verreynne wrote:
                            In PL/SQL, you can do the same thing using DBMS_SQL. Again, the RTTI is there. No need to re-invent the wheel. You get the names of the columns (plus data types) and can then fetch rows and render them (e.g. using the HTP package to render it as HTML or UTL_FILE to write it to CSV, etc.)
                            Hi Billy,

                            I stumbled across this post and I'm wondering if you could please explain this further, or point me where to look? We have the need to create a query for the last 52 weeks worth of data and output it to a csv. Naturally, the headers and data are going to be changing every week. I have gotten the query itself created, and I"m now to the point of how do i write this to a file if I do not know what my headers are going to be every time? (i.e. how to 'get the name of the columns' as you put it.)

                            Thanks!
                            Janel
                            • 13. Re: How to pipeline a function with a dynamic number of columns?
                              BluShadow
                              anasazii wrote:
                              Billy Verreynne wrote:
                              In PL/SQL, you can do the same thing using DBMS_SQL. Again, the RTTI is there. No need to re-invent the wheel. You get the names of the columns (plus data types) and can then fetch rows and render them (e.g. using the HTP package to render it as HTML or UTL_FILE to write it to CSV, etc.)
                              Hi Billy,

                              I stumbled across this post and I'm wondering if you could please explain this further, or point me where to look? We have the need to create a query for the last 52 weeks worth of data and output it to a csv. Naturally, the headers and data are going to be changing every week. I have gotten the query itself created, and I"m now to the point of how do i write this to a file if I do not know what my headers are going to be every time? (i.e. how to 'get the name of the columns' as you put it.)

                              Thanks!
                              Janel
                              Hi Janel,

                              Billy is unlikely to reply as he's not been around for several months (although we'd all love for him to come back; well most of us would ;) ).

                              With the DBMS_SQL package you can dynamically build up a query string to whatever your requirements are, naming the columns as you wish. The DBMS_SQL package will allow you to programmatically describe the query to obtain the column names and pull out the values as you fetch data.

                              Here's a simple example:

                              [DBMS_SQL Package|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#sthref6136]

                              {code}
                              SQL> ed
                              Wrote file afiedt.buf

                              1 CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2) IS
                              2 v_finaltxt VARCHAR2(4000);
                              3 v_val VARCHAR2(2000);
                              4 v_ret NUMBER;
                              5 c NUMBER;
                              6 d NUMBER;
                              7 col_cnt INTEGER;
                              8 f BOOLEAN;
                              9 rec_tab DBMS_SQL.DESC_TAB;
                              10 col_num NUMBER;
                              11 BEGIN
                              12 c := DBMS_SQL.OPEN_CURSOR;
                              13 DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
                              14 d := DBMS_SQL.EXECUTE(c);
                              15 DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
                              16 FOR j in 1..col_cnt
                              17 LOOP
                              18 DBMS_SQL.DEFINE_COLUMN(c,j,v_val,2000);
                              19 END LOOP;
                              20 FOR j in 1..col_cnt
                              21 LOOP
                              22 DBMS_SQL.COLUMN_VALUE(c,j,v_val);
                              23 v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
                              24 END LOOP;
                              25 DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                              26 LOOP
                              27 v_ret := DBMS_SQL.FETCH_ROWS(c);
                              28 EXIT WHEN v_ret = 0;
                              29 v_finaltxt := NULL;
                              30 FOR j in 1..col_cnt
                              31 LOOP
                              32 DBMS_SQL.COLUMN_VALUE(c,j,v_val);
                              33 v_finaltxt := ltrim(v_finaltxt||','||v_val,',');
                              34 END LOOP;
                              35 DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                              36 END LOOP;
                              37 DBMS_SQL.CLOSE_CURSOR(c);
                              38* END;
                              SQL> /

                              Procedure created.

                              SQL> set serverout on
                              SQL> exec run_query('select * from emp');
                              empno,ename,job,mgr,hiredate,sal,comm,deptno
                              7369,SMITH,CLERK,7902,17-DEC-80,800,,20
                              7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
                              7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
                              7566,JONES,MANAGER,7839,02-APR-81,2975,,20
                              7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
                              7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
                              7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
                              7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
                              7839,KING,PRESIDENT,,17-NOV-81,5000,,10
                              7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
                              7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
                              7900,JAMES,CLERK,7698,03-DEC-81,950,,30
                              7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
                              7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

                              PL/SQL procedure successfully completed.

                              SQL> exec run_query('select * from dept');
                              deptno,dname,loc
                              10,ACCOUNTING,NEW YORK
                              20,RESEARCH,DALLAS
                              30,SALES,CHICAGO
                              40,OPERATIONS,BOSTON

                              PL/SQL procedure successfully completed.

                              SQL>
                              {code}

                              Obviously you could add a little more internal processing to deal with the different datatypes and output them appropriately e.g. VARCHAR2 types could have double quotes put around them and DATE types could be formatted appropriately with a required mask etc.
                              • 14. Re: How to pipeline a function with a dynamic number of columns?
                                BluShadow
                                Here's a slightly more detailed example, taking account of the basic data types:
                                SQL> ed
                                Wrote file afiedt.buf
                                
                                  1  CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2) IS
                                  2    v_finaltxt  VARCHAR2(4000);
                                  3    v_v_val     VARCHAR2(4000);
                                  4    v_n_val     NUMBER;
                                  5    v_d_val     DATE;
                                  6    v_ret       NUMBER;
                                  7    c           NUMBER;
                                  8    d           NUMBER;
                                  9    col_cnt     INTEGER;
                                 10    f           BOOLEAN;
                                 11    rec_tab     DBMS_SQL.DESC_TAB;
                                 12    col_num     NUMBER;
                                 13  BEGIN
                                 14    c := DBMS_SQL.OPEN_CURSOR;
                                 15    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
                                 16    d := DBMS_SQL.EXECUTE(c);
                                 17    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
                                 18    FOR j in 1..col_cnt
                                 19    LOOP
                                 20      CASE rec_tab(j).col_type
                                 21        WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                                 22        WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
                                 23        WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
                                 24      ELSE
                                 25        DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                                 26      END CASE;
                                 27    END LOOP;
                                 28    FOR j in 1..col_cnt
                                 29    LOOP
                                 30      v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
                                 31    END LOOP;
                                 32    DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                                 33    LOOP
                                 34      v_ret := DBMS_SQL.FETCH_ROWS(c);
                                 35      EXIT WHEN v_ret = 0;
                                 36      v_finaltxt := NULL;
                                 37      FOR j in 1..col_cnt
                                 38      LOOP
                                 39        CASE rec_tab(j).col_type
                                 40          WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                                 41                      v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                                 42          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                                 43                      v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
                                 44          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                                 45                      v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
                                 46        ELSE
                                 47          v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                                 48        END CASE;
                                 49      END LOOP;
                                 50      DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                                 51    END LOOP;
                                 52    DBMS_SQL.CLOSE_CURSOR(c);
                                 53* END;
                                SQL> /
                                
                                Procedure created.
                                
                                SQL> exec run_query('select * from emp');
                                empno,ename,job,mgr,hiredate,sal,comm,deptno
                                7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
                                7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
                                7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
                                7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
                                7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
                                7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
                                7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
                                7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
                                7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
                                7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
                                7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
                                7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
                                7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
                                7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10
                                
                                PL/SQL procedure successfully completed.
                                
                                SQL> exec run_query('select * from dept');
                                deptno,dname,loc
                                10,"ACCOUNTING","NEW YORK"
                                20,"RESEARCH","DALLAS"
                                30,"SALES","CHICAGO"
                                40,"OPERATIONS","BOSTON"
                                
                                PL/SQL procedure successfully completed.
                                
                                SQL>
                                1 2 Previous Next