Process executes when condition is not met

Eslam_Elbyaly

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.

Ananya Chatterjee-Oracle

Hello,

You mentioned that the unique constraint is on the doctor_id,name so the update statement might be trying to change the p1_patient_name to a value that was already inserted. During the execution of the update statement try to check what value of p1_patient_name is getting set.

Eslam_Elbyaly

https://community.oracle.com/tech/developers/discussion/comment/16844307#Comment_16844307

Impossible. The name, doctor_id already exists. It can't exist if there's a similar name, doctor_id

Eslam_Elbyaly

https://community.oracle.com/tech/developers/discussion/comment/16844307#Comment_16844307

I tried commenting the "IF :P1_PATIENT_ID IS NULL...." part, and the weird part is that the ARP process threw error " ORA-01407 - can not change test.patient_id to null". Which means that it issues update statement with p1_patient_id set to null when it's not. I am sure it's not because I even changed it to a number item instead of a hidden one and it has a value.

If I do not comment the "IF....." part, and disable the unique constraint in patient table, I get a new record inserted, which means that the "IF..." part executes. It's a very weird thing to happen!

Eslam_Elbyaly

The message in Arabic means, p1_patient_id must have a value. See p1_patient_id below has a value!

Eslam_Elbyaly

I found the culprit and I can assure you it's definitely a bug @John Snyders-Oracle. There's a "loseFocus" DA on p1_patient_name with the same code of the "onUpdate" DA(Set value> select statement - I mentioned it in the main question)...

select id from patient where name = :p1_patient_name ;

. I created it because user could write the name instead of choosing it from the lov. In this case, the "onUpdate" DA won't fire.

I am focusing on the first item in the page which is p1_patient_id. The lose focus trigger fires when click SAVE because the cursor is in p1_patient_id then. The weird thing is that, even if it fires, it should set the value of p1_patient_id again not setting it to null. Plus it's not get set to null. I can still see the value in p1_patient_id.

When I set the lose focus DA to Never, the error did not show anymore.

I think this assures it's a very bad bug. The question now is, is there a workaround?

P.S. I don't think this problem exists on APEX 22.1.3 on apex.oracle.com. I tried it but not sure if I mimicked it correctly.