Procedure to check for non duplicate rows in table before insert (Oracle)

Question

I wrote a procedure in oracle(using guidance from here) to check for any duplicate values in table so that countries with the same countryId should return some error message as countryId is PK in countries table.Here it is:

   CREATE OR REPLACE PROCEDURE add_values4 (c_cntry_id IN OUT COUNTRIES.COUNTRY_ID%TYPE,
                                       c_cntr_name IN COUNTRIES.COUNTRY_NAME%TYPE, 
                                       c_rgn_id IN COUNTRIES.REGION_ID%TYPE)
IS
DECLARE
   outputValue CHAR  := 'JJ';
BEGIN
  INSERT INTO countries(COUNTRY_ID, COUNTRY_NAME,REGION_ID)
    values (user_seq.nextval, c_cntr_name,c_rgn_id);
  c_cntry_id := user_seq.currval;
EXCEPTION
  WHEN dup_val_on_index
  THEN 
    c_cntry_id := null;
END;
/

However,when I try to create this procedure,it gives me error PLS-00103.Any idea what could be wrong? (Anything in syntax?).I have been trying since 48 hours but with not much success.Appreciate some help here.

Tx in advance

Answer 1

You have a semicolon after your parameter block, and you are missing your IS or AS clause:

CREATE OR REPLACE PROCEDURE add_vals (c_cntry_id OUT COUNTRIES.COUNTRY_ID%TYPE,
                                       c_cntr_name IN COUNTRIES.COUNTRY_NAME%TYPE, 
                                       c_rgn_id IN COUNTRIES.REGION_ID%TYPE)
IS
BEGIN
  INSERT INTO countries(COUNTRY_ID, COUNTRY_NAME,REGION_ID)
    values (user_seq.nextval, c_cntr_name,c_rgn_id);
  c_cntry_id := user_seq.currval;
EXCEPTION
  WHEN dup_val_on_index
  THEN 
    c_cntry_id := null;
END;
/