Database Programming with PL/SQL 7-4: Practice Activities

www.oracle.com/academy

Database Programming with PL/SQL 7-4: Recognizing the Scope of Exceptions Practice Activities Vocabulary Identify the vocabulary word for each definition below:

Propagacion de Propagacion excepciones Visibili dad de excepciones  AMB ITO DE  AMBITO EXCEPCIONES

The inner block terminates unsuccessfully, and PL/SQL passes the exception to the outer block. The portion of the program where the exception can be accessed without using a qualifier. The portion of a program in which the exception is declared and is accessible.

Try It / Solve It Enter and run the foll owing code twice, once for each each of the two coun try_ids, 5 1. Enter (which do es not exist) and 672 (Antarctica, which d oes exist but has no cu rrency). DECLARE v_country_name countries.country_name%TYPE; v_currency_code countries.currency_code%TYPE; BEGIN DECLARE DECLARE e_no_cu rrency rr ency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_country_name, v_currency_code FROM countries WHERE WHERE cou ntr y_id = 5; -- repeat with wit h 672 672 IF v_currency_code = 'NONE' THEN RAISE RAISE e_no_cur e_no_cur rency; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_O DBMS_OUT UTPU PUT. T.PU PUT_ T_LINE LINE('This ('This countr cou ntr y does d oes n ot exist '); WHEN e_no_currency THEN

DBMS_OUTPUT.PUT_LINE('This countr y exist s but has no cur rency' ); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Anot her typ e of error occurr ed'); END; 2

 A. Explain the out put. Save your code. El id 5, aparece el mensaje de que no existe el país y con el id 672 se muestra un mensaje de que el país existe pero la moneda no.

B. Modify the cod e to move the two exception handl ers to the outer bl ock. Leave the declaration of e_no_curr ency in the inner bl ock. Execute twice, again using count ry_ids 5 and 672. Now what happens and why? Save your code.

DECLARE v_pais_nom countries.country_name%TYPE; v_currency countries.currency_code%TYPE; BEGIN DECLARE e_no_currency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_pais_nom, v_currency FROM countries WHERE coun try_id = 5; IF v_currency = 'NONE' THEN RAISE e_no_currency; END IF; END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘el pais no exist e ’ ); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE(‘Este país existe pero no tiene moneda ’); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘otro tipo de error ocurrio ’); END;

C. Modify the code again to move the declaration of e_no_curr ency to the outer bl ock. Execute the co de again using countr y_ids 5 and 672. Now what happens and why?

DECLARE v_pais_nom countries.country_name%TYPE; v_currency countries.currency_code%TYPE; e_no_currency EXCEPTION; BEGIN SELECT country_name, currency_code INTO v_pais_nom, v_currency FROM countries WHERE coun try_id = 5; IF v_currency = 'NONE' THEN RAISE e_no_currency; END IF; END; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘el pais no exist e ’ ); WHEN e_no_currency THEN DBMS_OUTPUT.PUT_LINE(‘Este país existe pero no tiene moneda ’); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘otro tipo de error ocurrio ’); END;