SQL & PL/SQL

Hi All,

I am not a developer but working as a DBA, so not very much familiar with pl/sql, still gone through with documentation and could come up with some solution of my problem. I need some expert advice here.

Problem : I am writing down some kind of plsql program for monitoring of some special batch job, I know we have lot of other option to do the same including db/grid control ..etc but for some
reason i have to do this using plsql only.

Requirement : my requirement is to select data from table in plsql and then should have ability to query it again and again. I would not prefer to go to table rather than directly from plsql..

I wrote down below code for sample, bulk collect data into collection type and can print using for loop.

Declare
type ts is table of v$session%rowtype index by pls_integer;
tsess ts;
begin
select * bulk collect into tsess from v$session ;
for i in 1..tsess.count loop
dbms_output.put_line(tsess(i).terminal);
end loop;
end;
/

But, is there any way same collection ( tsess in above example ) can be queried using select statement like 'select * from table ( Tsess ) ' I have searched on net and found this can be done using creating type at database level. But my problem is I can not create any object in database as being it is a production one.

I was looking for if is there any way same can be accomplished ... like cast / multiset .. however, I could not get it through.

your help would be appreciated !!

Regards,