Collections in pl sql, PL-SQL Programming


 The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subscript which determines its position in the collection. The PL/SQL offers 3 kinds of collections: the nested tables, index-by tables, and varrays . The Nested tables extend the functionality of the index-by tables (formerly known as the "PL/SQL tables").

The Collections works like the arrays found in mainly the third-generation programming languages. Though, collections can have only one dimension and should be indexed by integers. (In several languages like Ada and Pascal, arrays can have the multiple dimensions and can be indexed by inventory types.)

The Nested tables and varrays can store instances of an object type and, on the other hand, can be attributes of an object type. The collections can also be passed as the parameters. Therefore, you can use them to move the columns of data into and out of database tables or between the client-side applications and stored subprograms.




Keyword and Parameter Description



These identify a user-defined type specifier that is used in the subsequent declarations of collections.


This is any PL/SQL datatype except the BOOLEAN, BINARY_INTEGER, LONG, LONG RAW, NATURAL, NATURALN, NCLOB, NCHAR, NVARCHAR2, object types with TABLE or VARRAY attributes, PLS_INTEGER, POSITIVE, SIGNTYPE, POSITIVEN, REF CURSOR, STRING, TABLE, or VARRAY. Also, with the varrays, the element_type cannot be CLOB, BLOB, or an object type with BLOB or CLOB attributes. If the element_type is a record type, every field in the record should be a scalar type or an object type.


This optional clause defines the Version 2 PL/SQL tables that are called index-by tables in Version 8.


This is a positive integer literal which specifies the maximum size of a varray that is the maximum number of elements that the varray can contain.

Posted Date: 10/6/2012 9:05:54 AM | Location : United States

Related Discussions:- Collections in pl sql, Assignment Help, Ask Question on Collections in pl sql, Get Answer, Expert's Help, Collections in pl sql Discussions

Write discussion on Collections in pl sql
Your posts are moderated
Related Questions
Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

Identifiers You use identifiers to name the PL/SQL program items and units that include constants, variables, cursors, exceptions, cursor variables, subprograms, and packages.

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

When Are Constraints Checked Under the model constraints are conceptually checked at all statement boundaries (and only at statement boundaries). By default the same is true

SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.