ORA-22905 Error in a PIPELINED function [message #183142] |
Wed, 19 July 2006 10:02  |
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 #346980 is a reply to message #183148] |
Wed, 10 September 2008 05:24   |
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  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|