1

I have a tabular form based on a simple sql like 

select col1, col2, check_box from view1

users can update the check_box in the form, and col1 and col2 will be manipulated based on whether the check_box is checked or not. Then the base table will be updated in the DB by the MRU process I created.

The issue I have is, besides updating the base table using MRU, I also want to call another proc to do something else based on check_box in the form

e.g. 

if check_box is unchecked, then col1 and col2 will be blanked by the MRU;
if check_box is checked, col1 and col2 will be updated to 'Done' by the MRU,
also a proc should be called to do something else.

My question is, should I stick with the MRU approach(as MRU has its upside including lost update detection, locking etc...), and create a seperate process to to call the proc, or should I just create a process that does both (something like looping through each line of the report, update the base table and when the check_box is checked, call the proc)?

what would be the better apporach?

William Mu
  • 115
  • 5
  • 16
  • Are you sure you're talking about an interactive report and not a tabular form (updateable sql query)? – Tom Aug 29 '16 at 19:45
  • Sorry my bad... Yea you are right, it's a tabular form. – William Mu Aug 29 '16 at 20:24
  • If the procedure needs to be run for each record that is updated, then put it in the same MRU process. If it's only to be run once per page request, then you would create it as a separate process. – Jeffrey Kemp Aug 30 '16 at 05:16
  • Thanks Jeffrey. What I want to do, is for each modified row, I want to execute something like MY_PROCEDURE(). how to put this in the MRU process? I mean I can create a MRU process, and set the scope to "for created and modified rows only", which will update whatever has been changed to the DB. how do I make the MRU process to execute a separate proc as well? – William Mu Aug 30 '16 at 12:51

1 Answers1

1

You can associate a process with a tabular form. Doing this will cause the process to be run for each record in the tabular form (though you can specify the execution scope so it'll run for each row or only for new/updated rows).
In this process you can then refer to each column in the tabular form by using bind variable syntax. If the column is updateable, you can also alter the value. You could have a process which runs before the MRU and alters the value of col1 and col2. 

IF :CHECK_COL IS NULL THEN
  :COL1 := NULL;
  :COL2 := NULL;
ELSE
  :COL1 := 'Done';
  :COL2 := 'Done';
END IF;

Then create a process to be run after the MRU and runs the procedure. 

MY_PROCEDURE(:SOME_COL);

Keep in mind that if the procedure performs DML on records which are altered by the MRU, running this procedure would cause the MRU to fail due to lost update detection (non-matching checksums).

Tom
  • 6,988
  • 1
  • 26
  • 40
  • Tom, is there a way to specify a procedure or package procedure so that it only runs once for the whole tabular form, and have some kind of loop in this procedure to iterate through the rows? – Thomas Tschernich Aug 31 '16 at 11:45
  • @ThomasTschernich there is - though I'm not sure what the difference would be? Here apex will call the process (and take note: you CAN check the status of the row (created, updated)) for each row, while in your loop, you will call the process for ...each row? Unless you want to do some other, advanced thing? – Tom Aug 31 '16 at 11:58
  • Maybe just if there are dependencies between the rows. I cannot supply an example right now, but just in case I stumble upon this at a certain point of time in the future. I found some additional information [here](http://stackoverflow.com/questions/3962731/update-apex-tabular-form-with-plsql?rq=1), but that is very old and seems hardly documented. – Thomas Tschernich Aug 31 '16 at 12:04
  • @ThomasTschernich the info you linked is still relevant, so you can certainly build from there. All items in a tabform are mapped to one of the `apex_application.g_f##` arrays. You can loop over those if you ever need to process them in such a way. The more "modern" version would be to use a tabular form-associated process such as I mentioned . But the arrays are certainly still valid. They're also common practice as it's the same way in using manual tabforms created through using the `apex_item` api. – Tom Aug 31 '16 at 12:12