Oracle PL/SQL - Bulk Collection usage into WHERE and FROM clause inside conditions and inner views -

i have strange problem using bulk collection element of clause. when execute code example, get, @ run-time, error "invalid table name".

if replace collection table works well.

is there restriction bulk collection i'm missing? maybe cannot use anonymous block in clause? in sql debugger see l_vol(i) has values l_vol(i).field doesn't exists.

thanks.

 type t_bulk_vol table of vol%rowtype;  l_vol t_bulk_vol;  ...  cursor cur select * vol ... ;   open cur;  loop       fetch cur bulk collect l_vol;      ....      insert dest      select col1, col2, ...       (inner view pivot, unpivot , l_vol(i).field ...) src l_vol(i).field = src.field; 

ps: cannot paste original code.

type t_bulk_vol pl/sql type. means can use in pl/sql constructs. cannot use in sql, if it's sql in pl/sql program.

if want use nested table in clause of select need define sql type. pain, because means can't use %rowtype definition (that's pl/sql keyword). you'll have create object signature matches projection of table, , create nested table of type. find out more.

---

your cursor defined wrongly. should select statement.

cursor cur select * vol ... ; 

save bulk collect l_vol actual fetch.

although presumably artefact of faking pl/sql because "cannot paste original code."