with bind peeking - it could work - but it would be subject to bind peeking issues...
with bind peeking, it would derive the estimated the cardinality the first time - so it the collection "usually had about 500 items in it", but the first time you hard parsed - it had 1 in it, the plan would be developed for 1. If the next time you ran it - it had 500....
so, yes, bind peeking would let it default the cardinality the first time:
ops$tkyte%ORA11GR2> declare
2 l_data sys.odciVarchar2List := sys.odciVarchar2List( 'a', 'b', 'c' );
3 l_n number;
4 begin
5 l_n := l_data.count;
6 for x in (select * from table(l_data) where rownum <= l_n )
7 loop
8 null;
9 end loop;
10 for y in ( select plan_table_output from table(dbms_xplan.display_cursor))
11 loop
12 dbms_output.put_line( y.plan_table_output );
13 end loop;
14 end;
15 /
SQL_ID 752n8ycjxuxnb, child number 0
-------------------------------------
SELECT * FROM TABLE(:B1 ) WHERE ROWNUM <= :B2
Plan hash value: 1440075759
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)
|* 1 | COUNT STOPKEY | | | |
| 2 | COLLECTION ITERATOR PICKLER FETCH| | 3 | 6 | 29 (0)
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=:B2)
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> declare
2 l_data sys.odciVarchar2List := sys.odciVarchar2List( 'a', 'b', 'c', 'd', 'e', 'f' );
3 l_n number;
4 begin
5 l_n := l_data.count;
6 for x in (select * from table(l_data) where rownum <= l_n )
7 loop
8 null;
9 end loop;
10 for y in ( select plan_table_output from table(dbms_xplan.display_cursor))
11 loop
12 dbms_output.put_line( y.plan_table_output );
13 end loop;
14 end;
15 /
SQL_ID 752n8ycjxuxnb, child number 0
-------------------------------------
SELECT * FROM TABLE(:B1 ) WHERE ROWNUM <= :B2
Plan hash value: 1440075759
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)
|* 1 | COUNT STOPKEY | | | |
| 2 | COLLECTION ITERATOR PICKLER FETCH| | 3 | 6 | 29 (0)
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=:B2)
PL/SQL procedure successfully completed.
but it would be "stuck" there. using a representative number (what I said above) or a small/med/large number as appropriate would result in less variability (someone hard parses with 1 one day and 1,000 another... different plans on different days)