Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Collections can do efficiently everything what cursors can do . So why and when we need cursors

2756515Sep 22 2014 — edited Sep 24 2014

Hi All,

Im just started learning SQL and PL/SQL. This is my understanding about cursors and collections:

Cursors: Oracle brings data from DB to a memory by cursors either explicit or implicit (Is RAM the memory here ? how it stores millions of rows from a table? Is the RAM of a Server going to be that large ??). Then we can go over each record by a loop. Instead of going to Cursor memory area each time, Cursor FOR LOOP gets 100 records each time to its local variable.

Where as in collections we can BULK COLLECT entire result set from an Implicit/Explicit cursor in to a Collection which is a local variable to PL/SQL and we can limit the number of records by LIMIT (useful if we have less memory). As collections are local, accessing them will be faster. We can perform calculations and store data back to collections. We have BULK FORALL for collections.

So, Collections have upper hand in everything when compared to Cursors. Only advantage of cursors I can think of is its simplicity in declaring, where as in collections we need to declare a record, type and a variable.

What are the other advantages of Cursors and where do we use them ?

Thanks

Dinesh

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 22 2014
Added on Sep 22 2014
13 comments
7,756 views