PL/SQL exceptions dont raise

Question

I'm new to PL/SQL and I've been struggling with this problem waay too long so I thought I'll ask here:

What I want to achieve:

• I want to insert a new employee into my 'emp' table using pl/sql blocks.
• I want the data to be input from the keyboard.
• Then check if the data is correct using exceptions.
• After that I want the user to confirm it writing 'yes' or 'no'.

What doesn't work:

• Exceptions are not being raised even though I input invalid data.

• As it is now it asks the user to confirm at the same time as asking for the data. I want it to ask after inputing and after checking the correctness

My code:

DECLARE
  empno   NUMBER;
  ename   Varchar2(30);
  job     Varchar2(30);
  mgr     NUMBER;
  hiredate DATE;
  sal     NUMBER;
  comm    NUMBER;
  deptno  NUMBER;
  confirm VARCHAR2(3);
  negative_number EXCEPTION;
  invalid_date    EXCEPTION;
BEGIN
  INSERT INTO emp
  VALUES (&empno, '&ename', '&job', &mgr, '&hiredate', &sal, &comm, &deptno);
  IF empno < 0 OR mgr < 0 OR sal < 0 OR comm < 0 OR deptno < 0 THEN
    RAISE negative_number;
  ELSIF hiredate > SYSDATE THEN
    RAISE invalid_date;
  END IF;
  IF '&confirm' = 'yes' THEN commit;
  ELSE rollback;
  END IF;
EXCEPTION
  WHEN negative_number THEN
  dbms_output.put_line('number cannot be negative');
  WHEN invalid_date THEN
  dbms_output.put_line('cannot be future date');
END;

Answer 1

DECLARE

  empno   NUMBER := 10;
  ename   Varchar2(30) := 'sinha';
  job     Varchar2(30) := 'ITTTT';
  mgr     NUMBER := 100;
  hiredate DATE := TO_DATE('09/09/2014', 'mm/dd/yyyy');
  sal     NUMBER := 2000;
  comm    NUMBER := 50;
  deptno  NUMBER := 10;
  invalid_emp exception;
  invalid_date exception;

BEGIN

  INSERT INTO empl
  VALUES (empno, ename, job, mgr, hiredate, sal, comm, deptno);

  IF empno < 0 OR mgr < 0 OR sal < 0 OR comm < 0 OR deptno < 0 THEN
    RAISE invalid_emp;
  ELSIF hiredate > SYSDATE THEN
    RAISE invalid_date;
  END IF;

  exception
  when invalid_emp then
  dbms_output.put_line('number cannot be negative');
  when invalid_date then 
  dbms_output.put_line('cannot be future date');
END;

this is how you can use a user defined exception as you wanted

Answer 2

Just a little modification to your present code:

DECLARE
  empno   NUMBER := &empno;
  ename   Varchar2(30) := '&ename';
  job     Varchar2(30) := '&job';
  mgr     NUMBER := &mgr;
  hiredate DATE := TO_DATE('&hiredate', 'mm/dd/yyyy');
  sal     NUMBER := &sal;
  comm    NUMBER := &comm;
  deptno  NUMBER := &deptno;
  confirm VARCHAR2(3);
BEGIN
  INSERT INTO emp
  VALUES (empno, ename, job, mgr, hiredate, sal, comm, deptno);
  IF empno < 0 OR mgr < 0 OR sal < 0 OR comm < 0 OR deptno < 0 THEN
    RAISE_APPLICATION_ERROR ('-20001', 'Number cannot be negative!');
  ELSIF hiredate > SYSDATE THEN
    RAISE_APPLICATION_ERROR ('-20002', 'Cannot be future date!');
  END IF;
  confirm := '&confirm';
  IF lower(confirm) IN ('yes', 'y') THEN
    commit;
  ELSE
    rollback;
  END IF;
END;

The last part - confirm is not possible using pure PL/SQL though. What the code does is get the value of confirm at the beginning of the execution itself, along with other variables. What you should do for this is build a front-end that would control the flow of your program before issuing a DML.