Hi. I am using APEX 22.1.1.
I have a page with a form on a table "test". The form's based on a sql query joining multiple tables. The form has p1_patient_id(Hidden item with value protected=no) of table "test" and p1_patient_name of table "patient". p1_patient_name is a text field with autocomplete. It's autocomplete to enable user to whether choose a name from the item's lov or to enter a new name. It has a set value>sql statement dynamic action that sets p1_patient_id whenever the user choose a value from p1_patient_name. If user choose value from the item's lov, the DA will return an id into :p1_patient_id. If user writes a name that's not in the item's lov, the p1_patient_id will be null. The DA says the following...
select id from patient where name = :p1_patient_name ;
Affected element is p1_patient_id. Items to submit is p1_patient_name.
There's a button to submit the page and execute an Insert action. And a similar one to execute Update.
There's a unique constraint on patient(doctor_id, name).
There's a proesss that should insert data into PATIENT table only if p1_patient_id is null. Here's the code...
IF :P1_PATIENT_ID IS NULL THEN
:P1_PATIENT_ID := PATIENT_SEQ.nextval;
insert into patient(ID, NAME, ADDRESS, AGE, DOCTOR_ID)
values (:P1_PATIENT_ID, :P1_PATIENT_NAME, :P1_ADDRESS, :P1_AGE, :P9999_USER_ID);
ELSIF :P1_PATIENT_ID IS NOT NULL THEN
UPDATE PATIENT SET
NAME = :P1_PATIENT_NAME,
ADDRESS = :P1_ADDRESS,
AGE = :P1_AGE
where ID = :P1_PATIENT_ID
;
END IF;
The problem is there's something weird happens. I do not know if it's a bug or not. When I create a new record, User selects a value from p1_patient_name, the DA fires and sets p1_patient_id. When click submit, the "test" table's record gets inserted successfully and the process part...
ELSIF :P1_PATIENT_ID IS NOT NULL THEN
UPDATE PATIENT SET
NAME = :P1_PATIENT_NAME,
ADDRESS = :P1_ADDRESS,
AGE = :P1_AGE
where ID = :P1_PATIENT_ID
executes. If I click the "Update" button without changing anything, I get "Unique constraint violated" error of patient(doctor_id, name). Which means that the process part...
IF :P1_PATIENT_ID IS NULL THEN
:P1_PATIENT_ID := PATIENT_SEQ.nextval;
insert into patient(ID, NAME, ADDRESS, AGE, DOCTOR_ID)
values (:P1_PATIENT_ID, :P1_PATIENT_NAME, :P1_ADDRESS, :P1_AGE, :P9999_USER_ID);
gets executed. That shouldn't happen because p1_patient_id has a value. I can assure that because the page branches to itself and if I execute "$v(p1_patient_id) from the console, I get a value.
If I click the same button "update" again, the page submits successfully. If clicked again, it throws the unique constraint error and so on so forth. First time without error, second with, third without, 4th with and so on.
p1_patient_id's "maintain session state= per request(memory only).
p1_patient_name's maintain session state= per disk.
I tried to simulate the problem on apex.oracle.com but I failed to reproduce it.