ORACLE APEX IF STATEMENT IN A FORM

Question

I am new in APEX and i am trying to change a value to an item in a form based on an if condition. Can someone explain to me with a simple example how can i use IF/ELSIF/ELSE statement to a dynamic action in order to calculate a value and use this value to an item of a form?

For example

if itemA > 0 then
  ItemB = ItemA + 5
elsif ItemA =0 ItemB= ItemB + 1

Also how can i use a PL/SQL to Update some rows in a table using a value from my item in a form with dynamic action? I tried to execute PL/SQL Code in the Identification of Dynamic Action, but nothing happens. I also tried to Set Value Action of the Identification using as set types (PL/SQL expression, PL/SQL Function Body, but still nothing happens). Thanks

TineO TineO · Accepted Answer · 2020-06-18T12:31:01

welcome to APEX.

So firstly, if you want to reference an item on a page it usually has a name like P1_NEW. Where the 1 is the page number, and the NEW is something you usually change to reflect what it actually is.

You reference that item by putting : infront of it.

So the code would be something like:

IF :P1_ITEM_A > 0  THEN
  :P1_ITEM_B := :P1_ITEM_A + 5;
ELSIF :P1_ITEM_A = 0 THEN
  :P1_ITEM_B := :P1_ITEM_B + 1;
END IF;

You always need to finish a statement with ; and an IF needs to be closed, so you need to end it with "END IF;" And you should also submit the item if the code is in a Dynamic Action. Submitting basically means that the value actually gets stored and isnt just displayed. This you will stumble upon if you for example want two empty items that you write into and a button to fill a third item based on the first two, if the first two arent submitted then the dynamic action will see them as empty.

As for the second part of your question I am not entirely sure what it is you are asking, will need more knowledge on what the page looks like and what you want to accomplish.

EDIT TO ANSWER COMMENT: I think what you have is an Interactive Report with a form. Meaning you click on a pencil icon in the Table and a form opens up with these three items, SAVE and CANCEL buttons.

Firstly, one thing you should always be trying when dealing with DAs(dynamic actions) is to have the DA trigger an action Alert. This way you see if it actually triggered.

Your setup should trigger just fine. Once you confirm that the DA is being triggered, set the Action to PLSQL code. From what you told me I think you should set the Items to Submit: P5002_OverTimes, P5002_Salary

And Items to Return:P5002_OverTimes, P5002_Salary

And the PLSQL code being:

IF :P5002_OverTimes > 5 THEN
  :P5002_Salary := :P5002_Salary + 5;
ELSIF :P5002_OverTimes = 5 THEN
  :P5002_Salary := :P5002_Salary + 2;
ELSE
  :P5002_Salary := :P5002_Salary + 1;
END IF;

Then just to make sure I like to add another Action, also PLSQL code, the code being just "NULL;" and I again submit the items.

Then the change should be visible on the page(the items should have the new values) and you can Save and those values will go into the table.

The IF statement is in the correct spot for this scenario. What you are probably thinking of is Client Side condition. This is so you can set the DA to trigger only if a certain condition is met. In your case you could theoretically have three DAs with client side conditions that check the value of OverTime, one where VALUE > 5, one where VALUE = 5, and one where VALUE < 5, and they each have an action Set Value. This could be done, but its just needlessly complicated and more work than is needed.

ORACLE APEX IF STATEMENT IN A FORM

1 Answers