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 10.1.3.2.0 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

Goal


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

is

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


begin

for rec in c1 loop
   exit when c1%notfound;

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

end loop;

end;
/



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 ?

Solution

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