Paun Mihut Catalin Tema 5

Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE

Cuprins: Section 7 ....................................................................................................................................................... 2 Section 7 Lesson 1 Handling Exceptions ................................................................................................... 2 Test Quiz: Handling Exceptions ............................................................................................................... 10 Section 7 Lesson 2 Trapping Oracle Server Exceptions........................................................................... 13 Test Quiz: Trapping Oracle Server Exceptions ........................................................................................ 24 Section 7 Lesson 3 Trapping User-Defined Exceptions ........................................................................... 28 Test Quiz: Trapping User-Defined Exceptions ........................................................................................ 34 Section 7 Lesson 4 Recognizing the Scope of Exceptions ....................................................................... 37 Test Quiz: Recognizing the Scope of Exceptions ..................................................................................... 41


Section 7 Section 7 Lesson 1 Handling Exceptions Exceptions Vocabulary

Identify the vocabulary word for each definition d efinition below: Exception Handler

Code that defines the recovery actions to be performed when execution-time errors occur.

Exception

Occurs when an error occurs during the execution of a program that disrupts the normal operation of the program.

Exception handling

Allows clean separation of the error processing code from the executable code so that a program can continue operating in the presence of errors.

Exception propagating

The exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search in.

Try It / Solve It 1. What happens when Oracle encounters a runtime problem while executing a PL/SQL block?

An exception is raised and the rest of the block’s execution section is not n ot executed.

2. What do you need to add to your PL/SQL block to address these problems?

Add an exception section that includes handlers to trap and deal with all possible problems.

3. List three advantages of handling exceptions within a PL/SQL block. 

It can protect the user from frequent repeated errors.



It can prevent data in the database from being lost or overwritten.



PL/SQL code is more readable because error-handling routines can be written in the



same block in which the error occurred.


Section 7 Section 7 Lesson 1 Handling Exceptions Exceptions Vocabulary

Identify the vocabulary word for each definition d efinition below: Exception Handler

Code that defines the recovery actions to be performed when execution-time errors occur.

Exception

Occurs when an error occurs during the execution of a program that disrupts the normal operation of the program.

Exception handling

Allows clean separation of the error processing code from the executable code so that a program can continue operating in the presence of errors.

Exception propagating

The exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search in.

Try It / Solve It 1. What happens when Oracle encounters a runtime problem while executing a PL/SQL block?

An exception is raised and the rest of the block’s execution section is not n ot executed.

2. What do you need to add to your PL/SQL block to address these problems?

Add an exception section that includes handlers to trap and deal with all possible problems.

3. List three advantages of handling exceptions within a PL/SQL block. 

It can protect the user from frequent repeated errors.



It can prevent data in the database from being lost or overwritten.



PL/SQL code is more readable because error-handling routines can be written in the



same block in which the error occurred.


4. Run this PL/SQL code and then answer the questions that follow.

DECLARE v_jobid employees.job_id%TYPE; BEGIN SELECT job_id INTO v_jobid FROM employees WHERE department_id = 80; END; A. What happens when you run the block?

The block fails because there is more than one employee in department 80 and an exception (error) is raised.

B. In your own words, explain what you can do to fix this problem.

