SQL & PL/SQL

Hi,

I have read-only access to a database, i.e. I cannot CREATE anything. But I can declare a Colection in an anonymous PL/SQL block, and populate it with Bulk Collect. Now, I would like to use this Collection I have in a cursor, to join with other tables. In effect, I am breaking down a very complex query into two parts. The first part only retrieves a list of primary keys, and stores this list into a Collection. The second part must take this list of primary keys, and gather more data. I cannot use dbms_output, because the entire result set is too big. Instead, I define a cursor variable in SqlPLus and print it.

The trouble is, I cannot seem to get a "column name" for my collection elements. I can TABLE(CAST )the colelction. But then, how do I refer to an element when I write a Select statement ?

Here is a simplified example, written for SqlPlus

=============================

set serverout on
set feedback off
VARIABLE my_cr REFCURSOR

DECLARE
TYPE cn_num_tab IS TABLE OF number(9) ;
cn_num cn_num_tab; /* this is my collection */
BEGIN
dbms_output.enable( 500000 );
SELECT cnote_no
BULK COLLECT INTO cn_num,
FROM cnotes
WHERE system_date between to_date( '19/may/2002', 'dd/mon/yyyy' )
and to_date( '24/may/2002', 'dd/mon/yyyy' )
AND rownum < 10 -- for testing only
;
dbms_output.put_line( 'number of cnotes: ' || to_char( cn_num.count) );

/* now I need to access the elements of my collection as */
/* */
/* table_alias.column_name */

OPEN :my_cr FOR
SELECT st.val,
ctn.what_column_name
FROM stmt_txns st,
TABLE( CAST ( cn_num AS cn_num_tab ) ctn
WHERE st.txn_type = 'C'
AND st.txn_ref_no = ctn.what_column_name
;
END ;
/

PRINT my_cr

====================

Of course, there is no "what_column_name" above. This is what I'm asking about ?
Is this possible at all ? Can I join in a SELECT statement with a Local Collection defined in an anonymous block ?

regards,

Andrew Schonberger