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 functions 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)
