PLSQL EXCEPTION not working

Question

I am trying to raise an exception when the procedure does not insert a record. My code is as follows:

CREATE OR REPLACE PROCEDURE OPEN_CLASS(
    p_class IN TUTPRAC.CLASSID%TYPE,
    p_unitc IN TUTPRAC.UNITCODE%TYPE,
    p_classd IN TUTPRAC.CLASS_DAY%TYPE,
    p_classt IN TUTPRAC.CLASS_TIME%TYPE,
    p_classtp IN TUTPRAC.CLASS_TYPE%TYPE,
    p_roomnm IN TUTPRAC.ROOMNUM%TYPE)
IS
    -- Variables    
    x number:=0;
    y number:=0;  
    CLASS_CLASH EXCEPTION;
BEGIN
    -- checks
    SELECT nvl((SELECT 1 
                FROM TUTPRAC
                 WHERE UNITCODE = p_unitc and CLASS_DAY = p_classd 
                  or CLASS_DAY = p_classd and CLASS_TIME = p_classt
                  and ROOMNUM = p_roomnm) , 0) 
    INTO x FROM dual;
    SELECT nvl((SELECT 1 
                FROM UNITSTREAM 
                WHERE UNITCODE = p_unitc and DAY = p_classd 
                or DAY = p_classd and TIME = p_classt 
                and LOCATION = p_roomnm) , 0) 
    INTO y FROM dual;
    -- insert
    IF (x = 0 and y = 0) THEN
      INSERT INTO TUTPRAC (CLASSID, UNITCODE, CLASS_DAY, CLASS_TIME, CLASS_TYPE, ROOMNUM) 
      VALUES (p_class, p_unitc, p_classd, p_classt, p_classtp, p_roomnm);
    ELSE
      RAISE CLASS_CLASH;
    END IF;
EXCEPTION
      WHEN CLASS_CLASH THEN
        DBMS_OUTPUT.PUT_LINE('Record was not inserted due to a class clash.');
END OPEN_CLASS;

When I run this procedure, if the record does not have any clashes it says PL/SQL procedure successfully completed. and it adds a record into the table. The problem is that even if it finds a clash and doesn't add a record it still says that same message instead of the output from my exception.

Possible duplicate of http://stackoverflow.com/q/6020450/2091410 - see the first answer, which should help. — Ed Gibbs, Oct 25 '15 at 03:24

score 2 · Answer 1 · edited Oct 25 '15 at 17:11

Since you catch your exception within your procedure - the procedure does not terminate abnormally. So, PL/SQL procedure successfully completed. is the correct output. To make your procedure throw an exception you need to issue a raise; in the EXCEPTION block.

If you don't see the message this might be because of DBMS_OUTPUT output being suppressed. Check if you see any output if you put DBMS_OUTPUT.put_line as the first line of your procedure. If you don't - check that you're calling SET SERVEROUTPUT ON in your sqlplus session before calling your procedure.

score 0 · Answer 2 · edited Mar 19 '19 at 17:59

You might not have executed set serveroutput on in your session.

Your code will throw exception no data found. I think you're expecting a NULL when there is no match so you've used NVL. But the select statements will not return anything instead of the NULL you're expecting.

It is the reason for no data found exception.

You can use count(*) into x and count(*) into y instead of the NVLs. It should work as expected.

PLSQL EXCEPTION not working

2 Answers2