Select a unique column for the WHERE clause (the block could still fail if the unique value entered did not exist; Write an exception handler to trap the error.

C. Modify the code to fix the problem. Use a TOO_MANY_ROWS exception handler.

DECLARE v_jobid employees.job_id%TYPE; BEGIN SELECT job_id INTO v_jobid FROM employees WHERE department_id = 80; EXCEPTION WHEN TOO_MANY_ROWS THEN


DBMS_OUTPUT.PUT_LINE ('Too many rows returned from the query'); END;

D. Run your modified code. What happens this time?

The exception is successfully trapped and the message ‘'Too many rows returned from the query’ is displayed.

5. Run the following PL/SQL block, which tries to insert a new row (with department_id = 50) into the departments table. What happens and why?

BEGIN INSERT INTO departments (department_id, department_name,manager_id, location_id) VALUES (50, 'A new department', 100, 1500); DBMS_OUTPUT.PUT_LINE('The new department was inserted'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('An exception has occurred.'); END; The INSERT statement failed because department_id 50 already exists and department_id is a unique column. The rest of the executable section was skipped, therefore the message ‘The new department was inserted’ was not displayed. The WHEN OTHERS OTHERS exception handler was executed.

6. Enter the following PL/SQL block, which tries to SELECT all the employees in a specific department. Run it three times, using department_ids 10, 20, and 30. What happens and why?

DECLARE v_employee_id employees.employee_id%TYPE;


v_last_name employees.last_name%TYPE; BEGIN SELECT employee_id, last_name INTO v_employee_id, v_last_name FROM employees WHERE department_id = ; DBMS_OUTPUT.PUT_LINE('The SELECT was successful'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); END; Using department_id = 10 is successful because there is exactly one employee in that department, therefore the SELECT returned exactly one row.

Using department_id 20 failed because there is more than one employee in that department.

Using department_id 30 failed because there are no employees in that department.

7. Modify your code from question 6 to add two more exception handlers to trap the possible exceptions individually. Use NO_DATA_FOUND and TOO_MANY_ROWS. Rerun the block three times, using 10, 20, and 30 as before. Observe the message displayed in each case.


DECLARE v_employee_id employees.employee_id%TYPE; v_last_name employees.last_name%TYPE; BEGIN SELECT employee_id, last_name INTO v_employee_id, v_last_name FROM employees WHERE department_id = ; DBMS_OUTPUT.PUT_LINE('The SELECT was successful'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No rows were selected'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one row was selected'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); END; Pentru valoarea 10 :

Pentru valoarea 20:

Pentru valoarea 30:


8. List three guidelines for trapping exceptions.



Always add exception handlers whenever there is a possibility of an error occurring.



Handle named exceptions whenever possible, instead of using OTHERS in exception



handlers.



Test your code with different combinations of bad data to see what potential errors



arise.



Write out debugging information in your exception handlers.



Carefully consider whether each exception handler should commit the transaction, roll



it back, or let it continue.

9. Enter and run the following PL/SQL block. Explain the output. Note: the WHEN OTHERS handler successfully handles any type of exception which occurs.

DECLARE v_number NUMBER(2); BEGIN v_number := 9999; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); EN An exception has occurred because the 4-digit value 9999 is too large to be assigned to a NUMBER(2) variable. The block’s exception section has handled the exception successfully and displayed ‘An exception has occurred’. The exception has NOT been propagated back to the calling environment (Application Express) which therefore reports ‘Statement Processed’, meaning: success.

10. Modify the block in question 9 to omit the exception handler, then re-run the block. Explain the output.

DECLARE v_number NUMBER(2); BEGIN


v_number := 9999; END; The block does not handle the exception, which therefore propagates back to Application Express. Application Express displays an ‘ORA-06502: PL/SQL: numeric or value error: number precision too large.’

11. Enter and run the following code and explain the output.

DECLARE v_number NUMBER(4); BEGIN v_number := 1234; DECLARE v_number NUMBER(4); BEGIN v_number := 5678; v_number := 'A character string'; END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An exception has occurred'); DBMS_OUTPUT.PUT_LINE('The number is: '||v_number); END; The inner block’s attempt to assign a character string to a NUMBER variable causes an exception. The exception is not handled in the inner block, which therefore propagates the exception to the outer block. The outer block successfully handles the exception. The number 1234 (not 5678) is displayed because the inner block’s v_number is out of scope in the outer block.



Test Quiz: Handling Exceptions Section 1

(Answer all questions in this section) 1.

Which of the following is NOT an advantage of including an exception handler in a PL/SQL block?

Mark for Review (1) Points

Avoids costly and time-consuming correction of mistakes Prevents errors from being propagated back to the calling environment Prevents errors from occurring (*) Code is more readable because error-handling routines can be written in the same block in which the error occurred Correct 2.

Which of the following best describes a PL/SQL exception?


A user enters an invalid password while trying to log on to the database. An error occurs during the execution of the block, which disrupts the normal operation of the program. (*) The programmer forgets to declare a cursor while writing the PL/SQL code. A compile-time error occurs because the PL/SQL code references a non-existent table. Correct 3.

Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block?


An attempt is made to divide by zero A SELECT statement returns no rows Any other kind of exception that can occur within the block All of the above (*) None of the above Correct 4.

Only one exception at a time can be raised during one execution of a PL/SQL block. True or False?


True (*) False Correct 5.

Which of the following EXCEPTION sections is constructed correctly? (Choose thr ee.)

(Choose all correct answers)


Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE EXCEPTION WHEN NO_DATA_FOUND THEN statement_1 ; WHEN OTHERS THEN statement_2 ; END; (*) EXCEPTION WHEN NO_DATA_FOUND THEN statement_1 ; WHEN NO_DATA_FOUND THEN statement_2 ; WHEN OTHERS THEN statement_3 ; END; EXCEPTION WHEN OTHERS THEN statement_1 ; END; (*) EXCEPTION WHEN OTHERS THEN statement_1 ; WHEN NO_DATA_FOUND THEN statement_2 ; END; EXCEPTION WHEN TOO_MANY_ROWS THEN statement_1 ; END; (*) Incorrect. Refer to Section 7 Lesson 1. 6.

The following EXCEPTION section is constructed correctly. True or False? EXCEPTION WHEN ZERO_DIVIDE OR TOO_MANY_ROWS OR NO_DATA_FOUND THEN statement_1 ; statement_2 ; WHEN OTHERS THEN statement_3 ; END;



Which of the following are NOT good practice guidelines for exception handling? (Choose two.) (Choose all correct answers) Handle specific named exceptions where possible, instead of relying on WHEN OTHERS. Test your code with different combinations of data to see what potential errors can happen. Use an exception handler whenever there is any possibility of an error occurring. Allow exceptions to propagate back to the calling environment. (*) Include a WHEN OTHERS handler as the first handler in the exception section. (*)


Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE Correct 8.

Examine the following code. Why does this exception handler not follow good practice guidelines? (Choose two.) DECLARE v_dept_name departments.department_name%TYPE; BEGIN SELECT department_name INTO v_dept_name FROM departments WHERE department_id = 75; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('A select returned more than one row'); END; (Choose all correct answers) The exception handler should COMMIT the transaction. The exception handler should test fo r the named exception NO_DATA_FOUND. (*) You should not use DBMS_OUTPUT.PUT_LINE in an exception handler. The exception section should include a WHEN TOO_MANY_ROWS exception handler. (*) department_id 75 does not exist in the departments table. Correct



Section 7 Lesson 2 Trapping Oracle Server Exceptions Vocabulary Identify the vocabulary word for each definition below:

Predefined Oracle Server Each of these has a predefined name. For example, if the error ORAErrors

01403 occurs when no rows are retrieved from the database in a SELECT statement, then PL/SQL raises the predefined exceptionname NO_DATA_FOUND.

PRAGMA

Tells the compiler to associate an exception name with an Oracle

EXCEPTION_INIT

error number. That allows you to refer to any Oracle Server exception by name and to write a specific handler for it.

SQLERRM

Returns character data containing the message associated with the error number

Non-predefined

Oracle Each of these has a standard Oracle error number (ORA-nnnnn) and

Server Errors

error message, but not a predefined name. We declare our own names for these so that we can reference these names in the exception section.

SQLCODE

Returns the numeric value for the error code (You can assign it to a NUMBER variable.)

Try It / Solve It 1. What are the three types of exceptions that can be handled in a PL/SQL block? 

predefined Oracle errors



non-predefined Oracle errors



user-defined errors

2. What is the difference in how each of these three types of exceptions is handled in the PL/SQL block? 

predefined: Oracle has assigned a name to about 20 of the most common errors. We

reference the predefined names directly in the exception section of the block;

Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE 

non-predefined: these errors do not have names and so a name must be defined in the

declare section and associated with an Oracle error number; the name is then used in the exception section; 

user defined: these are unknown to Oracle and must be named in the declare section,

raised explicitly in the executable section and then the name used in the exception section

3. Enter and run the following PL/SQL block. Look at the output and answer the following questions:

DECLARE v_number NUMBER(6,2) := 100; v_region_id wf_world_regions.region_id%TYPE; v_region_name wf_world_regions.region_name%TYPE; BEGIN SELECT region_id, region_name INTO v_region_id, v_region_name FROM wf_world_regions WHERE region_id = 1; DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id ||' is: ' || v_region_name); v_number := v_number / 0; END; A. What error message is displayed and why ?

