• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

What is the use PLSQL tables?

What is the use of PLSQL tables, VARRAYS in PLSQL, What is possible here and could not be done in a simple plsql block?
0
sakthikumar
Asked:
sakthikumar
  • 2
2 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
both pl/sql tables and varrays are arrays in PLSQL with a lot of differences. You can even store VARRAYS in the SQL tables.

http://psoug.org/reference/varray.html  - varrays information with examples.

http://psoug.org/reference/arrays.html - associative arrays with examples.
0
 
sdstuberCommented:
the various collection types are used primarily with in pl/sql blocks (anonymous and stored)

you may also declare them as SQL objects,  doing this allows you to write table functions like the DBMS_XPLAN.DISPLAY  function  or the STR2TBL function found on asktom, ee and many other sites.

using the various collection types lets you load some data into memory and manipulate it directly rather than via sql statements.  just as you would with arrays in other languages.

collections can be passed as single parameters thus allowing you to build functions that accept a variable number of inputs

collection types can be passed as arrays to and from other languages like java, c# or vb.
0
 
sdstuberCommented:
associative arrays can be used to do bulk operations which is faster than iterating row-by-row.

you can use BULK COLLECT from a select statement to pull multiple rows in one step

and use FORALL to apply inserts,deletes or updates for multiple criteria at once.


For more info on these,  I highly recommend Steven Feurerstein's books and articles on efficient collection use in pl/sql
0
 
sakthikumarAuthor Commented:
Excellent answers, Thankyou very much sdstuber.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now