Previous | Table of Contents | Next

Page 181

 31     dbms_output.put_line (`There were `||sunday||' births on Sunday');
 32     dbms_output.put_line (`There were `||monday||' births on Monday');
 33     dbms_output.put_line (`There were `||tuesday||' births on Tuesday');
 34     dbms_output.put_line (`There were `||wednesday||' births on Wednesday');
 35     dbms_output.put_line (`There were `||thursday||' births on Thursday');
 36     dbms_output.put_line (`There were `||friday||' births on Friday');
 37     dbms_output.put_line (`There were `||saturday||' births on Saturday');
 38  end;
 39  /
There were 1 births on Sunday
There were 5 births on Monday
There were 4 births on Tuesday
There were 3 births on Wednesday
There were 5 births on Thursday
There were 0 births on Friday
There were 1 births on Saturday

PL/SQL procedure successfully completed.

You open the cursor in the first line of the Executable section. The program then initiates a loop. It is needed to evaluate each of the records in the employee table using the same code. The loop's first task is to fetch the first record from the cursor into the program for evaluation. Because birthday is a date data type, it is converted to a character string. It uses the day picture to format the date properly for evaluation. The day_of_week variable is then evaluated in a series of mutually exclusive elsif condition expressions in an if-then-else structure to determine the birth day-of-the-week counts. When the program reaches the end loop statement, it returns to the top of the loop and fetches the next record. When it reaches the end of the records, the no_data_found exception occurs and the loop terminates. The cursor is then closed, and the count of employee birthdays is the output. Some things you might want to remember about cursors include the following:

Identifying Exceptions

Exceptions are problems, errors, or abnormal conditions that occur when your PL/SQL program is operating. Some of these exceptions have already been discussed. In the previous section, you learned that opening a cursor already open or closing a cursor that is not open causes an exception. When an exception occurs, the program switches control from the Executable section to the Exception section of the PL/SQL block. If an exception handler exists

Page 182

for the exception, the program follows the instructions in the handler. If an exception handler does not exist, the program terminates with an error message.

operating as if the error did not occur.

A variety of exceptions can occur. To create an exception handler, you must identify the exception with the name of the handler. In addition to system exceptions, you can define and name user named exceptions. The next section, "Logging and Displaying Your Errors," discusses these. Table 8.1 contains the names and descriptions of many of the possible exceptions.

Table 8.1—Predefined PL/SQL Exceptions

Name Description
Cursor_already_open Occurs when the application tries to open a cursor currently
open. (ORA-6511 SQLCODE = -6511)
Dup_val_on_index Occurs when Oracle tries to perform an insert or update
operation and attempts to put a non-unique value in a field
defined with a unique constraint. (ORA-00001 SQLCODE = -1)
Invalid_cursor Occurs when the application references an undefined cursor in
program. (ORA-01001 SQLCODE _1001)
Invalid_number Occurs when the application tries to put an incorrect value in a
local variable or database column. Examples of incorrect values
are alphanumeric values being placed in a numeric field,
a value either numeric or alphanumeric larger than the field it is
to be assigned to, or a date that is invalid or is not in the
defined format. (ORA-01722 SQLCODE = -1722)
Login_denied Occurs when a database login is attempted by using either an
invalid user name or password. (ORA-01017 SQLCODE =
-1017)
No_data_found Occurs when a select statement is executed against a table and
no values are returned. (ORA-01403 SQLCODE = +100)
Not_logged_on Occurs when a call to the database happens and the application
is not connected. (ORA-01012 SQLCODE = -1012)
Program_error Occurs when and internal PL/SQL error happens.(ORA-06501
SQLCODE = -6501)
Storage_error Occurs when memory is used up. (ORA-06500 SQLCODE =
-6500)
Timeout_on_resource Occurs when the database manager decides to stop waiting for
a resource to become available. This often happens when a
table is locked by another user. (ORA-00051 SQLCODE =
-5100)
Too_many_rows Occurs when a select statement returns more than one row,

Page 183

Name Description
and the application is expecting one row.(ORA-01422
SQLCODE = -1422)
Transaction_backed_out Occurs when part of a database transaction has been rolled
back. (ORA-00061 SQLCODE = -61)
Value_error Occurs when PL/SQL cannot convert character data to
numbers. It also occurs when errors related to constraints and
truncation happen. (ORA-06502 SQLCODE = -6502)
Zero_divide(-1476) Occurs when a number is divided by zero.(ORA-01476
SQLCODE = -1476)
Others A catch-all exception used to handle any undefined or unnamed,
exceptions.

The error numbers following the preceding descriptions are the Oracle error number and the value returned to SQLCODE. SQLCODE is a built-in function that returns the status of the last executed database command. When there are no errors, the value is 0.

Logging and Displaying Your Errors

The exceptions identified in the preceding section cause the execution of statements in the Executable section to stop. The focus of the program shifts to the Exception section of the PL/SQL block. The processor reviews this section for an exception handler that contains the name of the exception. After it finds the handler, it performs the commands that follow it. If it does not find a handler, it terminates the program abnormally. Terminating the program abnormally can cause some problems. Unless a commit was issued in the program, all insert, update, or delete actions performed by the program are rolled back. In addition, when the PL/SQL program is terminated, an Oracle predefined error message issues. The message may be vague and meaningless to an operator. This proves especially troublesome to operators of an Oracle Form. More descriptive error messages would certainly increase the user-friendliness of the application.

The purpose of the exception section of the block is to eliminate these problems. Listing 8.9 illustrates a no_data_found exception. The Select statement attempts to find an employee with the name of "Buchanan". This employee does not exist in the Employee database, and the no_data_found exception will be raised. The first block does not contain an exception section. Notice the error message displayed.

Previous | Table of Contents | Next