Because region_id = 1 does not exist, the predefined exception NO_DATA_FOUND is raised but is not trapped within the block. The exception is propagated back to the calling environment (Application Express) which displays ORA-01403: no d ata found

B. Modify the block to handle this exception and re-run your code. Now what happens and why?

DECLARE v_number NUMBER(6,2) := 100; v_region_id wf_world_regions.region_id%TYPE;


v_region_name wf_world_regions.region_name%TYPE; BEGIN SELECT region_id, region_name INTO v_region_id, v_region_name FROM wf_world_regions WHERE region_id = 1; DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id ||' is: ' || v_region_name); v_number := v_number / 0; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Select returned no rows'); END; Now the exception is successfully trapped and the corresponding exception handler is executed.

C. Modify the block again to change the WHERE clause to region_id = 29. Re-run the block. Now what happens and why?

Now the SELECT executes successfully because region_id 29 exists. Block execution continues, but the next statement attempts to divide 100 by zero and raises an unhandled ORA-01476 exception.

D. Modify the block again to handle the latest exception and re-run your code.

DECLARE v_number NUMBER(6,2) := 100; v_region_id wf_world_regions.region_id%TYPE; v_region_name wf_world_regions.region_name%TYPE; BEGIN SELECT region_id, region_name INTO v_region_id, v_region_name


FROM wf_world_regions WHERE region_id = 29; DBMS_OUTPUT.PUT_LINE('Region: ' || v_region_id ||' is: ' || v_region_name); v_number := v_number / 0; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Select returned no rows'); WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero'); END;

4. Enter and run the following PL/SQL block. Look at the output and answer the following questions: DECLARE CURSOR regions_curs IS SELECT * FROM wf_world_regions WHERE region_id < 20 ORDER BY region_id; regions_rec regions_curs%ROWTYPE; v_count NUMBER(6); BEGIN LOOP FETCH regions_curs INTO regions_rec; EXIT WHEN regions_curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Region: regions_rec.region_name);

