DBMS_SQL.Execute and BULK update - Need Help

I have this query

select col1, col2 from table where critera = :criteria_var

The particular query was being used in DBMS_SQL to open cursor and BIND the variables. The values are then being fetched by DBMS_SQL fu开发者_高级运维nctions which are then updating another table. but this is happening one row at a time.

I want to use BULK FETCH INTO. I have read the tutorials but i couldn't find anythign where i can use BULK FETCH INTO with DBMS_SQL.

is it possible? if yes, then how?

You can use the BIND_ARRAY procedure in the DBMS_SQL package to do a bulk fetch. There is an example of this in the DBMS_SQL documentation.

Unless there is a particular need to use DBMS_SQL, however, (and assuming dynamic SQL is actually necessary in the first place) it seems likely that it would be easier to use native dynamic SQL, i.e.

EXECUTE IMMEDIATE 'SELECT col1, col2 FROM tableName WHERE criteria = :1'
   BULK COLLECT INTO l_col1_collection, l_col2_collection
  USING l_criteria_variable;

If you are just fetching the data from this query in order to update a different table, however, it would be more efficient to just let Oracle do that work by constructing a single UPDATE statement that used this query to fetch multiple rows. Something like

UPDATE destination_table dest
   SET (col1, col2) = (SELECT col1, col2
                         FROM source_table_name src
                        WHERE criteria = l_criteria_variable 
                          AND src.key_column = dest.key_column)
 WHERE EXISTS( SELECT 1
                 FROM source_table_name src
                WHERE criteria = l_criteria_variable 
                  AND src.key_column = dest.key_column)