Sure, flick it over to: asktom_us@oracle.com with subject: Question 9534472300346065516
============================
OK, so here's a really basic demo that shows that the 'concept' works
SQL> create global temporary table t ( x int);
Table created.
SQL> create table t1 as select rownum g from dual connect by level <= 20;
Table created.
SQL>
SQL>
SQL>
SQL> create or replace
2 procedure P(r out sys_refcursor, s sys.odcinumberlist) is
3 begin
4 forall i in 1 .. s.count
5 insert into t values ( s(i));
6
7 open r for
8 select * from t1
9 where g in ( select x from t ) ;
10 end;
11 /
Procedure created.
SQL>
SQL> variable rc refcursor
SQL> exec p(:rc, sys.odcinumberlist(1,2,3));
PL/SQL procedure successfully completed.
SQL> print rc
G
----------
1
2
3
Of course that doesn't help you but the fact that we know it "works" means we can work with your demo.
So
1) After your forall, add a "select count" on that table and dbms_out it to make sure the rows are in there as expected
2) Cut your query down to its absolute smallest whilst still incorporating the temporary table, eg (I've anonymized some of it)
SELECT
...
FROM
YOUR_FILE_TABLE f
JOIN YOUR_TEMP_TABLE ON f.file_id = afi.file_id,
YOUR_VERSIONS_TABLE v
WHERE
f.deleted = 0
...
Make sure the ref cur prints. If it does, slowly introduce more parts of your query back into it to isolate the "breaking point"
See how you go