Oracle Custom Exceptions ~ ~DECLAREv_ename emp.ename%TYPE;BEGINSELECTename into V_ename fromEMPWHEREsal = &v_sal;Dbms_output.put_line (' Employee Name: '| | | v_ename);EXCEPTION whenNo_data_found ThenDbms_output.put_line (' There is no wage for '| | | v_sal | |' Employees '); whenOTHERS ThenDbms_output.put_line (' Error number '||SQLCODE );Dbms_output.put_line (SQLERRM);END; -----------------------------------Custom exception handling
CREATE OR REPLACE PROCEDURE Stu_proc
(
--Multiple separated by commas
v_id in number
) is
--Multiple separated by semicolons
V_MAX_ID number;
V_name VARCHAR2 (20);
V_raise EXCEPTION;
BEGIN
SELECT MAX (a.id) to v_max_id from student A;
IF v_id>v_max_id Then
RAISE v_raise;
END IF;
SELECT O.sname to v_name from student o WHERE o.id=v_id;
Dbms_output.put_line (' Student name: ' | | V_name);
EXCEPTION
When V_raise Then
Raise_application_error ( -20010, ' v_id not exists! ');
When No_data_found Then
Raise_application_error ( -20011, ' ERROR: not present! ‘);
END Stu_proc; the following are common exception information:
--named system exception causesaccess_into_null undefined Objectif the corresponding when is not included in the Case_not_found case and is not setCollection_is_null Collection element not initializedcurser_already_open cursor already openDup_val_on_index Duplicate values on a column corresponding to a unique indexinvalid_cursor operation on an illegal cursorinvalid_number embedded in theSQLstatement cannot convert a character to a numberNo_data_found UseSelect intorow is not returned, or the index table is not initializedtoo_many_rows ExecutionSelect into , the result set is more than one rowzero_divide Divisor is0subscript_beyond_count element subscript exceeds the maximum value of a nested table or Varraysubscript_outside_limit using nested tables orVarray, the subscript is specified as a negative numberwhen Value_error is assigned, the variable length is not sufficient to accommodate the actual datalogin_deniedPL/SQLAn incorrect user name or password is provided when an application connects to an Oracle databasenot_logged_on PL/SQLThe application accesses data without connecting to the Oralce databaseProgram_errorPL/SQL internal issues, you may need to reload the data Dictionary & Pl./sql system Packagerowtype_mismatch Primary cursor variable andPL/SQLThe return type of the cursor variable is incompatibleSelf_is_null When using object types, the NULLcalling object methods on an objectStorage_Error RunPL/SQLout of memory space whensys_invalid_id Invalid rowIDstringTimeout_on_resource Oracle timed out while waiting for resources-------------------------------------------------------------------------A function that determines whether a string is a date:CREATEORREPLACEFUNCTION f_is_date (ParameterVARCHAR2)RETURN Number is
/*
determines whether the specified string is a date
*/ValDATE;BEGINval: = To_date (NVL (parameter,' A '), ' Yyyy-mm-dd hh24:mi:ss '); RETURN1;EXCEPTION whenOTHERS Then RETURN0;END;
Oracle Custom Exception
