Using extend - collection method, PL-SQL Programming

Using EXTEND

To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to the  collection. For e.g. the statement below appends 5 copies of element 1 to nested table courses:

courses.EXTEND(5,1);

You cannot use the EXTEND to initialize an automatically null collection. In addition if you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that kind.

The EXTEND operates on the internal size of the collection, that includes any deleted elements. Therefore, if EXTEND encounters deleted the elements, then it includes them in its tally. The PL/SQL keeps placeholders for the deleted elements so that you can swap them whenever you wish. Consider the example shown below:

DECLARE

TYPE CourseList IS TABLE OF VARCHAR2(10);

courses CourseList;

BEGIN

courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');

courses.DELETE(3); -- delete element 3

/* PL/SQL keeps a placeholder for element 3. So, the

next statement appends element 4, not element 3. */

courses.EXTEND; -- append one null element

/* Now element 4 exists, so the next statement does

not raise SUBSCRIPT_BEYOND_COUNT. */

courses(4) := 'Engl 2005';

Whenever it includes deleted elements, then the internal size of a nested table differs from the values returned by the COUNT and LAST. For illustration, when you initialize a nested table with five elements, delete the elements 2 & 5, then the internal size is 5, and the COUNT returns 3, &  the LAST returns 4. All the deleted elements are treated similarly.

Posted Date: 10/4/2012 3:17:44 AM | Location : United States







Related Discussions:- Using extend - collection method, Assignment Help, Ask Question on Using extend - collection method, Get Answer, Expert's Help, Using extend - collection method Discussions

Write discussion on Using extend - collection method
Your posts are moderated
Related Questions
Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

Perform the following queries on the Hospital1.DB using SQL Anywhere (START EARLY!). a. Which patients have purchased the drug "Tylenol"? List the names and addresses. Arrange the

GOTO Statement The GOTO statement branches to a label unconditionally. The label must be exclusive within its scope and should precede an executable statement or a PL/SQL block.

Object Types An object type is a user-defined complex datatype which encapsulates the data structure along with the functions and procedures required to manipulate the data. Th

Oracle 10G new features:- Automatic Database Diagnostic Monitor System Advancements - these methods will provides several methods for extracting reports through the Automatic

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Parameter and Keyword Description:   table_reference: This identifies a table or view which should be available when you execute the INSERT statement, and for that you sho

How Exceptions Propagate ? Whenever an exception is raised, and if the PL/SQL cannot find a handler for it in the present subprogram or block, the exception propagates. That is