'||

regions_rec.region_id

||'

Name:

'

||


END LOOP; CLOSE regions_curs; SELECT COUNT(*) INTO v_count FROM wf_world_regions WHERE region_id = 1; DBMS_OUTPUT.PUT_LINE('The number of regions is: ' || v_count); END; A. What happens and why ?

The first FETCH fails because the cursor has not been opened. The predefined exception INVALID_CURSOR is raised but is not trapped within the b lock.

B. Modify the block to handle the exception and re-run your code.

DECLARE CURSOR regions_curs IS SELECT * FROM wf_world_regions WHERE region_id < 20 ORDER BY region_id; regions_rec regions_curs%ROWTYPE; v_count NUMBER(6); BEGIN LOOP FETCH regions_curs INTO regions_rec; EXIT WHEN regions_curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Region: regions_rec.region_name); END LOOP; CLOSE regions_curs; SELECT COUNT(*) INTO v_count FROM wf_world_regions WHERE region_id = 1;

'||


||'

Name:

'

||


DBMS_OUTPUT.PUT_LINE('The number of regions is: ' || v_count); EXCEPTION WHEN INVALID_CURSOR THEN DBMS_OUTPUT.PUT_LINE('Attempt to fetch from an unopenedcursor '); END; C. Modify the block again to add an OPEN statement for the cursor, and re-run your code. Now what happens and why? Remember that region_id = 1 does not exist.

DECLARE CURSOR regions_curs IS SELECT * FROM wf_world_regions WHERE region_id < 20 ORDER BY region_id; regions_rec regions_curs%ROWTYPE; v_count NUMBER(6); BEGIN OPEN regions_curs; LOOP FETCH regions_curs INTO regions_rec; EXIT WHEN regions_curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Region:

'||


||

'

regions_rec.region_name); END LOOP; CLOSE regions_curs; SELECT COUNT(*) INTO v_count FROM wf_world_regions WHERE region_id = 1; DBMS_OUTPUT.PUT_LINE('The number of regions is: ' || v_count); EXCEPTION WHEN INVALID_CURSOR THEN DBMS_OUTPUT.PUT_LINE('Attempt to fetch from an unopened cursor'); END;

Name:

'

||


The cursor loop now works fine, so execution continues to the SELECT COUNT(*) … Some students may expect this to fail because region_id 1 does not exist. But a SELECT COUNT(*) (unlike SELECT * …) always returns exactly one ro w, in this case with a value of 0.

5. Oracle Server Errors: A. Add an exception handler to the following code to trap the following predefined Oracle Server errors: NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX.

DECLARE v_language_id wf_languages.language_id%TYPE; v_language_name wf_languages.language_name%TYPE; BEGIN SELECT language_id, language_name INTO v_language_id, v_language_name FROM wf_languages WHERE LOWER(language_name) LIKE ''; -- for example 'ab%' INSERT INTO wf_languages(language_id, language_name) VALUES(80, null); END; Add the following exception section: EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No rows were found'); WHEN TOO_MANY_ROWS THEN


DBMS_OUTPUT.PUT_LINE('More than one row was found'); WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('This language id already exists');

B. Test your block twice using each of the following language substrings: ba, ce. There are several language_names beginning with “Ba” but none beginning with “Ce”.

Pentru ‘ba%’:

Pentru ‘ce%’:

C. Now test your block a third time using substring: al. There is exactly one language_name beginning with “Al”. Note that language_id 80 (Arabic) already exists. Explain the output.

The SELECT succeeds but the INSERT fails with an ORA-01400 exception because language_name cannot be null.

D. Now (keeping the substring as “al”) add a non_predefined exception handler to trap the ORA-01400 exception. Name your exception e_null_not_allowed. Rerun the code and observe the results.

DECLARE v_language_id wf_languages.language_id%TYPE; v_language_name wf_languages.language_name%TYPE; e_null_not_allowed EXCEPTION; PRAGMA EXCEPTION_INIT(e_null_not_allowed, -01400);


BEGIN SELECT language_id, language_name INTO v_language_id, v_language_name FROM wf_languages WHERE lower(language_name) LIKE ‘al%’; INSERT INTO wf_languages(language_id, language_name) VALUES(80, null); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No rows were found'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one row was found'); WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('This language id already exists'); WHEN e_null_not_allowed THEN DBMS_OUTPUT.PUT_LINE('Language name cannot be null'); END;

Extension exercise 1. In preparation for this exercise, run the following SQL statement to create an errorlogging table:

CREATE TABLE error_log (who VARCHAR2(30), when DATE, error_code NUMBER(6), error_message VARCHAR2(255)); Modify your PL/SQL block from question 5 to remove the four explicit exception handlers, replacing them with a single WHEN OTHERS handler. The handler should INSERT a row into


