Category: PL/SQL

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)

The value_error exception is an predefined exception of PL/SQL language and catch numeric or value error. Value_error example declare v_dsp number; begin select description into v_dsp from course where name=’SQL 2′; dbms_output.put_line(‘Description is: ‘||v_dsp); exception when value_error then dbms_output.put_line(‘VALUE_ERROR: ‘||sqlerrm); dbms_output.put_line(‘Change data type of v_dsp in varchar2(2000)’); end; Output: VALUE_ERROR: ORA-06502: PL/SQL: numeric or value…(Continue Reading)

The others exception is an predefined exception of PL/SQL language and catch any predefined exceptions. Others example 1 declare v_order_id number; begin select order_id into v_order_id from orders where course_id=1234567; dbms_output.put_line(‘Order id is: ‘||v_order_id); exception when others then dbms_output.put_line(‘When others exception error message: ‘||sqlerrm); end; Output: When others exception error message: ORA-01403: no data found…(Continue Reading)

The too_many_rows exception is an predefined exception of PL/SQL language. Too_many_rows example 1 declare v_order_id number; begin select order_id into v_order_id from orders where course_id=5; dbms_output.put_line(‘Order id is: ‘||v_order_id); exception when too_many_rows then dbms_output.put_line(‘Tow many rows!’); end; Output: Tow many rows! Too_many_rows example 2 declare v_order_id number; begin select order_id into v_order_id from orders where…(Continue Reading)