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
Restriction and AND - SQL Restriction is available via the WHERE operator, and so it is in SQL. However, by Example showing how a certain simple restriction can be expressed u

Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

Entering and Exiting If you enter the executable part of an autonomous routine, the major transaction suspends. When you exit the routine, the major transaction resumes. To ex

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip

(a) What are decision support systems, and what role do they play in the business environment? (b) Data warehousing is defined as "a subject-oriented, integrated, non-volatile c

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com