the error_log table each time an exception is raised and handled. The row should consist of the Oracle username (who), when the error was raised (when), and the SQLCODE and SQLERRM of the exception. Test your block several times, with different data values to raise each of the four kinds of exceptions handled in the block. Finally, SELECT from the error-logging table to check that the rows have been inserted. DECLARE v_language_id wf_languages.language_id%TYPE; v_language_name wf_languages.language_name%TYPE; v_sqlcode NUMBER(5); v_sqlerrm VARCHAR2(255); BEGIN SELECT language_id, language_name INTO v_language_id, v_language_name FROM wf_languages WHERE lower(language_name) LIKE ''; INSERT INTO wf_languages(language_id, language_name) VALUES(80, null); EXCEPTION WHEN OTHERS THEN v_sqlcode := SQLCODE; v_sqlerrm := SQLERRM; INSERT INTO error_log(who, when, error_code, error_message) VALUES(USER, SYSDATE, v_sqlcode, v_sqlerrm); END; SELECT * FROM error_log;

Dupa mai multe verificari de coduri am mai facut un SELECT:



Test Quiz: Trapping Oracle Server Exceptions Section 1


Which of the following is NOT a predefined Oracle Server error?


TOO_MANY_ROWS ZERO_DIVIDE DUP_VAL_ON_INDEX NO_DATA_FOUND e_sal_too_high EXCEPTION; (*) Correct 2.

Which of the following best describes a predefined Oracle Server error?


Is not raised automatically but must be declared and raised explicitly by the PL/SQL programmer Is associated with an Oracle error number using PRAGMA EXCEPTION_INIT Has a standard Oracle error number but must be declared and named by the PL/SQL programmer Has a standard Oracle error number and a standard name which can be referenced in the EXCEPTION section (*) Correct 3.

Which kind of error can NOT be handled by PL/SQL?


Non-predefined Oracle Server errors Syntax errors (*) Predefined Oracle Server errors User-defined errors Correct 4.

Examine the following code. At Line A, you want to raise an exception if the employee's manager_id is null. What kind of exception is this? DECLARE v_mgr_id employees.manager_id%TYPE; BEGIN SELECT manager_id INTO v_mgr_id FROM employees WHERE employee_id = 100; IF v_mgr_id IS NULL THEN -- Line A END IF; ...


Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE A NO_DATA_FOUND exception A non-predefined Oracle server exception A constraint violation A user-defined exception (*) A predefined Oracle Server exception Correct 5.

How would you trap Oracle Server exception ORA-01403: no data found?


WHEN NO_DATA_FOUND THEN ... (*) WHEN SQL%ROWCOUNT=0 THEN ... WHEN NO DATA FOUND THEN ... WHEN ORA-01403 THEN ... Correct 6.

No employees exist whose salary is less than 2000. Which exception handlers would successfully trap the exception that will be raised when the following code is executed? (Choose two.)


DECLARE v_mynum NUMBER := 10; v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE salary < 2000; v_mynum := v_mynum / v_count; EXCEPTION ... END; (Choose all correct answers) NO_DATA_FOUND ZERO_DIVIDE (*) SQL%ROWCOUNT = 0 OTHER OTHERS (*) Correct 7.

What is the correct syntax to associate an exception named EXCEPNAME with the non-predefined Oracle Server error ORA-02292? SQLCODE (-2292, excepname); RAISE_APPLICATION_ERROR (-2292, excepname); PRAGMA EXCEPTION_INIT (excepname, -2292) (*)


Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE WHEN (-2292, excepname) THEN ﾅ Correct 8. An

ORA-1400 exception is raised if an attempt is made to insert a null value into a NOT NULL column. DEPARTMENT_ID is the primary key of the DEPARTMENTS table. What will happen when the following code is executed?


DECLARE e_not_null EXCEPTION; BEGIN PRAGMA EXCEPTION_INIT(e_not_null, -1400); INSERT INTO departments (department_id, department_name) VALUES(null, 'Marketing'); EXCEPTION WHEN e_not_null THEN DBMS_OUTPUT.PUT_LINE('Cannot be null'); END; The code will not execute because the syntax of PRAGMA EXCEPTION_INIT is wrong. The code will not execute because the syntax of the INSERT statement is wrong. The exception will be raised and "Cannot be null" will be displayed. The code will not execute because PRAGMA EXCEPTION_INIT must be coded in the DECLARE section. (*) Correct 9.

Examine the following code. The UPDATE statement will raise an ORA-02291 exception.


BEGIN UPDATE employees SET department_id = 45; EXCEPTION WHEN OTHERS THEN INSERT INTO error_log_table VALUES (SQLCODE); END; What will happen when this code is executed? The code will fail because we cannot use functions like SQLCODE directly in a SQL statement. (*) The code will fail because we access error message numbers by using SQLERRNUM, not SQLCODE. The code will fail because SQLCODE has not been declared. The code will execute and insert error number 02291 into error_log_table. Correct 10.

