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:
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.1Predefined 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.
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.