Category: PL/SQL

The user defined exceptions are user declared exceptions in the declarative part of any PL/SQL anonymous block, subprogram or package. In the first example exception was declared in the declarative part and then the exception is triggered inside the block using RAISE. The second example use RAISE_APPLICATION_ERROR inside the block with no need to declare…(Continue Reading)

The predefined PL/SQL exceptions are declared globally in the package STANDARD and the system raises these exceptions implicitly. Predefined exceptions from package STANDARD have predefined names: Predefined exceptions access_into_null case_not_found collection_is_null cursor_already_open dup_val_on_index invalid_cursor invalid_number login_denied no_data_found no_data_needed not_logged_on others program_error rowtype_mismatch self_is_null storage_error subscript_beyond_count subscript_outside_limit sys_invalid_rowid timeout_on_resource too_many_rows value_error zero_divide

The zero_divide exception is an predefined exception of PL/SQL language and catch error when pl/sql program attempts to divide a number by zero. Zero_divide example 1 declare v_sum number := 10; v_divide number := 0; v_result number; begin v_result := v_sum / v_divide; dbms_output.put_line(‘v_result: ‘||v_result); exception when zero_divide then dbms_output.put_line(‘ZERO_DIVIDE: ‘||sqlerrm); end; Output: ZERO_DIVIDE: ORA-01476:…(Continue Reading)

The invalid_number exception is an predefined exception of PL/SQL language and catch error when pl/sql program try to insert or update non numeric values in a database number column. Invalid_number example create table x_table(x_col number); table X_TABLE created. declare begin insert into x_table (x_col) values (‘aaa’); –insert into x_table (x_col) values (10); exception when invalid_number…(Continue Reading)

The case_not_found exception is an predefined exception of PL/SQL language and catch error when executing CASE statement and no data is found. Case_not_found example declare gen students.gender%TYPE; stu students.student_id%TYPE := 3; message VARCHAR2(250); begin SELECT gender INTO gen from students WHERE student_id = stu; dbms_output.put_line(‘Gender is: ‘||gen); CASE WHEN gen = ‘M’ THEN message :=…(Continue Reading)