CODEWITHSUNDEEP
oracle-databaseexceptionplsqltriggers
bbb
bbb

Reputation: 1489

ORA-06510: PL/SQL: unhandled user-defined exception [Oracle]

I'm relatively new to Oracle so forgive me for my lack of knowledge. Whenever this trigger is fired I keep getting an error stating that I have an unhandled user-defined exception. Elsewhere in my functions and procedures I have declared and raised my user-defined exactly as this but in this case is doesn't work. I know it's probably something trivial and obvious but as I said I'm fairly new to Oracle so please forgive me.

CREATE OR REPLACE TRIGGER PROGRAMME_BI
BEFORE INSERT ON PROGRAMME
DECLARE
v_run_time programme.run_time%TYPE;
INVALID_DURATION EXCEPTION;
BEGIN
IF v_run_time > 5 THEN
  DBMS_OUTPUT.PUT_LINE('Program duration is valid');
  COMMIT;
ELSE
  RAISE INVALID_DURATION;
END IF;
EXCEPTION
  WHEN INVALID_DURATION THEN
    RAISE_APPLICATION_ERROR(-20001,'Program duration is not long enough');
  ROLLBACK WORK;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
END;

UPDATE I have updated the line after the exception is raised so it doesn't give me the unhandled user-defined exception error anymore. However it still does not work as intended. Whenever I enter in a program duration greater than 5 I get the following in the DBMS output window.

-20001ORA-20001: Program duration is not long enough
ORA-06512: at "DT2113A.PROGRAMME_BI", line 13
ORA-04088: error during execution of trigger 'DT2113A.PROGRAMME_BI'
Program not added

Upvotes: 0

Views: 29151

Answers (1)

psaraj12
psaraj12

Reputation: 5072

you have to assign value to v_run_time Kindly try the below

CREATE OR REPLACE TRIGGER PROGRAMME_BI
BEFORE INSERT ON PROGRAMME
FOR EACH ROW
DECLARE
v_run_time programme.run_time%TYPE:=:new.run_time;
INVALID_DURATION EXCEPTION;
BEGIN
 IF v_run_time > 5 THEN
 DBMS_OUTPUT.PUT_LINE('Program duration is valid');
  COMMIT;
ELSE
RAISE INVALID_DURATION;
END IF;
EXCEPTION
 WHEN INVALID_DURATION THEN
  RAISE_APPLICATION_ERROR(-20001,'Program duration is not long enough');
  ROLLBACK WORK;
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
END;

Upvotes: 2

Related Questions