Solved

What is the use PLSQL tables?

Posted on 2011-03-14
5
785 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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

707 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