Skip to Main Content
  • Questions

  • Real time scenarios for Collections and Ref cursors

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prashant.

Asked: February 17, 2016 - 9:19 am UTC

Last updated: February 18, 2016 - 10:51 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

Can you please help us by giving the real time scenarios or examples of following types in oracle. At what conditions or scenarios we need to use below types in code.

-Nested table
-Associated Arrays
-Varrays
-Refcursor
-Difference between object type and record type.

and Chris said...

The docs discuss the differences between associative arrays, nested tables and varrays in detail at:

http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS005

For a thorough explanation of the differences please read that. At a high-level:

Associative arrays

- PL/SQL only
- Index by integers or strings
- Unbounded upper limit on number of elements

Nested Tables

- PL/SQL or SQL
- Index by integer
- Unbounded upper limit on number of elements

Varrays

- PL/SQL or SQL
- Index by integer
- Fixed upper limit on number of elements

Records are also discussed in the link above. These are PL/SQL only complex data types.

Object types implement object-oriented concepts in the database such as inheritance. You can read all about these at:

http://docs.oracle.com/database/121/ADOBJ/adobjint.htm#ADOBJ001

For more discussion and examples of objects and collections see these articles:

http://www.oracle.com/technetwork/issue-archive/2006/06-nov/o66plsql-088399.html
http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42plsql-1653077.html

Ref cursors are separate concept. They are pointers to a result set. You can read more about these at:

http://docs.oracle.com/database/121/LNPLS/static.htm#LNPLS00605


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I mean real time scenarios

Prashant shirude, February 17, 2016 - 10:06 am UTC

This answer is not useful for me. I was expecting real time scenarios at what time we need to use these collection types. At what conditions. please mention real time scenarios that will help me more and more...
Chris Saxon
February 18, 2016 - 10:51 am UTC

Your question is very broad.

You don't need to use any of these. But they are helpful in various situations.

The documentation linked discusses in detail the differences between these type. It includes example code showing you how to use them. It also talks about when you should use one over another. Longer answers will be repeating large sections of this.

Please read the links. If you have specific questions about these after reading them then feel free to post a new question.

When to use collections

Dushan, February 17, 2016 - 3:13 pm UTC

Prashant, you can use associative arrays, nested tables and varrays, cursors at any time you want.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.