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 thoughts on “Dynamic number of columns”

  2. Laurent Schneider Post author

    interesting, probably increases the chance to invalidate object.

    the statement probably cannot be reused without re-parsing, so I would be surprised if no bugs exist 😉

  3. Zahar Hilkevich

    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

  4. Zahar Hilkevich

    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

  5. Laurent Schneider Post author

    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.

Comments are closed.