Oh My That's a Big String!

DECLARE 
   aname   VARCHAR2 (5); 
BEGIN 
   BEGIN 
      aname := 'Big String'; 
      DBMS_OUTPUT.put_line (aname); 
   EXCEPTION 
      WHEN VALUE_ERROR 
      THEN 
         DBMS_OUTPUT.put_line ('Inner block'); 
   END; 
 
   DBMS_OUTPUT.put_line (SQLCODE); 
    
   DBMS_OUTPUT.put_line ('What error?'); 
EXCEPTION 
   WHEN VALUE_ERROR 
   THEN 
      DBMS_OUTPUT.put_line ('Outer block'); 
END;

That's STILL a Big String!

DECLARE 
   aname VARCHAR2(5); 
BEGIN 
   DECLARE 
      aname VARCHAR2(5) := 'Big String'; 
   BEGIN 
      DBMS_OUTPUT.PUT_LINE (aname); 
 
   EXCEPTION 
      WHEN VALUE_ERROR 
      THEN 
         DBMS_OUTPUT.PUT_LINE ('Inner block'); 
   END; 
    
   DBMS_OUTPUT.put_line (SQLCODE); 
   DBMS_OUTPUT.PUT_LINE ('What error?'); 
EXCEPTION 
   WHEN VALUE_ERROR 
   THEN 
      DBMS_OUTPUT.PUT_LINE ('Outer block'); 
END; 

CREATE TABLE employees AS SELECT * FROM hr.employees

No Room for Justice in This World (Block)

DECLARE 
   aname VARCHAR2(5); 
BEGIN 
   DECLARE 
      aname VARCHAR2(5) := 'Justice'; 
      loc INTEGER := 1; 
   BEGIN 
      loc := 2; 
      DBMS_OUTPUT.PUT_LINE (aname); 
 
      loc := 3; 
      DELETE FROM employees; 
 
   EXCEPTION 
      WHEN VALUE_ERROR 
      THEN 
         DBMS_OUTPUT.PUT_LINE ('Got as far as ' || loc); 
         ROLLBACK; 
   END; 
 
   DBMS_OUTPUT.PUT_LINE ('What error?'); 
   ROLLBACK; 
EXCEPTION 
   WHEN VALUE_ERROR 
   THEN 
      DBMS_OUTPUT.PUT_LINE ('Outer block'); 
      ROLLBACK; 
END;

Did You Know You Can Name Nested Blocks?

BEGIN 
   <<outer>> 
   DECLARE 
      aname   VARCHAR2 (5); 
   BEGIN 
      <<inner>> 
      DECLARE 
         aname   VARCHAR2 (20); 
      BEGIN 
         OUTER.aname := 'Big String'; 
      EXCEPTION 
         WHEN VALUE_ERROR 
         THEN 
            RAISE NO_DATA_FOUND; 
			 
         WHEN NO_DATA_FOUND 
         THEN 
            DBMS_OUTPUT.put_line ('Inner block'); 
      END INNER; 
   EXCEPTION 
      WHEN NO_DATA_FOUND 
      THEN 
         DBMS_OUTPUT.put_line ('Outer block'); 
   END OUTER; 
END; 

And the Winner Is....

DECLARE 
   v_totsal NUMBER; 
   v_ename employees.last_name%TYPE; 
BEGIN 
   /* There are no rows with department_id = -15 */ 
   SELECT SUM (salary) INTO v_totsal 
     FROM employees  
    WHERE department_id = -15; 
 
   DBMS_OUTPUT.PUT_LINE ('Total salary: ' || v_totsal); 
 
   SELECT last_name INTO v_ename 
     FROM employees 
    WHERE salary = 
       (SELECT MAX (salary) 
          FROM employees WHERE department_id = -15); 
           
   DBMS_OUTPUT.PUT_LINE ( 
      'The winner is: ' || v_ename); 
EXCEPTION 
   WHEN NO_DATA_FOUND 
   THEN 
      DBMS_OUTPUT.PUT_LINE ('Outer block'); 
END; 

My Head Hurts

DECLARE 
   PLS_INTEGER    VARCHAR2 (1); 
   NO_DATA_FOUND   EXCEPTION; 
BEGIN 
   SELECT dummy 
     INTO PLS_INTEGER 
     FROM DUAL 
    WHERE 1 = 2; 
 
   IF PLS_INTEGER IS NULL 
   THEN 
      RAISE NO_DATA_FOUND; 
   END IF; 
EXCEPTION 
   WHEN NO_DATA_FOUND 
   THEN 
      DBMS_OUTPUT.put_line ('No dummy!'); 
END;