How to check if ID no exists in the database when type on page item?

I have ID NO page item in oracle apex and its unique but what I need when type exists ID NO check direct if this ID NO exists in the database stop and show message

This ID NO already exists .

Actually I don't know which dynamic action I will use I think when press dynamic action will help to check ID NO and this is the SELECT statement :

I tried the true action execute server side condition :

select patient_no  into :P3_PATIENT_NO from mr_files
where patient_no = :P3_PATIENT_NO;

but its not working how to do that and stop the cursor and show message if ID NO exists in the database ?

Solution

Typically this would be done using a validation, not a dynamic action. A validation is fired on page submit and if a validation fails, then the record will not be saved. That is the behaviour you're looking for. In this case, this is how you'd create a validation:

I have created an interactive report with form on the EMP table (EMP/DEPT sample dataset). When I create a new employee, I want to check if the value that I enter in "Name" does not exist in the table yet.

The page item that holds the value for column ENAME is P171_ENAME.

Add a validation to page item P171_ENAME of type "Function Body (returning BOOLEAN)" with source

DECLARE
  l_nr NUMBER;
BEGIN
  SELECT 1 INTO l_nr FROM emp WHERE ename = :P171_ENAME;
  RETURN false;
EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN true;
END;

and error message "Ename already exists".

That is all there is to it. When the user enters an existing name he will get a validation error on submit.

But...since you mentioned dynamic actions, here is an example of a dynamic action on a form field to popup an alert if the value already exists. In this case it checks for ename, but you can use it for an id column as well. Note that this is not a validation. It doesn't stop the user from continuing. Validations provide a better user experience than dynamic actions in this case.

Create a new (hidden) page item P171_ENAME_EXISTS
On the page item P171_ENAME, create a dynamic action on Event "Change".
Create a "Execute Server-side Code" true action with code:

DECLARE
  l_nr NUMBER;
BEGIN
  SELECT 1 INTO l_nr FROM emp WHERE ename = :P171_ENAME;
  :P171_ENAME_EXISTS := 'Y';
EXCEPTION WHEN NO_DATA_FOUND THEN
  :P171_ENAME_EXISTS := 'N';
END;

items to submit: P171_ENAME, items to return P171_ENAME_EXISTS

create an true action of type "Alert" with message "Ename exists !" and client-side condition Item = Value (Item: P171_ENAME_EXISTS, value: Y)

Run the page and create a new employee. If you put in an existing name, the alert will fire.