Which type of exception MUST be explicitly raised by the PL/SQL programmer?

User-defined exceptions (*) Predefined Oracle server errors such as TOO_MANY_ROWS Non-predefined Oracle server errors such as ORA-01203


Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE All of the above Correct 11. A

PL/SQL block executes and an Oracle Server exception is raised. Which of the following contains the text message associated with the exception?


SQLERRM (*) SQL_MESSAGE_TEXT SQLCODE SQL%MESSAGE Correct 12.

Which one of the following events would implicitly raise an exception?

A database constraint is violated. (*) An UPDATE statement modifies no rows. A SELECT statement returns exactly one row. The PL/SQL programmer mis-spells the word BEGIN as BEGAN. Correct



Section 7 Lesson 3 Trapping User-Defined Exceptions Vocabulary Identify the vocabulary word for each definition below: RAISE_APPLICATIO

A procedure used to return user-defined error messages from

N_ERROR

stored subprograms.

RAISE

Use this statement to raise a named exception.

user-defined errors

These errors are not automatically raısed by the Oracle Server, but are defined by the programmer and are specific to the programmer's code.

Try It / Solve It All the questions in this exercise use a copy of the employees table. Create this copy by running the following SQL statement: CREATE TABLE excep_emps AS SELECT * FROM employees; 1. Create a PL/SQL block that updates the salary of every employee to a new value of 10000 in a chosen department. Include a user-defined exception handler that handles the condition where no rows are updated and displays a custom message. Also include an exception handler that will trap any other possible error condition and display the corresponding

SQLCODE

and

SQLERRM.

Test

your

code

three

times,

department_ids 20, 30, and 40.

DECLARE e_no_rows_updated EXCEPTION; BEGIN UPDATE excep_emps SET salary = 10000 WHERE department_id = ; IF SQL%NOTFOUND THEN -- sau putem spune: IF SQL%ROWCOUNT = 0 RAISE e_no_rows_updated; END IF; EXCEPTION

using


WHEN e_no_rows_updated THEN DBMS_OUTPUT.PUT_LINE ( 'There are no employees in that department.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error has occurred:'||SQLCODE || '-' || SQLERRM); END; Pentru department_id = 20:

Pentru department_id = 30:

Pentru department_id = 40:

2. Modify your code from question 2 to handle the condition where no rows are updated using RAISE_APPLICATION_ERROR procedure in the exception section. Use an error number of – 20202. Test your code again using department_id 40 and check that the – 20202 error is displayed.

DECLARE e_no_rows_updated EXCEPTION; BEGIN UPDATE excep_emps SET salary = 10000


WHERE department_id = 40; IF SQL%NOTFOUND THEN RAISE e_no_rows_updated; END IF; EXCEPTION WHEN e_no_rows_updated THEN RAISE_APPLICATION_ERROR(-20202,'There are no employees in that dep artment'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error has occurred: '||SQLCODE || '-' || SQLERRM); END;

3. Modify your code from question 3 to use RAISE_APPLICATION_ERROR in the executable section instead of the exception section. Test your code again using department_id 40.

DECLARE _no_rows_updated EXCEPTION; BEGIN UPDATE excep_emps SET salary = 10000 WHERE department_id = 40; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202,'There are no employees in that dep artment'); END IF; END;

4. Before starting this question, disable Autocommit in Application Express. A. Enter and run the following PL/SQL block using department_id = 40, and explain the output.

DECLARE v_dept_id excep_emps.department_id%TYPE;


v_count NUMBER; BEGIN v_dept_id := 40; SELECT COUNT(*) INTO v_count FROM excep_emps WHERE department_id = v_dept_id; DBMS_OUTPUT.PUT_LINE('There are ' || v_count || ' employees'); DELETE FROM excep_emps WHERE department_id = v_dept_id; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||' employees were deleted'); ROLLBACK; END; Both the SELECT and the DELETE execute successfully (no exceptions are raised) although there are no employees in department 40.

B. Modify your block to include two user-defined exception handlers, one to test whether SELECT returns a value of 0, and the other to test if no rows were DELETEd. Declare the exceptions and RAISE them explicitly before trapping them in the exception section. Do NOT

use

RAISE_APPLICATION_ERROR.

Test

department_id 40.

DECLARE v_dept_id excep_emps.department_id%TYPE; v_count NUMBER; e_no_emps_in_dept EXCEPTION; e_no_rows_deleted EXCEPTION; BEGIN v_dept_id := 40;

your

modified

block

using


