Avoiding collection exceptions, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Avoiding collection exceptions

Read-only operator (+) - sql, Read-Only Operator (+) - SQL The term r...

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

Referencing records, Referencing Records Unlike the elements in a coll...

Referencing Records Unlike the elements in a collection, that are accessed using subscripts, the fields in a record are accessed by name. To reference an individual field, you

Count operator in sql, Count Operator in SQL Example: Counting the stu...

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

Processing transactions, Processing Transactions This part describes ho...

Processing Transactions This part describes how to do the transaction processing. You learn the fundamental techniques that safeguard the consistency of your database, involvin

Using bulk collect clause- bulk bind performance improvement, Using the BUL...

Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

I need sql to infopath data connection, I need SQL to infopath data connect...

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

Effects of null for union - sql, Effects of NULL for union - SQL The ...

Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari

Use triggers to maintain referential integrity, At times, Brewbean's has ch...

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Iteration schemes- iterative control, Iteration Schemes The bounds of a ...

Iteration Schemes The bounds of a loop range can be variables, literals, variables, or expressions but must compute to integers. Below are some of the examples. As you can see t

Using set transaction, Using SET TRANSACTION You use the SET TRANSACTI...

Using SET TRANSACTION You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a

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