Solved

What is the use PLSQL tables?

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

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

778 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