ORA-22905 When Trying To Create A View Object With Bind Variables On A Function Table

(Doc ID 743245.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle JDeveloper - Version and later
Information in this document applies to any platform.
Internal Only : Reviewed for Currency : 09-Jan-2013
Internal Only : Reviewed for Currency : 06-Aug-2014
Checked the relevance on 26-Jan-2016


You created a function table query as the following in Oracle 10g:

create or replace type emp_type as object
empno number(4),
Ename varchar2(100),
sal number(7,2)

create or replace type emp_type_table as table of emp_type ;

create or replace function getEmp (sal_var number, total_sal_v number)
return emp_type_table pipelined


cursor c1 is select empno, ename, sal from emp where sal > sal_var;
total_sal number(7,2) := 0;


for rec in c1 loop
   exit when c1%notfound;

   total_sal := total_sal + rec.sal ;
   if(total_sal >= total_sal_v ) then
      PIPE ROW (emp_type (rec.empno, rec.ename, rec.sal));
   end if;

end loop;


Then you created an ADF BC View Object using bind variables and based on this function table as
the following:

SELECT * FROM TABLE(getEmp (:salV, :totalSalV))

However, it fails with the following error

SELECT * from (SELECT * FROM TABLE(getEmp(:salV, ;totalSalV))) QRSLT where 1=2

SQL Querry Error Message: ORA-22905: cannot access rows from a non-nested table item

How to correct this error ?


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms