This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Feb 26, 2013 5:26 AM by BluShadow RSS

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

122922 Newbie
Currently Being Moderated
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
  • 1. Re: How to pipeline a function with a dynamic number of columns?
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Read a discussion from today :
    Refcursor Vs Pipelined

    Nicolas.
  • 2. Re: How to pipeline a function with a dynamic number of columns?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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 Expert
    Currently Being Moderated
    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
  • 6. Re: How to pipeline a function with a dynamic number of columns?
    32685 Expert
    Currently Being Moderated
    Most impressive! :-)
  • 7. Re: How to pipeline a function with a dynamic number of columns?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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