Forum Stats

  • 3,781,165 Users
  • 2,254,486 Discussions
  • 7,879,602 Comments

Discussions

APEX 20.2 - How to run a PLSQL Process once before/after IG DML

TMohamme
TMohamme Member Posts: 57 Red Ribbon
edited Oct 31, 2021 6:43AM in APEX Discussions

Hi APEXperts,

Is there a way to execute a package procedure "only once" before/after grid Interactive Grid - Automatic Row Processing (DML) ?

When I tried Type = "Execute Code" and Editable region assigned, the package procedure is being called for every modified/created row.

One workaround I could think of is to use global variable in the package procedure to execute it only once.

IF NOT g_already_executed THEN
  -- do my process logic

  -- set  the global variable to true
  g_already_executed  := TRUE;
END IF;


But I would like this to be possible within APEX framework without the need for workarounds.

Assuming this is not possible using APEX provided processes already, I have posted this as an feature request. If this is not possible OOTB from APEX and if you too happen to run into this use case, you can up-vote the idea.

https://apexapps.oracle.com/pls/apex/apex_pm/r/ideas/details?idea=FR-2075

Thanks

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,946 Silver Crown

    APEX uses Connection Pooling. This makes session level stuff (package variables, GTT) a "bad idea(tm)" if you expect to use it between pages/calls.

    You should probably use a hidden Item to keep track.

    If you need to only run it "one time per login", use a Collection.

    For security things (VPD, RLS, RAS), use an application CONTEXT.

  • TMohamme
    TMohamme Member Posts: 57 Red Ribbon
    edited Oct 31, 2021 6:20PM

    Using a package variable is not a bad idea if the said variable is explicitly initialized for every db call without depending on the package instantiation per session.I do that already. Same is the case with hidden item. I have to make sure it is initialized for every DB call (Cant simply depend on Session state set during pre-rendering).

    The sample code I pasted is just to give an idea.It doesn't include initialization of global variable in actual DML process.

    If you need to only run it "one time per login", use a Collection.

    And No its not one time per login. As mentioned in the question, its once per IG DML.

    also why to use collection for "one time per login" as we already have "After Authentication" process point. Nevertheless, this is not my question.

  • Mike Kutz
    Mike Kutz Member Posts: 5,946 Silver Crown

    also why to use collection for "one time per login" as we already have "After Authentication" process point.

    I was thinking about "setting a variable to be used elsewhere". (Which would be set via after authentication process).

    Using a package variable is not a bad idea if the said variable is explicitly initialized for every db call without depending on the package instantiation per session.

    Every region can be a new DB call. When you refresh the IG, that's a new DB call. If the end user adds filters/go to a different page of data, that's a new DB call.

    APEX uses Connection Pooling.

    There's no telling if another APEX session reset those variables. It's the same problem as GTTs in APEX.

    DB sessions are shared between APEX sessions. There is no guarantee that you'll get the same session as before.

    Is there a way to execute a package procedure "only once" before/after grid Interactive Grid - Automatic Row Processing (DML) ?

    What is the Business Requirement are you trying to solves?

    Easiest way would be storing the "I already ran" variable as a hidden Item on a page.

    But, I'm just making sure you don't have an XY Problem.