SELECT COUNT(*) INTO v_count FROM excep_emps WHERE department_id = v_dept_id; IF v_count = 0 THEN RAISE e_no_emps_in_dept; END IF; DBMS_OUTPUT.PUT_LINE('There are ' || v_count || ' emplo yees'); DELETE FROM excep_emps WHERE department_id = v_dept_id; IF SQL%NOTFOUND THEN -- or IF SQL%ROWCOUNT = 0 THEN RAISE e_no_rows_deleted; END IF; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||' employees were deleted'); ROLLBACK; EXCEPTION WHEN e_no_emps_in_dept THEN DBMS_OUTPUT.PUT_LINE('This department has no employees'); WHEN e_no_rows_deleted THEN DBMS_OUTPUT.PUT_LINE('No employees were deleted'); END;

C. Modify your block again to use RAISE_APPLICATION_ERROR in the executable section. Use error numbers – 20203 and – 20204. Test your modified block using department_id 40.

DECLARE v_dept_id excep_emps.department_id%TYPE; v_count NUMBER; BEGIN


v_dept_id := 40; SELECT COUNT(*) INTO v_count FROM excep_emps WHERE department_id = v_dept_id; IF v_count = 0 THEN RAISE_APPLICATION_ERROR(-20203,'This department has no employees'); END IF; DBMS_OUTPUT.PUT_LINE('There are ' || v_count || ' emplo yees'); DELETE FROM excep_emps WHERE department_id = v_dept_id; IF SQL%NOTFOUND THEN -- sau IF SQL%ROWCOUNT = 0 THEN RAISE_APPLICATION_ERROR(-20204,'No employees were deleted'); END IF; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||' employees were deleted'); ROLLBACK; END;


Test Quiz: Trapping User-Defined Exceptions Section 1


What is a user-defined exception?


A predefined Oracle server exception such as NO_DATA_FOUND. An exception which has a predefined Oracle error number but no predefined name. An exception which is not raised automatically by the Oracle server, but must be declared and raised explicitly by the PL/SQL programmer. (*) An exception handler which the user (the programmer) includes in the EXCEPTION section. Correct 2.

What is the datatype of a user-defined exception?


BOOLEAN VARCHAR2 EXCEPTION (*) NUMBER None of the above Correct 3.

What is wrong with the following code? BEGIN UPDATE employees SET salary = 20000 WHERE job_id = 'CLERK'; IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; -- Line A END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee was updated'); END;


Line A should be: HANDLE NO_DATA_FOUND You cannot use SQL%ROWCOUNT in conditional control statements such as IF or CASE. Nothing is wrong; the code will execute correctly. (*) NO_DATA_FOUND has not been DECLAREd. You cannot explicitly raise predefined Oracle Server errors such as NO_DATA_FOUND. Correct 4.

What will be displayed when the following code is executed?

Mark for Review

Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE (1) Points DECLARE e_myexcep EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Message 1'); RAISE e_myexcep; DBMS_OUTPUT.PUT_LINE('Message 2'); EXCEPTION WHEN e_myexcep THEN DBMS_OUTPUT.PUT_LINE('Message 3'); RAISE e_myexcep; DBMS_OUTPUT.PUT_LINE('Message 4'); END; The code will not execute because it contains at least one syntax error. The code will execute but will return an unhandled exception to the calling environment. (*) Message 1 Message 2 Message 3 Message 4 Message 1 Message 3 Message 4 Message 1 Message 3 Correct 5.

The following line of code is correct. True or False? RAISE_APPLICATION_ERROR(-21001,'My error message');


True False (*) Correct 6.

How are user-defined exceptions raised ?


By PRAGMA EXCEPTION_INIT By DECLARE e_my_excep EXCEPTION; By RAISE exception_name; (*) None of the above. They are raised automatically by the Oracle server. Correct 7.

The following three steps must be performed to use a user-defined exception: - Raise the exception - Handle the exception - Declare the exception In what sequence must these steps be performed? The steps can be performed in any order.


Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE Declare, Raise, Handle (*) Handle, Raise, Declare Raise, Handle, Declare Correct 8. You

want to display your own error message to the user. What is the correct syntax to do this? RAISE application_error; RAISE_APPLICATION_ERROR('My own message', -20 001); RAISE_APPLICATION_ERROR(20001, 'My own message'); RAISE_APPLICATION_ERROR (-20001, 'My own message'); (*) Correct



Section 7 Lesson 4 Recognizing the Scope of Exceptions Vocabulary Identify the vocabulary word for each definition below: Propogation

The inner block terminates unsuccessfully, and PL/SQL passes the exception to the outer block.

Exception scope

The portion of a program in which the exception is declared and is accessible.

Try It / Solve It 1. Enter and run the following code twice, once for each of the two country_ids 5 (which does not exist) and 672 (Antarctica, which does exist but has no currency).

DECLARE v_country_name wf_countries.country_name%TYPE; v_currency_code wf_countries.currency_code%TYPE; BEGIN DECLARE e_no_currency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_country_name, v_currency_code FROM wf_countries WHERE country_id = 5; -- repeat with 672 IF v_currency_code = 'NONE' THEN RAISE e_no_currency; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('This country does not exist'); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE('This country exists but has no currency');


