Dynamic number of columns

I used to believe you cannot have a dynamic number of columns. Today Tom referenced Anton on asktom.

It leaded me there, back in time, 2006, on the OTN forums
https://forums.oracle.com/forums/message.jspa?messageID=1297717#1297717

Difficult to write an article on this without copy-pasting most of Anton code, so just read it on the link above.

Then you will see the magic :


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> desc NColPipe
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 L_PARM                                             VARCHAR2(10)
 ROWS_REQUESTED                                     NUMBER
 RET_TYPE                                           ANYTYPE
 ROWS_RETURNED                                      NUMBER

METHOD
------
 STATIC FUNCTION ODCITABLEDESCRIBE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RTYPE                          ANYTYPE                 OUT
 P_PARM                         VARCHAR2                IN
 P_ROWS_REQ                     NUMBER                  IN     DEFAULT

METHOD
------
 STATIC FUNCTION ODCITABLEPREPARE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCTX                           NCOLPIPE                OUT
 TI                             ODCITABFUNCINFO         IN
 P_PARM                         VARCHAR2                IN
 P_ROWS_REQ                     NUMBER                  IN     DEFAULT

METHOD
------
 STATIC FUNCTION ODCITABLESTART RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCTX                           NCOLPIPE                IN/OUT
 P_PARM                         VARCHAR2                IN
 P_ROWS_REQ                     NUMBER                  IN     DEFAULT

METHOD
------
 MEMBER FUNCTION ODCITABLEFETCH RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NROWS                          NUMBER                  IN
 OUTSET                         ANYDATASET              OUT

METHOD
------
 MEMBER FUNCTION ODCITABLECLOSE RETURNS NUMBER

The function is returning ANYDATASET and implemeting ODCITABLEDESCRIBE. This is all clean documented code.

Read more:
Data Cartridge Developer’s Guide – Using Pipelined and Parallel Table Functions – Describe Method
Sometimes it is not possible to define the structure of the return type from the table function statically … You can implement a ODCITableDescribe() routine

2 thoughts on “Dynamic number of columns”

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>