[This followup was posted to comp.databases and a copy was sent to the
cited author.]

Hi

I can create a cursor, and I can make a table array out of the cursor,
but I want to execute subsequent SELECT statements against either the
cursor or the array. Is this possible? I know I can step through either
row-by-row, and using an array I can do multiple passes, but I would
like to be able to do things like select max(abc) from 'cursor or
array' or any other type of SQL query. I can of course create a physical
table from the array, but I'd prefer to be able to SELECT from a cursor
or array object in memory rather than SELECTing from a table on disk.

i.e

CURSOR cur_1 IS SELECT x from y

(if necessary create a table array type and fetch the cursor into it)

SELECT aggregate(z) from cur_1;

I prefer to keep both the orignal set and the select aggregate set as
separate objects in memory so I can also step through each of them using
FOR/LOOP constructs. Both require subsequent multiple pass queries.

The following is as close as I can get, but I want to use a cursor
instead of the myEmpRecord object. Instead I want to cast my cursor into
a memory table or read the cursor into an array and then cast it into a
memory table

sql>create or replace type myEmpRecord as object (empno number(4), ename
varchar2(10));

sql>create or replace type myEmpArray as table of myEmpRecord;

sql>declare
v_array myEmpArray;
begin
select myEmpRecord(empno, ename) bulk collect
into v_array
from emp
where rownum <= 5;
for r in (select * from table(cast(v_array as myEmpArray)) order
by empno) loop
dbms_output.put_line( r.empno || ',' || r.ename );
end loop;
dbms_output.put_line( '=====' );
for r in (select * from table(cast(v_array as myEmpArray)) order by
ename) loop
dbms_output.put_line( r.empno || ',' || r.ename );
end loop;
end;
Please reply to swright)AT(cityregina)DOT(com
remove )...(

Thanks and take care,
Shayne

either the cursor or the array. Is this possible? I know I can step
through either row-by-row, and using an array I can do multiple
passes, but I would like to be able to do things like select max(abc)
from 'cursor or array' or any other type of SQL query. <<


What is the purpose of using all the proprietary, non-relational
things you can think of in one vendor's product to avoid just writing
clean, portable, SQL? You don't like DRI? Set-oriented languages?
W@hat problem are you trying to solve?

[This followup was posted to comp.databases and a copy was sent to the
cited author.]

In article <18c7b3c2.0407221543.22d51eb0 (AT) posting (DOT) google.com>, jcelko212
@earthlink.net says...

In simplist terms I want to convert a complex sql statement into a
memory object for multiple subsequent queries...for/loop, aggregrates,
etc. However I don't want to have to predefine the query field structure
as part of a data type. Hence my preference for another way such as a
cursor.

No doubt there are other non-proprietary solutions, but I would like to
know if this is possible as a self-challenge. I learn more about the
tools at my disposal this way. But so far this question has stumped me
and everyone I've posed it to, but I'm sure someone has tried this
before and can let me know one way or the other.

BTW, I don't know what DRI is or any set-oriented languages. I'll look
into them as I continue my search. I like the challenge :-)

Thanks and take care,
Shayne