Thanks Billy and you all,
Billy, Can you please let me know a demo example of a single cursor to perform both read and write. Thanks again
INSERT INTO foo1 SELECT * FROM foo2
This SQL cursor does a SELECT (read) and an INSERT (write).
Likewise there are other SQL constructs like UPDATE(SELECT) and MERGE that also does both read and write.
The read and write parts can be done in parallel in certain circumstances. Which means it can scale.
Also direct path writes can be used at times too (always used when parallel) - which means better performance for the write part.
The basic and most fundamental rule for scalable Oracle applications:
Maximise SQL. Minimise PL/SQL.
Which means use SQL to its full extent as a data processing language. Do not try and reinvent the wheel using PL/SQL code to process SQL data.