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

5 Replies to “Dynamic number of columns”

  1. Laurent, the function works fine. However, how can you possibly use it from PL/SQL? When I tried to open a cursor based on any select that references your table function, it fails with an error: ORA-22905: cannot access rows from a non-nested table item.
    The same error is raised when testing Anton’s pivot function in PL/SQL. I have create a couple of such functions myself and they all fail in PL/SQL.
    Do you have any idea how to make a use of them in PL/SQL?

    If such functions are not supported by PL/SQL engine, then the whole idea is half-baked as practically, nearly all oracle application development is done in PL/SQL.

    Thank you,
    Zahar

  2. DECLARE

    c SYS_REFCURSOR;

    BEGIN

    OPEN c FOR

    SELECT * FROM TABLE( NColPipe.show( ‘test’, 3 ) );

    END;

    PL/SQL: ORA-22905: cannot access rows from a non-nested table item

  3. one could use dynamic sql

    
    DECLARE
       c   SYS_REFCURSOR;
       r1   varchar2(30);
       r2   varchar2(30);
       r3   number;
    BEGIN
       OPEN c FOR 'SELECT * FROM TABLE (NColPipe.show (''test'', 3))';
       FETCH c INTO r1,r2,r3;
    END;
    /

    or

    
    SET SERVEROUT ON
    
    DECLARE
       c         NUMBER;
       col_cnt   NUMBER;
       desc_t    DBMS_SQL.desc_tab;
       namevar   VARCHAR2 (4000);
    BEGIN
       c := DBMS_SQL.open_cursor;
       DBMS_SQL.parse (c,
                       'SELECT * FROM TABLE( NColPipe.show( ''test'', 3 ) )',
                       DBMS_SQL.NATIVE);
    
       DBMS_SQL.describe_columns (c, COL_CNT, DESC_T);
    
       FOR i IN 1 .. col_cnt
       LOOP
          DBMS_SQL.DEFINE_COLUMN (c,
                                  i,
                                  namevar,
                                  4000);
          DBMS_OUTPUT.put_line (desc_t (i).col_name);
       END LOOP;
    
       IF DBMS_SQL.EXECUTE (c) = 0
       THEN
          WHILE (DBMS_SQL.FETCH_ROWS (c) > 0)
          LOOP
             FOR i IN 1 .. col_cnt
             LOOP
                DBMS_SQL.COLUMN_VALUE (c, i, namevar);
                DBMS_OUTPUT.put_line (namevar);
             END LOOP;
          END LOOP;
       END IF;
    
       DBMS_SQL.CLOSE_CURSOR (c);
    END;
    /
    

    
    test1
    test2
    test3
    row: 1
    row: 1
    1
    row: 2
    row: 2
    2
    row: 3
    row: 3
    3
     PL/SQL procedure successfully completed.
    

    maybe you can also work ANYDATASET natively, without unnesting with TABLE(), with the documented functions listed there : anydataset . Not tested.

Leave a Reply

Your email address will not be published.

*