0657-0660

Previous Table of Contents Next

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 reports that require breaks and subtotals. You could employ additional package-level variables to determine breakpoints and hold summary information as each row is returned to the application. This is just one example of how you can use packages to overcome many of the limitations of PL/SQL.

Exception Handling

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 valuable because it can be used as a catchall (all other

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 prevents an application from bombing because of an unhandled error in a subprogram.

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. User -defined exceptions are particularly useful in performing sanity checks within PL/SQL blocks. You can use a package variable to associate user-defined text with an exception, which the application can access through an additional packaged subprogram. Listing 26.8 demonstrates how you can use packaged constructs to provide an application additional information concerning a user-defined error.

Listing 26.8. A demonstration of user-defined exception handling.

 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 name with an Oracle error number; and RAISE_APPLICATION_ERROR raises a user-defined exception, accepting an error number and error text as parameters. The way in which exceptions are handled depends entirely on the nature of the application. What is most important is that all exceptions are handled. As a general rule, the OTHERS handler should always be used to trap all exceptions that do not have specific handlers.

Package Privileges

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 

Previous Table of Contents Next