<<outer_block>>
BEGIN
DBMS_OUTPUT.PUT_LINE('----------1ST EXAMPLE STARTS HERE----------');
<<first_example_block>>
DECLARE
v_emp_no number;
PROCEDURE DIAGNOSTICS_USING_MESSAGES_FNC(p_level NUMBER,stmt varchar2)
IS
BEGIN
IF p_level=1 THEN
DBMS_OUTPUT.PUT_LINE(stmt);
END IF;
END;
BEGIN
DIAGNOSTICS_USING_MESSAGES_FNC(1,'Before Running 1st SQL Validation');
select emp_no into v_emp_no from employees where emp_NAME='Jane Doe';
DIAGNOSTICS_USING_MESSAGES_FNC(1,'Before Running 2nd SQL Validation');
select emp_no into v_emp_no from employees where emp_NAME='Jacky Do';
DIAGNOSTICS_USING_MESSAGES_FNC(1,'Validations completed');
EXCEPTION
WHEN OTHERS THEN
DIAGNOSTICS_USING_MESSAGES_FNC(1,SQLERRM);
END;
DBMS_OUTPUT.PUT_LINE('----------2ND EXAMPLE STARTS HERE----------');
<<second_example_block>>
DECLARE
v_emp_no number;
PROCEDURE DIAGNOSTICS_USING_MESSAGES_FNC(p_level NUMBER,stmt varchar2)
IS
BEGIN
IF p_level=1 THEN
DBMS_OUTPUT.PUT_LINE(stmt);
END IF;
END;
BEGIN
select emp_no into v_emp_no from employees where emp_NAME='Jane Doe';
select emp_no into v_emp_no from employees where emp_NAME='Jacky Do';
exception
when others then
DIAGNOSTICS_USING_MESSAGES_FNC(1,'ERROR MESSAGE: '||SQLERRM||CHR(10)||'Backtrace : '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
END;