Categories
sql

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

By Laurent Schneider

Oracle Certified Master

5 replies on “Dynamic number of columns”

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

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.