2011-10-21 capture of custom exceptions
Http://www.itpub.net/thread-1499223-7-1.html
70th floor
Which of the following blocks will display after execution: "Trapped!"
Note: Oracle throws an ORA-01858 error when you try to convert the string "13-DEC-2004 6:56 PM" to a date using the mask "DD-MON-YYYY HH:MI:SS".
(A)
DECLARE E1 EXCEPTION; PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); EXCEPTION WHEN E1 THEN DBMS_OUTPUT.put_line ('THEN DBMS_OUTPUT.put_line'); END;/SQL > DECLARE 2 E1 EXCEPTION; 3 PRAGMA EXCEPTION_INIT (E1,-1858) 4 BEGIN 5 DBMS_OUTPUT.put_line (6 TO_DATE ('13-DEC-2004 6:56 PM' 7,' DD-MON-YYYY HH:MI:SS')); 8 EXCEPTION 9 WHEN E1 10 THEN 11 DBMS_OUTPUT.put_line ('THEN 11'); 12 END; 13 / DECLARE E1 EXCEPTION; PRAGMA EXCEPTION_INIT (E1,-1858) BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); EXCEPTION WHEN E1 THEN DBMS_OUTPUT.put_line ('Trappeduncle'); END;ORA-01843: invalid month ORA-06512: in line 5SQL >
(B)
DECLARE E1 EXCEPTION; PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DECLARE e2 EXCEPTION; PRAGMA EXCEPTION_INIT (e2,-1858); BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); END;EXCEPTION WHEN E1 THEN DBMS_OUTPUT.put_line ('pedagogic'); END / SQL > DECLARE 2 E1 EXCEPTION; 3 PRAGMA EXCEPTION_INIT (E1,-1858); 4 BEGIN 5 DECLARE 6 e2 EXCEPTION; 7 PRAGMA EXCEPTION_INIT (e2,-1858); 8 BEGIN 9 DBMS_OUTPUT.put_line (10 TO_DATE ('13-DEC-2004 6:56 PM' 11,' DD-MON-YYYY HH:MI:SS')); 12 END 13 EXCEPTION 14 WHEN E1 15 THEN 16 DBMS_OUTPUT.put_line ('END; 18 / DECLARE E1 EXCEPTION; PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DECLARE e2 EXCEPTION; PRAGMA EXCEPTION_INIT (e2,-1858) BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); END;EXCEPTION WHEN E1 THEN DBMS_OUTPUT.put_line ('Trappeduncle'); END;ORA-01843: invalid month ORA-06512: in line 9SQL >
(C)
DECLARE E1 EXCEPTION; PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DECLARE e2 EXCEPTION; PRAGMA EXCEPTION_INIT (e2,-1858); BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); END;EXCEPTION WHEN e2 THEN DBMS_OUTPUT.put_line ('pedagogy'); END / SQL > DECLARE 2 E1 EXCEPTION; 3 PRAGMA EXCEPTION_INIT (E1,-1858); 4 BEGIN 5 DECLARE 6 e2 EXCEPTION; 7 PRAGMA EXCEPTION_INIT (e2,-1858); 8 BEGIN 9 DBMS_OUTPUT.put_line (10 TO_DATE ('13-DEC-2004 6:56 PM' 11,' DD-MON-YYYY HH:MI:SS')); 12 END 13 EXCEPTION 14 WHEN e2 15 THEN 16 DBMS_OUTPUT.put_line ('DECLARE pedagogy'); 17 END; 18 / DECLARE E1 EXCEPTION; PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DECLARE e2 EXCEPTION; PRAGMA EXCEPTION_INIT (e2,-1858) BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); END;EXCEPTION WHEN e2 THEN DBMS_OUTPUT.put_line ('Trappeduncle'); END ORA-06550: line 14, column 9: PLS-00201: the identifier 'E2'ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminatedSQL > must be declared
(D)
DECLARE E1 EXCEPTION;BEGIN DECLARE PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); END;EXCEPTION WHEN E1 THEN DBMS_OUTPUT.put_line ('END;EXCEPTION WHEN E1 EXCEPTION'); END;/SQL > DECLARE 2 E1 EXCEPTION 3 BEGIN 4 DECLARE 5 PRAGMA EXCEPTION_INIT (E1,-1858); 6 BEGIN 7 DBMS_OUTPUT.put_line (8 TO_DATE ('13-DEC-2004 6:56 PM' 9,' DD-MON-YYYY HH:MI:SS')); 10 END; 11 EXCEPTION 12 WHEN E1 13 THEN 14 DBMS_OUTPUT.put_line ('DD-MON-YYYY HH:MI:SS''); 15 END 16 / DECLARE E1 EXCEPTION;BEGIN DECLARE PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); END;EXCEPTION WHEN E1 THEN DBMS_OUTPUT.put_line ('Trappeduncle'); END ORA-06550: line 2, column 4: PRAGMA EXCEPTION_INIT for PLS-00700: 'E1' must be after the exception error declaration in the same declaration section ORA-06550: line 5, column 7: PL/SQL: Item ignoredSQL >
There are differences in the results. Rerun after setting the language to English.
SQL > alter session set nls_language='american';Session alteredSQL > SQL > DECLARE 2 E1 EXCEPTION; 3 PRAGMA EXCEPTION_INIT (E1,-1858); 4 BEGIN 5 DBMS_OUTPUT.put_line (6 TO_DATE ('13-DEC-2004 6:56 PM' 7,' DD-MON-YYYY HH:MI:SS')); 8 EXCEPTION 9 WHEN E1 10 THEN 11 DBMS_OUTPUT.put_line ('DD-MON-YYYY HH:MI:SS''); 12 END 13 / DECLARE procedure successfully completedSQL > SQL > alter session set nls_language='american';Session alteredSQL > SQL > SQL > SQL 2 E1 EXCEPTION; 3 PRAGMA EXCEPTION_INIT (E1,-1858); 4 BEGIN 5 DECLARE 6 e2 EXCEPTION; 7 PRAGMA EXCEPTION_INIT (e2,-1858) 8 BEGIN 9 DBMS_OUTPUT.put_line (10 TO_DATE ('13-DEC-2004 6:56 PM' 11,' DD-MON-YYYY HH:MI:SS')); 12 END; 13 EXCEPTION 14 WHEN e1 15 THEN 16 DBMS_OUTPUT.put_line ('END; THEN 16 DBMS_OUTPUT.put_line'); 17 END; 18 / TrappedSQL PL procedure successfully completedSQL > SQL > alter session set nls_language='american' Session alteredSQL > SQL > SQL > DECLARE 2 E1 EXCEPTION; 3 PRAGMA EXCEPTION_INIT (E1,-1858); 4 BEGIN 5 DECLARE 6 e2 EXCEPTION; 7 PRAGMA EXCEPTION_INIT (e2,-1858); 8 BEGIN 9 DBMS_OUTPUT.put_line (10 TO_DATE ('13-DEC-2004 6:56 PM' 11,' DD-MON-YYYY HH:MI:SS')); 12 END 13 EXCEPTION 14 WHEN e2 15 THEN 16 DBMS_OUTPUT.put_line ('DECLARE pedagogy'); 17 END; 18 / DECLARE E1 EXCEPTION; PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DECLARE e2 EXCEPTION; PRAGMA EXCEPTION_INIT (e2,-1858) BEGIN DBMS_OUTPUT.put_line (TO_DATE ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS')); END;EXCEPTION WHEN e2 THEN DBMS_OUTPUT.put_line ('Trappeduncle'); END;ORA-06550: line 14, column 9:PLS-00201: identifier 'E2' must be declaredORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis terminatedSQL > SQL > alter session set nls_language='american' Session alteredSQL > SQL > SQL > DECLARE 2 E1 EXCEPTION; 3 BEGIN 4 DECLARE 5 PRAGMA EXCEPTION_INIT (E1,-1858); 6 BEGIN 7 DBMS_OUTPUT.put_line (8 TO_DATE ('13-DEC-2004 6:56 PM' 9,' DD-MON-YYYY HH:MI:SS')); 10 END 11 EXCEPTION 12 WHEN E1 13 THEN 14 DBMS_OUTPUT.put_line ('TO_DATE'); 15 END; 16 / DECLARE E1 EXCEPTION;BEGIN DECLARE PRAGMA EXCEPTION_INIT (E1,-1858); BEGIN DBMS_OUTPUT.put_line ('13-DEC-2004 6:56 PM',' DD-MON-YYYY HH:MI:SS'); END EXCEPTION WHEN E1 THEN DBMS_OUTPUT.put_line ('Trappedaling'); END;ORA-06550: line 2, column 4:PLS-00700: PRAGMA EXCEPTION_INIT of E1 must follow declaration of its exception in the same declarative partORA-06550: line 5, column 7:PL/SQL: Item ignoredSQL >
Answer AB (if the language is set to English, then the answer AB; is incorrect if the language is Chinese)
The answer says the 74th floor
A: correctly catch-1858 exception B: the inner layer also defines-1858 but does not capture, so the outer layer can still capture C: the definition of the inner layer is not visible in the outer layer, it will be reported that PLS-00201: identifier 'E2' must be declaredD: PRAGMA EXCEPTION_INIT must be defined in the same layer DECLARE as EXCEPTION: PLS-00700: PRAGMA EXCEPTION_INIT of E1 must follow declaration of its exception in the same declarative part
