Author: Dev

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)

The dup_val_on_index exception is an predefined exception of PL/SQL language and catch error when pl/sql program try to store duplicate values in a database column that is constrained by a unique index. STUDENT_ID FIRST_NAME LAST_NAME CITY 1 Daniel SCOTT New York 2 Anthony SIMMONS Chicago 3 Sophia THOMPSON Los Angeles Dup_val_on_index example declare begin insert…(Continue Reading)