Page 657
RETURN(0);
EXCEPTION
WHEN end_of_cursor THEN
RETURN(1);
WHEN OTHERS THEN
RETURN(1);
END get_next_address_type;
FUNCTION close_address_type RETURN NUMBER
IS
BEGIN
CLOSE c1;
RETURN(0);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END close_address_type;
FUNCTION reopen_address_type RETURN NUMBER
IS
BEGIN
OPEN c1;
RETURN(0);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END reopen_address_type;
BEGIN
OPEN c1;
END address_type_info;
Note that the cursor is opened in the body of the package itself. To retrieve the first row, an application need only call address_type_info.get_next_address_type to retrieve the first row. When this function returns 1, it informs the calling application that the end of the cursor is reached. The application should then call address_type_info.close_address_type. The OPEN c1 statement in the body of the cursor is executed only once when the package is first loaded. To access the cursor a second time, the application must first call address_type_info.reopen_address_type. Subsequent calls to address_type_info. get_next_address_type can then retrieve rows.
Although this approach might be somewhat cumbersome, it might be acceptable for retrieving small result sets. This method could also be useful in producing
Oracle provides many predefined exceptions and a number of functions and procedures to handle them. Oracle implicitly raises predefined exceptions when they occur in PL/SQL blocks. Among these, the OTHERS exception is extremely
Page 658
exceptions that are not explicitly handled), which in many cases is all that is needed. Even when specific handlers are used, using the OTHERS exception is a good idea. Using this exception
In some cases, defining an exception that does not exist in Oracle might be useful. User-defined exceptions are declared in much the same way as variables. For example, in Listing 26.7, the user-defined exception end_of_cursor is declared in the get_next_address_type function. Control is passed to the exception handler using the RAISE statement.
Listing 26.8. A demonstration of user-defined exception handling.
{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}
CREATE OR REPLACE PACKAGE manage_individuals AS
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,
d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER;
FUNCTION get_error_text(text_out OUT VARCHAR2) RETURN NUMBER;
END manage_individuals;
CREATE OR REPLACE PACKAGE BODY manage_individuals AS
user_id VARCHAR2(20);
invalid_b_day EXCEPTION;
error_text VARCHAR2(255);
FUNCTION insert_individual(last_in VARCHAR2, first_in VARCHAR2,
d_o_b DATE, notes_in VARCHAR2) RETURN NUMBER
IS
new_id NUMBER;
temp_bd VARCHAR2(20);
temp_today VARCHAR2(20);
BEGIN
temp_bd:=TO_CHAR(d_o_b, `MMDDYYYY',
`nls_date_language = American');
SELECT TO_CHAR(sysdate, `MMDDYYYY',
`nls_date_language = American')
INTO temp_today FROM dual;
Page 659
IF ((to_date(temp_bd, `MMDDYYYY',
`nls_date_language = American') >
to_date(temp_today, `MMDDYYYY',
`nls_date_language = American')) OR
((SUBSTR(temp_today, 7, 4) SUBSTR(temp_bd, 7, 4))
> 100)) THEN
RAISE invalid_b_day;
ELSE
SELECT individual_ids.nextval INTO new_id FROM dual;
INSERT INTO individual (id, last_name, first_name,
date_of_birth, notes, last_updt_user,
last_updt_date) VALUES (new_id, last_in,
first_in, d_o_b, notes_in, user_id,
sysdate);
error_text:= ` `;
RETURN(new_id);
END IF;
EXCEPTION
WHEN invalid_b_day THEN
error_text:= `Date of birth outside normal range.';
RETURN(11);
WHEN OTHERS THEN
error_text:=SUBSTR(SQLERRM, 1, 255);
RETURN(1);
END insert_individual;
FUNCTION get_error_text(text_out OUT VARCHAR2) RETURN NUMBER
IS
BEGIN
text_out:=error_text;
RETURN(0);
EXCEPTION
WHEN OTHERS THEN
text_out:='Unable to retrieve error information.';
RETURN(1);
END get_error_text;
BEGIN
SELECT user INTO user_id FROM dual;
END manage_individuals;v
The example in Listing 26.8 uses a package-level variable to store error text and provides a function to retrieve error text. Note the use of the predefined function SQLERRM in the OTHERS handler. In this context, SQLERRM is used to copy the Oracle error message into the package variable.
The example in Listing 26.8 is just one way to deal with exceptions in packages. Oracle includes many other predefined functions used to handle exceptions, including SQLCODE, EXCEPTION_INIT, and RAISE_APPLICATION_ERROR. SQLCODE returns the Oracle error number associated with an exception; EXCEPTION_INIT enables the developer to associate a
Using packages can greatly simplify the process of granting rights to users and roles. When you grant a user the EXECUTE privilege for a package, the user can access any data and subprograms in the package specification. In the package body, subprograms can access other packaged or standalone subprograms and other database objects. The user to whom EXECUTE was granted does not need to have any rights to the external objects referenced in the package body. This is another way in which you can use packages for information hiding. Listing 26.9 redefines the lookup_admin package from Listing 26.4 to hide the implementation of address_type_info from Listing 26.7.
Page 660
Listing 26.9. A demonstration of indirect function calling.
CREATE OR REPLACE PACKAGE lookup_admin AS
FUNCTION get_next_address_type(id_out OUT NUMBER,
description_out OUT VARCHAR2) RETURN NUMBER;
FUNCTION close_address_type RETURN NUMBER;
FUNCTION reopen_address_type RETURN NUMBER;
/* add get_next, close, and reopen functions */
/* for other lookups here */
FUNCTION add_address_type(description VARCHAR2) RETURN NUMBER;
FUNCTION add_phone_type(description VARCHAR2) RETURN NUMBER;
FUNCTION add_contact_type(description VARCHAR2) RETURN NUMBER;
FUNCTION add_contact_method(description VARCHAR2) RETURN NUMBER;
FUNCTION add_contact_reason(description VARCHAR2) RETURN NUMBER;
/* add update and delete functions here */
END lookup_admin;
/
CREATE OR REPLACE PACKAGE BODY lookup_admin AS
user_id VARCHAR2(40);
temp_id NUMBER(10);
temp_desc VARCHAR2(40);
FUNCTION get_next_address_type(id_out OUT NUMBER,
description_out OUT VARCHAR2) RETURN NUMBER
IS
ret NUMBER(10);
BEGIN
ret:=address_type_info.get_next_address_type(id_out, description_out);
RETURN(ret);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END get_next_address_type;
FUNCTION close_address_type RETURN NUMBER
IS
ret NUMBER(10);
BEGIN
ret:=address_type_info.close_address_type;
RETURN(ret);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END close_address_type;
FUNCTION reopen_address_type RETURN NUMBER
IS
ret NUMBER(10);
BEGIN