I have created a pipelined table function similar to the well known Anton Scheffer's PIVOT function and it works as expected (in SQL*Plus)
When I tried to use it in PL/SQL to open a cursor or bulk fetch, it fails with the following exception:
PL/SQL: ORA-22905: cannot access rows from a non-nested table itemI tried Anton's function and some others with the same outcome (i.e. exact same error).
You can reproduce it if you create NColPipe function described here:
https://community.oracle.com/message/1299717#1297717 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
However,
DECLARE
c SYS_REFCURSOR;
BEGIN
OPEN c FOR
SELECT * FROM TABLE( NColPipe.show( 'test', 3 ) );
END;--throws exception:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
If PL/SQL cannot process such table functions, what is the practical use of this ODCI table interface?
You can use these in PL/SQL. Open the cursor with dynamic SQL:
set serveroutput on
DECLARE
c sys_refcursor;
type rec is record (
test1 varchar2(100),
test2 varchar2(100),
test3 varchar2(100)
);
v rec;
BEGIN
open c for
'SELECT * FROM TABLE( NColPipe.show( ''test'', 3 ) ) ';
loop
fetch c into v;
exit when c%notfound;
dbms_output.put_line(v.test1 || ' ' || v.test2 || ' ' || v.test3);
end loop;
close c;
END;
/
row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3
But using table functions that can return a varying number of columns is all rather questionable in most situations. Either you've then got to write dynamic SQL to handle the different number of columns, or build if/else branches to fetch the columns.
And it destroys your ability to use bind variables for the parameter(s) determining number of columns:
create or replace procedure p ( cols varchar2 ) as
c sys_refcursor;
type rec3 is record (
test1 varchar2(100),
test2 varchar2(100),
test3 varchar2(100)
);
type rec2 is record (
one varchar2(100),
two varchar2(100)
);
v3 rec3;
v2 rec2;
begin
open c for
'select * from table( ncolpipe.show( :cols, 3 ) ) '
using cols;
loop
if cols = 'two' then
fetch c into v2;
exit when c%notfound;
dbms_output.put_line(v2.one || ' ' || v2.two);
elsif cols = 'test' then
fetch c into v3;
exit when c%notfound;
dbms_output.put_line(v3.test1 || ' ' || v3.test2 || ' ' || v3.test3);
else
exit;
end if;
end loop;
close c;
end p;
/
exec p('test');
row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3
exec p('two');
ORA-00932: inconsistent datatypes: expected - got -
So you've got to build your string manually and sanitize your inputs to avoid SQL injection!
create or replace procedure p ( cols varchar2 ) as
c sys_refcursor;
type rec3 is record (
test1 varchar2(100),
test2 varchar2(100),
test3 varchar2(100)
);
type rec2 is record (
one varchar2(100),
two varchar2(100)
);
v3 rec3;
v2 rec2;
begin
open c for
'select * from table( ncolpipe.show( ' || dbms_assert.enquote_literal (cols ) || ', 3 ) ) ' ;
loop
if cols = 'two' then
fetch c into v2;
exit when c%notfound;
dbms_output.put_line(v2.one || ' ' || v2.two);
elsif cols = 'test' then
fetch c into v3;
exit when c%notfound;
dbms_output.put_line(v3.test1 || ' ' || v3.test2 || ' ' || v3.test3);
else
exit;
end if;
end loop;
close c;
end p;
/
exec p('test');
row: 1 row: 1 1
row: 2 row: 2 2
row: 3 row: 3 3
exec p('two');
1 row: 1
2 row: 2
3 row: 3