Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Process executes when condition is not met

Eslam_ElbyalyAug 15 2022

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.

Comments

Anirban1

Hi,

Create a dummy session variable, put that session variable in a prompt as request variable, this will override the session variable you mentioned in the RPD. Now use that session variable to populate your different session variable.

Let me know if it resolves your issue.

Thanks

Anirban

anwickes

Thanks for the response Anirban.

I was thinking about the theory of this last night and don't think this will end up helping me anyway.

Correct me if i'm wrong.

Session variables are initialized when a user logs in.

This means that if I had 2 session variables set up (A & B), A being the dummy variable overridden by the request variable and B being something like "Dateadd(valueof(A),-1)", B would be initialized before A was overridden. This means I would still have to write a piece of code to overwrite each session variable anyway.

My main objective is to have 6 session variables. One holding the date prompted. 2-6 holding 1 month ago, 2 month ago, 3 month ago etc.

Is there an easier way? It seems like such an easy thing to do.

Cheers,

Adam

anwickes

Alright.... Think i was doing things the hard way.
For those people who are trying to do the same thing and find this thread, this is what I did.

In my time dimension, I created 5 extra columns and calculated the previous 5 months worth of dates.

Then in my main date prompt (That the user will see), I made it set a presentation variable (p_Var).

I then created another prompt with 5 "column variables". Each of the 5 prompts will use the new columns in the time dimension.

eg: prompt 1 = "Time Dim"."1 Month Ago", prompt 2 = "Time Dim"."2 Months Ago" etc

In the default selection for each new prompt, select "SQL RESULTS" and type something like:

1 Month Ago = SELECT "Time"."Month - 1 Month Ago" FROM "RPD_Name" WHERE "Time"."Month" = '@{p_Var}';

2 Months Ago = SELECT "Time"."Month - 2 Month Ago" FROM "RPD_Name" WHERE "Time"."Month" = '@{p_Var}';

etc


Each of the 5 prompts set new presentation variables. ie p_Var1, p_Var2, p_Var3 etc

I then dragged the new prompt onto the dashboard and set it to invisible by typing "display:none" in the Custom CSS Style of the section formatting options.

To update the column names to use the prompt values, we can now refer to the presentation values we have created by using the following in the column name:

@{dashboard.variables['p_Var1']}

Hope that has helped guys

I am certainly happy now that i've worked it out.

Christian Berg-0racle

anwickes wrote:

This means that if I had 2 session variables set up (A & B), A being the dummy variable overridden by the request variable and B being something like "Dateadd(valueof(A),-1)", B would be initialized before A was overridden. This means I would still have to write a piece of code to overwrite each session variable anyway.

Hmm no, since nothing prevents you from nesting variables which in the example state here would give:

B = "Dateadd(@{pvMyPresVar}{VALUEOF(NQ_SESSION.A)},-1)"

anwickes

But B would be initialised on session start which is before A is overridden by the prompt?

Christian Berg-0racle

Sorry, I meant literally use "Dateadd(@{pvMyPresVar}{VALUEOF(NQ_SESSION.A)},-1)" instead of B. Should have been "B => ..."

anwickes

i don't think you can use dateadd in a column name? Could be wrong though.

Christian Berg-0racle

Well TIMESTAMPADD(SQL_TSI_DAY...  , I just copied your formula for arguments sake.

anwickes

I will try this tomorrow but I didn't think you could do any manipulation of variables in a column name.

Understand your method would work in a column formula but not sure about column name.

1 - 9

Post Details

Added on Aug 15 2022
5 comments
154 views