END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Another type of error occurred'); END; A. Explain the output. Save your code.

Country_id 5 raises NO_DATA_FOUND and ‘This country does not exist’ is displayed.

Country_id 672 raises e_no_currency and ‘This c ountry exists but has no currency’ is displayed.

B. Modify the code to move the two exception handlers to the outer block. Leave the declaration of e_no_currency in the inner block. Execute twice, again using country_ids 5 and 672. Now what happens and why? Save your code.

DECLARE v_country_name wf_countries.country_name%TYPE; v_currency_code wf_countries.currency_code%TYPE; BEGIN DECLARE e_no_currency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_country_name, v_currency_code FROM wf_countries WHERE country_id = 5; -- repeat with 672 IF v_currency_code = 'NONE' THEN


RAISE e_no_currency; END IF; END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('This country does not exist'); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE('This country exists but has no currency'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Another type of error occurred'); END; In both executions, a PLS-00201 “Identifier E_NO_CURRENCY must be declared” e rror occurs, because e_no_currency was declared in the inner block and is therefore out of scope in the outer block. We would expect this for country_id 672, but what about country_id 5, which should raise a NO_DATA_FOUND? PLS-00201 is a compile-time error. This shows that PL/SQL checks the scope of variables when compiling the code, not when executing it. If invalid scope is detected, the code is not executed at all.

C. Modify the code again to move the declaration of e_no_currency to the outer block. Reexecute again using country_ids 5 and 672. Now what happens and why?

DECLARE v_country_name wf_countries.country_name%TYPE; v_currency_code wf_countries.currency_code%TYPE; e_no_currency EXCEPTION; BEGIN


BEGIN SELECT country_name, currency_code INTO v_country_name, v_currency_code FROM wf_countries WHERE country_id = 5; -- repeat with 672 IF v_currency_code = 'NONE' THEN RAISE e_no_currency; END IF; END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('This country does not exist'); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE('This country exists but has no currency'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Another type of error occurred'); END; Pentru country_id = 5 :

Pentru country_id = 672 :


Test Quiz: Recognizing the Scope of Exceptions Section 1


Predefined Oracle Server exceptions such as NO_DATA_FOUND can be raised automatically in inner blocks and handled in outer blocks. True or False?



Non-predefined Oracle Server errors (associated with Oracle error numbers by PRAGMA EXCEPTION_INIT) can be declared and raised in inner blocks and handled in outer blocks. True or False?


True False (*) Correct 3.

What will happen when the following code is executed? DECLARE e_outer_excep EXCEPTION; BEGIN DECLARE e_inner_excep EXCEPTION; BEGIN RAISE e_outer_excep; END; EXCEPTION WHEN e_outer_excep THEN DBMS_OUTPUT.PUT_LINE('Outer raised'); WHEN e_inner_excep THEN DBMS_OUTPUT.PUT_LINE('Inner raised'); END;


The code will execute successfully and 'Outer Raised' will be displayed. The code will propagate the e_outer_excep back to the calling environment (Application Express). The code will fail to compile because e_inner_excep cannot be referenced in the outer block. (*) The code will fail to compile because e_inner_excep was declared but never RAISEd. Correct 4.

What will happen when the following code is executed? DECLARE e_excep1 EXCEPTION; e_excep2 EXCEPTION; BEGIN RAISE e_excep1; EXCEPTION WHEN e_excep1 THEN BEGIN


Student: Paun Mihut Catalin Master: SIMR Anul: II Materia: DEZVOLTAREA APLICAŢIILOR ORACLE RAISE e_excep2; END; END; It will compile successfully and return an unhandled e_excep2 to the calling environment. (*) It will fail to compile because you cannot declare more than one exception in the same block. It will fail to compile because e_excep1 is out of scope in the subblock. It will fail to compile because you cannot have a subblock inside an exception section. Correct 5.

There are three employees in department 90. What will be displayed when this code is executed?


DECLARE v_last_name employees.last_name%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Message 1'); BEGIN SELECT last_name INTO v_last_name FROM employees WHERE department_id = 90; DBMS_OUTPUT.PUT_LINE('Message 2'); END; DBMS_OUTPUT.PUT_LINE('Message 3'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Message 4'); END; Message 1 Message 3 Message 4 Message 1 Message 4 (*) Message 1 An unhandled exception will be propagated back to the calling environment. None of the above Correct 6.

What will be displayed when the following code is executed? <> DECLARE v_myvar NUMBER; BEGIN v_myvar := 25; DECLARE v_myvar NUMBER := 100; BEGIN outer.v_myvar := 30; v_myvar := v_myvar / 0; outer.v_myvar := 35;


Paun Mihut Catalin Tema 5

Recommend Documents