Procedure to delete non existing rows oracle
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?
try to set serverout to ON
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>
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;