?
Solved

What is the use PLSQL tables?

Posted on 2011-03-14
5
Medium Priority
?
789 Views
Last Modified: 2012-05-11
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
Comment
Question by:sakthikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35125959
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35263063
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 35263161
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
 

Author Closing Comment

by:sakthikumar
ID: 35479562
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question