Why is the syntax for returning the column values as a collection so complicated when using dynamic sql in pl/sql?
For example why is the syntax like:
EXECUTE IMMEDIATE 'DELETE FROM bulk_collect_test RETURNING object_id INTO :1' returning bulk collect into l_tab;
And not like:
EXECUTE IMMEDIATE 'DELETE FROM bulk_collect_test RETURNING object_id BULK COLLECT INTO :1' USING l_tab(i);
Basically why do we need to use RETURNING clause twice - once returning the object_id values into :1 and once again returning bulk collect.
Also how does oracle substitute the bind variable :1 i.e. how does it correlate the variable :1 and the collection l_tab?
By learning the right syntax I can write the statement correctly but wanted to understand the reason the syntax was not made any simpler.
Thanks in advance.