Skip to Main Content
  • Questions
  • How to make a use of Pipelined Table functions (that implement ODCI table interface) in PL/SQL?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Zahar.

Asked: August 03, 2017 - 1:49 pm UTC

Last updated: August 04, 2017 - 3:28 pm UTC

Version: 10+

Viewed 1000+ times

You Asked

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 item


I 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?

with LiveSQL Test Case:

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dynamic SQL for ODCI table functions

Zahar Hilkevich, August 04, 2017 - 1:19 pm UTC

Thank you for a very quick and meaningful response, Chris!

I was going to use the table function for joining only. The fetching was meant to be done into a variable with a predefined (constant) structure, so I would not have to use any code branching to figure out which variable to use for fetching. The optional columns were meant to be used only for calculations (expressions).

Loosing capability to bind variables is unfortunate though I could try the "USING" clause.
At the same time, the whole idea was to avoid dynamic sql as with dynamic sql I could produce variable number of columns anyway.
Chris Saxon
August 04, 2017 - 3:28 pm UTC

Thanks. You can use bind variables, you're just going to run into problems if these cause the number of columns the whole statement returns changes.

It sounds like you've found a way to avoid this, so you may be OK.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library