Advanced Exceptions Database Programming Using Oracle 11g Advanced Exceptions RaiseApplicationError Built in procedure It let user attached a ora number and customized message to user Terminate any plsql block and behave like predefine ID: 769204
Download Presentation The PPT/PDF document "Advanced Exceptions" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Advanced Exceptions Database Programming Using Oracle 11g
Advanced Exceptions:Raise_Application_ErrorBuilt in procedure It let user attached a ora -number and customized message to userTerminate any pl/sql block and behave like pre-define exception Advanced Exceptions
Raise vs Raise_Application_Error Raise is used to call pre-defined or user-defined exceptionRaise_application_erorr let the developer show the customized message with number Call from front end Advanced Exceptions
Syntax of Raise_Application_Error Database Programming Using Oracle 11g
R aise_application_error(error_number, message[, {TRUE | FALSE}]); E rror_number is a negative integer in the range -20000 .. -20999Message is a character string up to 2048 bytes long Error Handling and Exceptions
Syntax of Raise_Application_ErrorPre-define procedure to return user-friendly message back to user Advanced Exceptions
Implementing Raise_application_error - I Database Programming Using Oracle 11g
declare name emp.ename%type;salary emp.sal%type;begin select ename, sal into name, salary from emp where empno=7369 ; if salary <5000 thenRaise_application_error(-20030, ‘Invalid salary’);else Error Handling and Exceptions
dbms_output.put_line ('Valid Salary');end if;exceptionwhen others then dbms_output.put_line(SQLERRM); end; Error Handling and Exceptions
Implementing User Defined Exception - II Database Programming Using Oracle 11g
Write a PL/SQL block to retrieve the ename, job, mrg and hiredate for a particular empno and make sure data is retrieved without error i-e if any field have null value then raise the following exceptions: Error Handling and Exceptions Exception No Message -20010 No name -20020 No job 20030 No manager -20040 No hire date
declare v_ename emp.ename%TYPE; v_job emp.job%TYPE; v_mgr emp.mgr%TYPE; v_hiredate emp.hiredate%TYPE ; p_empno emp.empno%type :=7654; BEGIN SELECT ename, job, mgr, hiredate INTO v_ename , v_job , v_mgr , v_hiredate FROM emp Error Handling and Exceptions
WHERE empno = p_empno; IF v_ename IS NULL THEN RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno ); END IF; IF v_job IS NULL THEN RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno); END IF; IF v_mgr IS NULL THEN Error Handling and Exceptions
RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno); END IF; IF v_hiredate IS NULL THEN RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno ); END IF; DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' validated without errors'); Error Handling and Exceptions
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE); DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);END; Error Handling and Exceptions
What is exception_init Pragma Exception name can be associated with oracle exception numberOra – Error no can be intercepted and specific handler can be written Advanced Exceptions
What is exception_init Pragma Pragma signifies that it’s a compiler directive Pragma is processed at compile time not run-time Advanced Exceptions
Syntax of Exception_init Pragma Database Programming Using Oracle 11g
DECLARE user_define_exception_name EXCEPTION; PRAGMA EXCEPTION_INIT(user_define_exception_name ,-error_number); BEGIN statement(s); IF condition THEN RAISE user_define_exception_name; END IF; EXCEPTION WHEN user_define_exception_name THEN User defined statement (action) will be taken; END ; Error Handling and Exceptions
Syntax of Exception_init Pragma To associate customize message to pre-defined exceptions Advanced Exceptions
Implementing Exception_init Pragma -I Database Programming Using Oracle 11g
declare salary number;FOUND_NOTHING exception;Pragma exception_init(FOUND_NOTHING ,100);beginselect sal in to salary from emp where ename =‘Akbar';dbms_output.put_line(salary);exceptionWHEN FOUND_NOTHING THENdbms_output.put_line(SQLERRM);end; Error Handling and Exceptions
Implementing Exception_init Pragma -II Database Programming Using Oracle 11g
DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60);BEGIN dbms_output.put_line('In the begin section'); EXCEPTION WHEN deadlock_detected THEN dbms_output.put_line('Deadlock is detected');END; Error Handling and Exceptions