PL/SQL Procedure with invalid IN parameter

Question

How can I make ID validation in this procedure if p_id doesn't exist (I have ID's from 1 to 1000) or it's not valid to add error message "ID not found in this table"?

This is my code:

CREATE OR REPLACE PROCEDURE MyProc
(
  p_id IN NUMBER,
  ret_val OUT NUMBER
) AS
BEGIN
  ret_val := 0;
  BEGIN
    UPDATE Table1
       SET n_id = 2,
           n_date = SYSDATE
       WHERE n_id = p_id;
    ret_val := 1;
  EXCEPTION
    WHEN OTHERS THEN
         ret_val := -1;
  END;
END MyProc;

Answer 1

If p_id is invalid,i.e. a non-numeric value is provided as input the procedure will error out implicitly.Oracle will complain the mismatch in datatype.

To handle "ID not found in this table" you can explicitly raise exception with the help of oracle implicit cursor attribute SQL%ROWCOUNT.It will return <p_id> ID not found in this table error.

CREATE OR REPLACE PROCEDURE MyProc
(
  p_id IN NUMBER,
  ret_val OUT NUMBER
) AS

BEGIN
ret_val := 0;

UPDATE Table1
SET n_id = 2,
n_date = SYSDATE
WHERE n_id = p_id;

ret_val := 1;

 IF (SQL%ROWCOUNT = 0) THEN
     ret_val := -1;
     RAISE_APPLICATION_ERROR(-20000,p_id||' - ID not found in this table ');
 END IF;

END MyProc;
/

Refer dbfiddle for demo https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8436a79b7a2f7f37a4e9b8f324e37ab4