Avoiding collection exceptions, PL-SQL Programming

Avoiding Collection Exceptions 

In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown below:

DECLARE

TYPE NumList IS TABLE OF NUMBER;

nums NumList; -- atomically null

BEGIN

/* Assume execution continues despite the raised exceptions. */

nums(1) := 1; -- raises COLLECTION_IS_NULL (1)

nums := NumList(1,2); -- initialize table

nums(NULL) := 3 -- raises VALUE_ERROR (2)

nums(0) := 3; -- raises SUBSCRIPT_OUTSIDE_LIMIT (3)

nums(3) := 3; -- raises SUBSCRIPT_BEYOND_COUNT (4)

nums.DELETE(1); -- delete element 1

IF nums(1) = 1 THEN ... -- raises NO_DATA_FOUND (5)

In the first situation, the nested table is automatically null. In the second situation, the subscript is null. In the third situation, the subscript is outside the legal range. In the fourth situation, the subscripts exceed the number of elements in the table. In the fifth situation, the subscript designates a deleted element.

The list below shows when a given exception is raised:

2127_collection exception.png

In many cases, you can pass "invalid" subscripts to a method without raising the exception. For illustration, if you pass a null subscript to the procedure DELETE, it does nothing. You can also replace the deleted elements without raising NO_DATA_FOUND, as the example below shows:

DECLARE

TYPE NumList IS TABLE OF NUMBER;

nums NumList := NumList(10,20,30); -- initialize table

BEGIN

...

nums.DELETE(-1); -- does not raise SUBSCRIPT_OUTSIDE_LIMIT

nums.DELETE(3); -- delete 3rd element

DBMS_OUTPUT.PUT_LINE(nums.COUNT); -- prints 2

nums(3) := 30; -- legal; does not raise NO_DATA_FOUND

DBMS_OUTPUT.PUT_LINE(nums.COUNT); -- prints 3

END;

The Packaged collection types and the local collection types are never compatible. For example, assume that you want to call the following packaged process:

CREATE PACKAGE pkg1 AS

TYPE NumList IS VARRAY(25) OF NUMBER(4);

PROCEDURE delete_emps (emp_list NumList);

...

END pkg1;

CREATE PACKAGE BODY pkg1 AS

PROCEDURE delete_emps (emp_list NumList) IS ...

...

END pkg1;

If you run the PL/SQL block below, then the second procedure call fails with a wrong number or types of arguments error. This is because the packaged and local VARRAY types are incompatible even though their definitions are same.

DECLARE

TYPE NumList IS VARRAY(25) OF NUMBER(4);

emps pkg1.NumList := pkg1.NumList(7369, 7499);

emps2 NumList := NumList(7521, 7566);

BEGIN

pkg1.delete_emps(emps);

pkg1.delete_emps(emps2); -- causes a compilation error

END;

Posted Date: 10/4/2012 3:23:59 AM | Location : United States







Related Discussions:- Avoiding collection exceptions, Assignment Help, Ask Question on Avoiding collection exceptions, Get Answer, Expert's Help, Avoiding collection exceptions Discussions

Write discussion on Avoiding collection exceptions
Your posts are moderated
Related Questions
Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

TTITLE and BTITLE are commands in Pl-SQL to control report headings and footers. This Ttitle & Btitle are mainly used on creating SQL*PLUS report. Ttitle is used for toptitle headi

Write a program to implement the inverted file shown in the slides (Simple Index file, LabelID file and Data file).  Use the Avail_List to point at the deleted Label IDs so that th

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

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 e

Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Question: a) Given the following relation: Location(loc_id, bldg_code, room, capacity) The underlined field is a primary key. (i) Write a PL/SQL program using the impl

Multiple Assignment- SQL SQL supports multiple assignment to local variables and also applies multiple assignment semantics in SET clauses of UPDATE statements, but does not

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh