Nested tables versus index-by tables, PL-SQL Programming

Nested Tables versus Index-by Tables

The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in the similar way (by using subscript notation). The main distinction is that the nested tables can be stored in a database column (and hence the word "nested table") while the index-by tables cannot.

The Nested tables extend the functionality of the index-by tables by letting you SELECT,
INSERT, DELETE, and UPDATE nested tables stored in the database. (Keep in mind, that index-by tables cannot be stored in the database). Some collection methods also operate only on the nested tables and varrays. For example, the built-in procedure TRIM cannot be applied to the index-by tables.

Another merit of the nested tables is that an uninitialized nested table is automatically null (that is, the table itself is null, not its elements), while an uninitialized index-by table is simply empty. Therefore, you can apply the IS NULL comparison operator to the nested tables but not to index-by tables.

Though, index-by tables also have some merits. For example, the PL/SQL supports implicit (automatic) datatype conversion between the host arrays and index-by tables (but not nested tables). Therefore, the most efficient way to pass collections to and from the database server is to use the anonymous PL/SQL blocks to bulk-bind input and output host arrays to the index-by tables.

Posted Date: 10/3/2012 6:29:32 AM | Location : United States







Related Discussions:- Nested tables versus index-by tables, Assignment Help, Ask Question on Nested tables versus index-by tables, Get Answer, Expert's Help, Nested tables versus index-by tables Discussions

Write discussion on Nested tables versus index-by tables
Your posts are moderated
Related Questions
Relational Algebra - SQL It describes some operators, that together constitute an algebra that is not only relationally complete but also irreducibly so (very nearly- apart f

Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

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

Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

Block Structure The PL/SQL is a block-structured language. That is, the fundamental units (procedures, anonymous blocks, and functions) that make up a PL/SQL program are logi

In Packages The Forward declarations also group logically related subprograms in the package. The subprogram specifications go in the package specification, & the subprogram b

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera

Transaction context As the figure shows, the major transaction shares its context with the nested transactions, but not with the autonomous transactions. Similarly, If one aut