Problem I am trying to solve:
I need to read and process data from several tables. Each has the identical structure but contain different data.

I wrote a pipelined table function that worked against a view. I then reset the view to whatever table I needed to see. This was just done as a proof of concept. (key excerpts are shown below).

However, I do not want to continually update my view when I need to run the table function against a new table. So, I tried to replace the code using a sys_refcursor and a cursor variable.

Most of the things I've tried ended in compilation errors, but the current example (shown below) compiles. However, when I run it, I get
ORA-22905: cannot access rows from a non-nested table item

Clearly, I am not getting some key conceot, and any insights or suggestions you may have would be appreciated!

**********************
Here are the critical portions from the code that did work:

-- there are actually 100+ variables in the object, but 3 should illustrate the concept just fine.
create or replace
TYPE statsData_t as object (
sample_time NUMBER(11) ,
element_id NUMBER(11) ,
var108 NUMBER(38,7)
);
/


create or replace
TYPE StatsDataRecSet_t IS TABLE OF StatsData_t;
/
CREATE or replace
PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR;
END refcur_pkg;
/


Create or replace package
regdata_pkg as


FUNCTION regularize (c1 IN refcur_pkg.refcur_t) return StatsDataRecSet_t pipelined;
procedure nh_regularize (statsView VARCHAR2, startUTC NUMBER, endUTC NUMBER);
end regdata_pkg;

/


create or replace package body
regdata_pkg is



CREATE or replace
FUNCTION regularize (c1 refcur_pkg.refcur_t)
RETURN StatsDataRecSet_t pipelined
PARALLEL_ENABLE(PARTITION c1 BY ANY)
IS


in_rec nhv_stats0%rowtype;
out_rec statsData_t := statsData_t(
NULL,
NULL,
NULL
);

begin


LOOP
FETCH c1 INTO in_rec;
EXIT WHEN c1%NOTFOUND;


out_rec.sample_time := in_rec.sample_time;
out_rec.element_id := in_rec.Element_id;
out_rec.VAR108 := in_rec.VAR108;
pipe row (out_rec);

END LOOP; -- the cursor loop

RETURN;
end;


procedure nh_regularize (statsView VARCHAR2, startUTC NUMBER, endUTC NUMBER)
IS

start_date NUMBER;
end_date NUMBER;
num_loops NUMBER;
sqlCommand VARCHAR2(1000);
BEGIN


sqlCommand := 'create or replace view nhv_stats0 as select s.* from ' || statsView || ' s where var81 > ' || startUTC || ' and var81 <= ' || endUTC;

execute immediate sqlCommand;

insert /* append */ into nh_reg0
select sample_time, element_id,
VAR108

from table(regularize(CURSOR(select * from nhv_stats0)));


commit;

END;
--/

************Now here is the latest variant of my trying to do it with the view removed. It compiles but fails when run:



create or replace
TYPE statsData_t as object (
sample_time NUMBER(11) ,
element_id NUMBER(11) ,
var108 NUMBER(38,7)
);
/



Create or replace package
regdata_pkg as


TYPE StatsDataRecSet_t IS TABLE OF StatsData_t;
FUNCTION regularize (c1 IN sys_refcursor) return StatsDataRecSet_t pipelined;
procedure nh_regularize (statsView VARCHAR2, startUTC NUMBER, endUTC NUMBER)'
end regdata_pkg;

/

create or replace package body
regdata_pkg is

cursor stats_cursor is select * from nh_stats;




--=====================================================
--ROUTINE: REGULARIZE
--
-- This is the heart of the regularization process
-- Given a cursor, it will return regularized rows of data
--=================================================
FUNCTION regularize (c1 IN sys_refcursor)
RETURN StatsDataRecSet_t pipelined
--PARALLEL_ENABLE(PARTITION c1 BY ANY)
IS

in_rec stats_cursor%ROWTYPE;

out_rec statsData_t := statsData_t(
NULL, NULL, NULL );


begin


LOOP
FETCH c1 INTO in_rec;
EXIT WHEN c1%NOTFOUND;

out_rec.sample_time := in_rec.sample_time;
....

RETURN;
end regularize;


procedure nh_regularize (statsView VARCHAR2, startUTC NUMBER, endUTC NUMBER)
IS

start_date NUMBER;
end_date NUMBER;
num_loops NUMBER;
sqlCommand VARCHAR2(1000);

statsViewCursor sys_refcursor;

BEGIN

open statsViewCursor for 'select s.* from ' || statsView || ' s where var81 > :startUTC
and var81 <= :endUTC ' using startUTC, endUTC;

insert /* append */ into nh_reg0
select sample_time, element_id,
VAR108

from table(regularize(CURSOR(statsViewCursor)));

close statsViewCursor;

END nh_regularize;
--/


It fails on the "insert ... into nh_reg0 ... from table ..." statement with the following error:
/home/cms/configSpecs:\oerr ora 22905
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

I actually tried casting it to every object type that made sense (and even some that didn't), but all casts returned invalid data type.