DBMS_SQL.Execute and BULK update - Need Help

oracle plsql sqlbulkcopy

Question

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?

Accepted Answer

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)


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why