Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22905: cannot access rows from a non-nested table item
icon5.gif  ORA-22905: cannot access rows from a non-nested table item [message #253775] Tue, 24 July 2007 16:10 Go to next message
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 #253776 is a reply to message #253775] Tue, 24 July 2007 16:14 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
I meant to put this in the newbie section.
We use Oracle 10.2.0.1.
Re: ORA-22905: cannot access rows from a non-nested table item [message #253834 is a reply to message #253775] Wed, 25 July 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we don't know at which lines this error occurs, it is difficult to answer.
It is even more difficult that you don't tell us what it the parameter and return datatype of your function.

Regards
Michel
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 Go to previous messageGo to next message
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 ...

CAST ... AS timestamp


but still get the same error.
Re: ORA-22905: cannot access rows from a non-nested table item [message #254007 is a reply to message #254006] Wed, 25 July 2007 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the return type is neither timestamp_table nor timestamp it is sm_time_pkg.timestamp_table which is not known from SQL.
You have to create a type with CREATE TYPE.

Regards
Michel
Re: ORA-22905: cannot access rows from a non-nested table item [message #254008 is a reply to message #253775] Wed, 25 July 2007 08:37 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
When I run:

ALTER SESSION SET CURSOR_SHARING=EXACT;


and then run the query I get results with no errors.

Why would the first set of code work without using CAST and without using the ALTER SESSION statement?

Thanks.

Re: ORA-22905: cannot access rows from a non-nested table item [message #254028 is a reply to message #253775] Wed, 25 July 2007 10:00 Go to previous message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
The SQL will also work when using the CURSOR_SHARING_EXACT hint (without CAST and without the ALTER SESSION statement):

select    /*+ ALL_ROWS CURSOR_SHARING_EXACT  */
...


Why did Oracle behave differently between the 1st and 2nd SQL statements in the initial post?

Thanks,
Tom
Previous Topic: error in trigger
Next Topic: ora-03114 error
Goto Forum:
  


Current Time: Wed Dec 07 18:58:04 CST 2016

Total time taken to generate the page: 0.08957 seconds