Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22905 Error in a PIPELINED function
ORA-22905 Error in a PIPELINED function [message #183142] Wed, 19 July 2006 10:02 Go to next message
rasa
Messages: 45
Registered: February 2006
Member
Friends:

Please help me with this weird anomaly that I am seeing.

I wrote a SPLIT function as a piplined function. It works fine as a standalone.

Say, when I run a Query like this:

SELECT TO_NUMBER(STRING_VAL) "E_ID"
FROM TABLE(CDI_SPLIT('52439,52440,52441,52442,52443,52444'))


I get the results as follows:

E_ID
52439
52440
52441
52442
52443
52444


However, when I try to use this "TABLE" result in a sub-query, I am getting
ORA-22905 Cannot Access Rows From a Non-Nested Table Item


Here is how I use the TABLE function in a sub-query:

SELECT * 
FROM 
EN B
WHERE E_ID IN (
               SELECT TO_NUMBER(STRING_VAL) "E_ID"
               FROM 
               TABLE(CDI_SPLIT('52439,52440,52441,52442,52443,52444'))
	      )


Is this a known issue?

Here is the environment that I am running on:

BANNER
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE	9.2.0.7.0	Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production


I also ran this in this environment and got the same error:

Oracle Database 10g Release 10.1.0.4.0 - Production
PL/SQL Release 10.1.0.4.0 - Production
CORE	10.1.0.4.0	Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
Re: ORA-22905 Error in a PIPELINED function [message #183145 is a reply to message #183142] Wed, 19 July 2006 10:07 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Try sticking a CAST in your TABLE clause to see if that helps.

Link.
Re: ORA-22905 Error in a PIPELINED function [message #183148 is a reply to message #183145] Wed, 19 July 2006 10:10 Go to previous messageGo to next message
rasa
Messages: 45
Registered: February 2006
Member
Art Metzer wrote on Wed, 19 July 2006 11:07

Try sticking a CAST in your TABLE clause to see if that helps.

Link.


It worked!!! Thank you **SO** very much, Art. God bless you!

[Updated on: Wed, 19 July 2006 10:10]

Report message to a moderator

Re: ORA-22905 Error in a PIPELINED function [message #346980 is a reply to message #183148] Wed, 10 September 2008 05:24 Go to previous messageGo to next message
shahbhav
Messages: 4
Registered: August 2006
Location: Baroda
Junior Member
Hello,
I am getting similar error for the split function on 9.2.0.6 database version.

the function works fine with literal values as below:
SQL> select * from table(att_hr_pkg.split('a,b,c'));

COLUMN_VALUE
-----------------------------------------------------------
a
b
c

But as soon as I replace literal values with a variable, I get ora-22905.

SQL> var p_to varchar2(100);
SQL> execute :p_to:='a,b,c';

PL/SQL procedure successfully completed.

SQL> select * from table(att_hr_pkg.split(:p_to));

select * from table(att_hr_pkg.split(:p_to))
*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

Does anyone know what is going on here?

Thanks,
Bhavesh
Re: ORA-22905 Error in a PIPELINED function [message #346982 is a reply to message #346980] Wed, 10 September 2008 05:32 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has been answered.
More precisely:
ORA-22905: cannot access rows from a non-nested table item
  *Cause:  attempt to access rows of an item whose type is not known at
           parse time or that is not of a nested table type
  *Action: use CAST to cast the item to a nested table typ

Just click on the previous links.

Regards
Michel

[Updated on: Wed, 10 September 2008 05:32]

Report message to a moderator

Previous Topic: DBA_ ,USER_ views and procedure calls
Next Topic: Deleting large number of rows Quickly in Oracle Db
Goto Forum:
  


Current Time: Sat Dec 10 11:07:55 CST 2016

Total time taken to generate the page: 0.05419 seconds