2102022 PLSQL EXCEPTION WITH GOD EVERYTHING IS POSSIBLE

2/10/2022

PL/SQL: EXCEPTION WITH GOD EVERYTHING IS POSSIBLE

Exceptions In PL/SQL, errors are known as exceptions. An excepitons occurs when an unwanted situation arises during the execution of a program. Exceptions can result from a system error, a user error, or an application error. When an exception occurs, control of the current program block shifts to another section of the progam, known as the exception section, to handle exceptions. 2/10/2022

Exceptions Syntax of anonymous block, [ Declare declaration of constants, variables, cursors and exceptions] Begin Executable PL/SQL and SQL statements [ Exception Actions for error conditions ] End; 2/10/2022

Exceptions Syntax of Exception section is: Exception when exceptionname 1 [or exceptionname 2, . . . ] then executable statements [when excepitonname 3[or exceptionname 4, . . . ] then executable statements] [when others then executable statements] An exception is handled when the exception name matches the name of the raised exception. If there is no handler present, WHEN OTHERS clause is performed. 2/10/2022

Exceptions Types of Exceptions: There are three types of exceptions in PL/SQL: 1. Predefined Oracle server exceptions: are exceptions that are named by PL/SQL and are raised implicitly when a PL/SQL error occurs. There approximately 20 such exceptions. Each has a name and assosiated error number. 2. Nonpredefined Oracle server exceptions: are standard Oracle server errors that are not named by the system. They can declare but raised implicitly by the server. It has no name but have error number. 3. User defined exceptions: are exceptions that are declared and are raised by the user explicitly. 2/10/2022

Exceptions Predefined Oracle server exceptions: Exceptions that are given names by PL/SQL and are declared in a PL/SQL package called STANDARD. The user does not have to declare. Each has a name and assosiated error number. Exception name Error Number NO_DATA_FOUND ORA-01403 TOO_MANY_ROWS ORA-01422 ZERO_DIVIDE ORA-01476 Description single row. SELECT returned no data single row. SELECT return more than one data divide 2/10/2022

Exceptions Nonpredefined Oracle server exceptions: are standard Oracle server errors that are not named by the system. You can trap such exceptions with a WHEN OTHERS clause or declaring them. Pragma Exception_Init: Pragma is a compiler directive that associates name with an internal Oracle error code. Naming and associating are two separate statements in the declaration section. 1. an exception name is declared as an EXCEPTION. 2. the declared name is associated with an internal error code. 2/10/2022

Exceptions Syntax. , exceptionname EXCEPTION; PRAGMA EXCEPTION_INIT(exceptionname, errornumber); Example: Declare emp_remain EXCEPTION; PRAGMA EXCEPTION_INIT(emp_remain, -2292); Begin . . . Exception when emp_remain then . . . End; 2/10/2022

Exceptions Exception-Trapping Functions: When an exception occurs in your program, you don't know the error code for the error and its associated message unless you take specofoc action to identify them. The two functions to identify the error code and error message are: 1. SQLCODE. It returns a negative error code number. The number can be assigned to a NUMBER type variable. 2. SQLERRM. It returns the error message associated with the error code. The maximum length of error message is 512 bytes. It can be assigned to a. VARCHAR 2 -type variable. 2/10/2022

Exceptions Example: Declare code number; msg varchar 2(255); Begin . . . Exception when others then code: =SQLCODE; msg: =SQLERRM; . . End; 2/10/2022

Exceptions User Defined Exception: You will encounter situations that are specific to a given program. For example. , a birthdate falls in a future a quantity in an invoice is negative. etc. , You are allowed to define your own exceptions in PL/SQL. Three Steps for exceptions. 1. You must Declare the exception in the DECLARE section. 2. You must Raise the exception in execution section with an explicit RAISE statement. 2/10/2022 3. You must write the handler for the exception.

Exceptions RAISE_APPLICATION_ERROR Procedure: It allows you to display nonstandard error codes and user-defined error messages from a stored subprogram. Syntax. , RAISE_APPLICATION_ERROR(ERROR_CODE, ERROR_MESSAGE [, TRUE/FALSE]); error_code - is a user-specified number between - 20, 000 and -20, 999 error_message is a user-supplied message that can be up to 512 bytes long. TRUE means 'place an error in stack, FALSE is default value, it means 'it replaces all previous errors'. 2/10/2022

Exceptions Example. , EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Department does not exists'); 2/10/2022