Message is not prompting for same store code when added twice in Oracle

Question

I have stored procedure as below, where I am checking UNIQUE constraint that same store_code cannot be added twice. So once the user has inserted the STORE_CODE with name say abc. And again he is trying to insert the same store_code with same name. then he should get a prompt as Record already exist.

I have tried like below SP but it's not prompting the message.

PROCEDURE INSERT_INTO_RRSOC_MST
  (    
    P_STORE_CODE IN NVARCHAR2,
    P_STATE IN NVARCHAR2,
    P_CITY IN NVARCHAR2,
    P_Indication IN NUMBER,
    TBLDATA OUT NVARCHAR2
  ) 
  
  AS
  
  V_RRSOC_ID NUMBER:=0;
  
  BEGIN
    
    SELECT COUNT(RRSOC_ID) INTO V_RRSOC_ID FROM TBL_RRSOC_STORE_INFO 
    WHERE STORE_CODE = P_STORE_CODE AND isactive = 'Y';
    
    IF V_RRSOC_ID > 0 AND  P_Indication = 1 THEN
    
                              UPDATE TBL_RRSOC_STORE_INFO 
                              SET                                
                                  STATE = P_STATE,
                                  CITY = P_CITY,
                              WHERE STORE_CODE = P_STORE_CODE;   
  commit;  
  
    ELSE
    
     IF V_RRSOC_ID = 0 AND  P_Indication = 0 THEN
    
    INSERT INTO TBL_RRSOC_STORE_INFO      
                                   (
                                          STORE_CODE,
                                          STATE,     
                                          CITY                                      
                                          
                                   )
            
     VALUES
                                   (            
                                          P_STORE_CODE,
                                          P_STATE,
                                          P_CITY
                                            
                                   ) 
                                   
                                   RETURNING RRSOC_ID INTO TBLDATA;
commit;                                         
          TBLDATA:='Record Saved Succesfully';  
          
          ELSE
          
           TBLDATA:='Record already exist';  
    
          END IF;
  END IF;
        EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
        
  END INSERT_INTO_RRSOC_MST;

Answer 1

It is kind of unclear what is P_INDICATION used for and which values it can get.

Code that follows distinguishes different v_rrsoc_id values and checks what's in p_indication to decide what to do.

Perhaps you shouldn't commit within a procedure, but let caller do that if everything is OK. Also, rolling back in when others will silently ignore errors and you won't know what went wrong. Maybe you should return error message in such a case.

PROCEDURE insert_into_rrsoc_mst (p_store_code  IN     NVARCHAR2,
                                 p_state       IN     NVARCHAR2,
                                 p_city        IN     NVARCHAR2,
                                 p_indication  IN     NUMBER,
                                 tbldata          OUT NVARCHAR2)
AS
   v_rrsoc_id  NUMBER := 0;
BEGIN
   SELECT COUNT (rrsoc_id)
     INTO v_rrsoc_id
     FROM tbl_rrsoc_store_info
    WHERE     store_code = p_store_code
          AND isactive = 'Y';

   IF v_rrsoc_id > 0
   THEN
      IF p_indication <> 1
      THEN
         tbldata := 'Record already exist';
      ELSIF p_indication = 1
      THEN
         UPDATE tbl_rrsoc_store_info
            SET state = p_state, city = p_city
          WHERE store_code = p_store_code;
      END IF;
   ELSIF v_rrsoc_id = 0
   THEN
      IF p_indication = 0
      THEN
         INSERT INTO tbl_rrsoc_store_info (store_code, state, city)
              VALUES (p_store_code, p_state, p_city)
           RETURNING rrsoc_id
                INTO tbldata;
      END IF;
   END IF;

   tbldata := 'Record Saved Succesfully';
EXCEPTION
   WHEN OTHERS
   THEN
      tbldata := 'Error: ' || SQLERRM;
END insert_into_rrsoc_mst;

---

[EDIT]

Now that we found out what p_indication means, procedure can be significantly simplified:

PROCEDURE insert_into_rrsoc_mst (p_store_code  IN     NVARCHAR2,
                                 p_state       IN     NVARCHAR2,
                                 p_city        IN     NVARCHAR2,
                                 p_indication  IN     NUMBER,
                                 tbldata          OUT NVARCHAR2)
AS
BEGIN
   IF p_indication = 1
   THEN
      UPDATE tbl_rrsoc_store_info
         SET state = p_state, city = p_city
       WHERE store_code = p_store_code;

      tbldata := 'Record updated';
   ELSIF p_indication = 0
   THEN
      INSERT INTO tbl_rrsoc_store_info (store_code, state, city)
           VALUES (p_store_code, p_state, p_city);

      tbldata := 'Record inserted';
   END IF;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      tbldata := 'Record already exists';
END;