Solved

What is the use PLSQL tables?

Posted on 2011-03-14
5
775 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
  • 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 500 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 500 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

Technology Partners: 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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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