My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.