Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22905 Problem (Oracle 10.2)
ORA-22905 Problem [message #304603] Wed, 05 March 2008 16:02 Go to next message
mouser13
Messages: 2
Registered: March 2008
Junior Member
I get this error ORA-22905: cannot access rows from a non-nested table item. When I run this statement select * from table(nwo_relational_data.getrivergagelevels('PIST'));

Which all this is a wraper for the sql statement select * from local_relational_data where CWMS_ID_UC = 'PIST' and KEY_WORD like 'riverdamagelevel%';
Which works fine.

Just wondering if anyone has any idea's what could be the problem?

CREATE OR REPLACE PACKAGE cwms.nwo_relational_data
IS
TYPE return_cur IS REF CURSOR;

FUNCTION getrivergagelevels (
cwms_id_uc_in VARCHAR2
) RETURN return_cur;


FUNCTION getrivergagelevels (
cwms_id_uc_in VARCHAR2
) RETURN return_cur
IS
tmp_date TIMESTAMP WITH TIME ZONE;
tmp_text VARCHAR2 (1000);
rc_out return_cur;
BEGIN
open rc_out FOR select * from local_relational_data where CWMS_ID_UC = cwms_id_uc_in and KEY_WORD like 'riverdamagelevel%';
return rc_out;

END getrivergagelevels;
Re: ORA-22905 Problem [message #304605 is a reply to message #304603] Wed, 05 March 2008 16:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
22905, 00000, "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 type

Re: ORA-22905 Problem [message #304651 is a reply to message #304603] Thu, 06 March 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use package type in SQL. You have to create a real type with CREATE TYPE (or use 11g).

Regards
Michel
Re: ORA-22905 Problem [message #304657 is a reply to message #304603] Thu, 06 March 2008 01:04 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Or, If you convert your function to pipelined, you can use a package type in SQL.

Read up on pipelined table functions.
Re: ORA-22905 Problem [message #304662 is a reply to message #304657] Thu, 06 March 2008 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you convert your function to pipelined

Which also requires to create a type for the returned values.

Regards
Michel
Re: ORA-22905 Problem [message #304799 is a reply to message #304603] Thu, 06 March 2008 14:47 Go to previous message
mouser13
Messages: 2
Registered: March 2008
Junior Member
Thanks guys.
Previous Topic: Huge Data extract-need suggestions
Next Topic: summation of result of two queries
Goto Forum:
  


Current Time: Sun Dec 11 04:37:05 CST 2016

Total time taken to generate the page: 0.25144 seconds