 ORA-22905: cannot access rows from a non-nested table item [message #253775] |
Tue, 24 July 2007 16:10  |
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
|
Member |
|
|
I have searched the forum and have thus tried a few code changes using CAST, but to no avail. I am not familiar with the in-house developed package: sm_time_pkg ... so this may be where my problem is. However, the following code works:
select /*+ ALL_ROWS */
-- get nuclear data
smpcr.sm_grp_id
, ten_min_time_intvl.time_intvl as sch_date
, smpcr.rsrc_id
from sm_rsrc_pcr_mv smpcr -- contains hourly records
join -- create 10-minute records for the nuclears
(
SELECT trunc( time_ref,'HH') AS sch_date
, time_ref AS time_intvl
FROM TABLE
( sm_time_pkg.timestamp_list
( trunc((sysdate + 1/24),'HH'),
trunc((sysdate + 25/24),'HH'),
'00 00:10:00'
)
)
) ten_min_time_intvl
on smpcr.sch_date = ten_min_time_intvl.sch_date
where smpcr.sch_date >= trunc( systimestamp + 1/24,'HH')
and smpcr.sch_date < trunc( systimestamp + 25/24,'HH')
and smpcr.rsrc_type_cd = 'NUCLEAR'
This code does not work (I simply joined on another table):
select /*+ ALL_ROWS */
-- get nuclear data
smpcr.sm_grp_id
, ten_min_time_intvl.time_intvl as sch_date
, smpcr.rsrc_id
, rc.max_cap
from sm_rsrc_pcr_mv smpcr -- contains hourly records
join -- create 10-minute records for the nuclears
(
SELECT trunc( time_ref,'HH') AS sch_date
, time_ref AS time_intvl
FROM TABLE
( sm_time_pkg.timestamp_list
( trunc((sysdate + 1/24),'HH'),
trunc((sysdate + 25/24),'HH'),
'00 00:10:00'
)
)
) ten_min_time_intvl
on smpcr.sch_date = ten_min_time_intvl.sch_date
-- get capacities and availability status
join dx_sm_resourcecapability_v rc
on smpcr.sch_date = rc.sch_date
and smpcr.rsrc_id = rc.rsrc_id
and smpcr.sm_grp_id = rc.sm_grp_id
where smpcr.sch_date >= trunc( systimestamp + 1/24,'HH')
and smpcr.sch_date < trunc( systimestamp + 25/24,'HH')
and smpcr.rsrc_type_cd = 'NUCLEAR'
The error returned near the "FROM TABLE" clause is:
| Quote: | ORA-22905: cannot access rows from a non-nested table item
|
Any clues are welcomed.
Thanks.
|
|
|
|
|
|
|
|
| Re: ORA-22905: cannot access rows from a non-nested table item [message #254006 is a reply to message #253775] |
Wed, 25 July 2007 08:29  |
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
|
Member |
|
|
The error occurs on the "FROM TABLE" line of the following section of code:
SELECT trunc( time_ref,'HH') AS sch_date
, time_ref AS time_intvl
FROM TABLE
( sm_time_pkg.timestamp_list
( trunc((sysdate + 1/24),'HH'),
trunc((sysdate + 25/24),'HH'),
'00 00:10:00'
)
)
If I run this code by it self I get data like:
7/25/2007 2:00:00.000 PM 7/25/2007 2:00:00.000 PM
7/25/2007 2:00:00.000 PM 7/25/2007 2:10:00.000 PM
7/25/2007 2:00:00.000 PM 7/25/2007 2:20:00.000 PM
...
.. and as I mentioned earlier I get the desired results without an error in the first set of code displayed in the post ... code that does not use CAST or:
ALTER SESSION SET CURSOR_SHARING=EXACT;
which was one of the suggestions for another post.
Here is the code for the function:
/*******************************************************************************************************
* function: timestamp_list
* parameters: start_ts in timestamp, end_ts in timestamp, interval_increment in string
* return value: timestamp_table pipelined
* description: returns a pipelined list of timestamps from start_ts to end_ts at
* specified increment intervals.
*******************************************************************************************************/
-- TYPES --
--
type timestamp_struct is record (time_ref timestamp);
type timestamp_table is table of timestamp_struct; -- a table of timestamps.
FUNCTION timestamp_list (start_ts in timestamp, end_ts in timestamp, interval_increment_string in string)
return sm_time_pkg.timestamp_table pipelined
is
current_timestamp sm_time_pkg.timestamp_struct;-- := sm_time_pkg.timestamp_struct();
create_interval interval day to second;
begin
create_interval := to_dsinterval(interval_increment_string);
if start_ts is not null
and end_ts > start_ts
and create_interval > to_dsinterval('0 00:00:00') then
--
current_timestamp.time_ref := start_ts;
--
while (current_timestamp.time_ref <= end_ts)
loop
pipe row (current_timestamp);
current_timestamp.time_ref := current_timestamp.time_ref + create_interval;
end loop;
end if;
end;
I have tried using CAST ... as in:
FROM TABLE
( CAST
( sm_time_pkg.timestamp_list
( trunc((sysdate + 1/24),'HH'),
trunc((sysdate + 25/24),'HH'),
'00 00:10:00'
)
AS timestamp_table
)
)
and also ...
but still get the same error.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
