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
We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv


Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Creating a SQL file 1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL co

Indeterminacy in SQL Some SQL expressions are actually not function invocations at all in the mathematical sense, being indeterminate-invocations operating on identical input

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

%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o