Using extend - collection method, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Using extend - collection method

Predefined exceptions, Predefined Exceptions The internal exception is ...

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Manipulating local collections - pl/sql, Manipulating Local Collections ...

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally

Need for dynamic sql - pl sql , Need for Dynamic SQL: You need dynamic...

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

Rollback behavior - bulk bind performance improvement, Rollback Behavior ...

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Effects of null for unique specification - sql, Effects of NULL for UNIQUE ...

Effects of NULL for UNIQUE Specification When a UNIQUE specification u for base table t includes a column c that is not subject to a NOT NULL constraint, the appearance of sev

Write sql code that will create the writes table, Question: Consider th...

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

Application to export excel data to mssql server table, Application to Expo...

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

When or then key constraints, WHEN or THEN Key Constraints Suppose a t...

WHEN or THEN Key Constraints Suppose a table has two columns representing a period of time throughout which the information conveyed by the other columns is recorded as having

Package body, The Package Body The package specification is implemented...

The Package Body The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the p

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd