PL/SQL Exceptions on Update/Delete of non-existing row

Question

I am learning PL/SQL these days and currently working with Procedures and exceptions using oracle HR schema.

Here is my simple procedure.

create or replace
PROCEDURE DEL_JOB
(p_jobid jobs.job_id%TYPE)
AS 
sqle NUMBER; 
sqlm VARCHAR2(300);
BEGIN
DELETE FROM JOBS 
WHERE JOB_ID = UPPER(p_jobid);
IF SQL%NOTFOUND THEN
  DBMS_OUTPUT.PUT_LINE('No such record');
END IF;
EXCEPTION
 WHEN OTHERS THEN
  sqle := SQLCODE;
  sqlm := SQLERRM;
  DBMS_OUTPUT.PUT_LINE('There is no job with this id that could be deleted');
  DBMS_OUTPUT.PUT_LINE('Error Code ='||sqle||' Error message =' ||sqlm);
END;

When I execute this procedure the output is

  No such record
  PL/SQL procedure successfully complete. 

However, according to the Oracle PDF it should throw an exception and I should really get the message I entered in the exception.

Same thing happened with the Update on non existing record. Please advise. Thanks

Answer 1

I believe SQL%NOTFOUND returns true when no records are found. Your IF would evaluate to true in that case, and therefore write your put_line to terminal. The SQL statement executed successfully. If you execute that SQL statement by itself from command line, you will receive 0 rows updated/deleted, not an Oracle error.

If you want to have an exception thrown, you could use RAISE inside your IF and point it to the exception in the exception block you want to have thrown.

Answer 2

There is no "exception" - the sql executed successfully. It successfully deleted every record that matched the criteria...which was 0 records. Same thing if a similar update statement was executed. You used the SQL%NOTFOUND to determine there were no records that were affected, but this does not mean there was an "exception".

Perhaps you're thinking of the NO_DATA_FOUND Exception raised if you try a "select into" clause and it doesn't find any matching records.