Monday, 29 February 2016

Oracle Forms Exception Handling: NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS

Oracle Forms Exception Handling: NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS

EXCEPTION block in PLSQL Oracle Forms is used to track the exceptions. Following is the PLSQL code snippet which uses NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS exceptions. If the SQL SELECT query does not return any data, NO_DATA_FOUND exception is fired. If the SQL SELECT query returns more than one row where it was expected to return only one row, TOO_MANY_ROWS exception can be used to track this kind of exception. If you are not sure what kind of exception can the code throw, use OTHERS exception.

DECLARE
  DEPARTMENT_NAME VARCHAR(60);
BEGIN
  SELECT DEPTNAME INTO DEPARTMENT_NAME FROM DEPT WHERE DEPTNO = 20;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    MESSAGE('No data found');
  WHEN TOO_MANY_ROWS THEN
    MESSAGE('More than one row found');
  WHEN OTHERS THEN
    NULL; -- don't do anything and just return from the procedure
END;

No comments:

Post a Comment