Procedure to delete non existing rows oracle

Question

I wrote a procedure in PL/SQL to delete rows from a table,However,if that record does not exist,then throws some error like this: DBMS_OUTPUT.PUT_LINE('No such record'); My procedure is:

CREATE OR REPLACE PROCEDURE del_cn2
                   (c_cntry_id IN COUNTRIES.COUNTRY_ID%TYPE
                   )
    IS
      v_error_code NUMBER;
BEGIN
        DELETE from countries
        WHERE country_id =c_cntry_id;

        IF SQL%NOTFOUND THEN
          DBMS_OUTPUT.PUT_LINE('No such record');
        END IF;
      EXCEPTION WHEN OTHERS THEN
        v_error_code :=SQLCODE;
        IF v_error_code =-2292 THEN
          RAISE_APPLICATION_ERROR(-20004,'Organization '||TO_CHAR(c_cntry_id)||' site 

details defined for it.');
        END IF;
      END;

  /

However,when I execute this procedure and provide a record that does not exist in my table,it gives message "Procedure completed successfully" I am using this to execute:

Execute procedure del_cn2('JJ');

Can someone please suggest?

Answer 1

If you want an exception to be thrown when a value that does not exist in the table is passed in, you would need to actually throw an exception. You shouldn't use dbms_output for any sort of error output. That is a very simplistic debugging tool-- you shouldn't assume that the caller will ever be able to see that output.

My guess is that you want something like

CREATE OR REPLACE PROCEDURE del_cn2
               (c_cntry_id IN COUNTRIES.COUNTRY_ID%TYPE
               )
IS
BEGIN
    DELETE from countries
    WHERE country_id =c_cntry_id;

    IF SQL%ROWCOUNT = 0 
    THEN
      raise_application_error( -20001, c_cntry_id || ' no such value.' );
    END IF;
END;

Answer 2

try to set serverout to ON

example:

create table tst_delete (col1 int); 

create procedure p_test_delete  as
BEGIN
    DELETE FROM tst_delete
    WHERE       col1 = 1;
    IF (SQL%NOTFOUND)
    THEN
        dbms_output.put_line('No records found');
    END IF;
  END;

then call the procedure in SqlPlus

SQL> exec p_test_delete;

PL/SQL procedure successfully completed

same issue that you described - no insformation... next try with output activated

SQL> set serverout on
SQL> exec p_test_delete;

No records found

PL/SQL procedure successfully completed

SQL>