Rectangle 27 0

Process Flow Control in Oracle APEX

(This is actually useful to think about in other programming disciplines and environments.)

The problem is that the page item (:P4550_REQUESTOR) is not populated with a value until a conditional is met. It appears that the PL/SQL block process is binding the variable to an empty value as soon as the page is loaded, despite the fact that the process does not fire until a specific button has been clicked.

  • There is a REPORT REGION on the page which contains the result of a direct reference to a data table/view. This report is managed by an Apex process called "Automated Fetch" and is initiated automatically by the loading of the page headers.
  • There is a FORM ITEM on a page which which is populated conditionally by a BUTTON ITEM selection made by the user. The BUTTON ITEM is part of the report results.
  • There are multiple button items. Each is associated with a value for each report record.
  • If the user does not select the BUTTON ITEM from the REPORT REGION, the FORM ITEM remains unassigned and contains a "null" value.

There is a defined PL/SQL block of code which is set to execute when a SUBMIT BUTTON item is pressed (also on the same page). Why does my code block (defined page process) run with a null value when it is triggered without first pressing a BUTTON ITEM from the REPORT REGION first?

The answer is not obvious if you think under the paradigm of a procedural language. Without diving into a lecture on the topic, here's a visual layout of the problem space of the OP that I cooked up to illustrate how the problem can be made more obvious:

This is my Apex page design in implementation. It's generic enough to use as a template for other Apex designs. There are no flow arrows on this diagram because it's a stateful system. One thing causes another thing to happen and so on... but not always and not all at the same time.

Try walking through a few use cases to understand how the elements broken down in the diagram operate together. Each user may take any number of click combinations and interactions, but there is a commonality:

  • They all enter the same initialized conditions on page load.
  • They all leave the page by: navigating elsewhere or through the SUBMIT button event.
  • User enters page and reviews results displayed in the {MyPage:SQLReport} region.
  • User decides no additional input is necessary and then selects the {MyPage:HTML-Region:ThisSubmit} button to inform the system to continue on.
  • (a note: the state of form item {MyPage:HTML-Region:ThisItem} has not been changed from the initial null value at this point... after the submit button has been selected)
{MyPage:SQLReport:ThisButton}
{MyPage:SQLReport}
{MyPage:SQLReport:ThisButton} #3
{MyPage:HTML-Region:ThisItem}
  • The form item state has been updated and changed from the initial value stored in step (2).
{MyPage:HTML-Region:ThisSubmit}
  • The submit button executes the defined PL/SQL procedure block: {MyPage:RunCodeBlock}

The difference between each case should illustrate why the dependent value (ThisItem, or more specifically, page item P4550_REQUESTOR) is null in one use case vs. the other.

The table I used is called STAR_EMPS. It is similar to the EMP table but has only three columns: ename, deptno and salary. Although it is not super important, this is the data set I used to populate STAR_EMPS:

I used a simple two-column table named STAR_EMPS_LOG for capturing the output of a successfully executed procedure call. You could accomplish the same with just one column, but I wanted a sequential id for tracking the order each event was recorded- for running multiple test cases. The procedure is one of several defined processes kept on this page:

DECLARE
      -- output from this procedure will be recorded in the star_emps_log
      -- table.  {MyPage:RunCodeBlock}

      mycelebrity  star_emps.ename%TYPE:= :P17_CELEBRITY_NAME;
      mylogmessage star_emps_log.log_message%TYPE;

    BEGIN
      -- Conditional; changes message based on the value set for the
      -- page item.

         if mycelebrity is null then
         mylogmessage:= 'No button was pressed on the previous page.';
     else 
         mylogmessage:= 'The user selected: ' || mycelebrity ||
         ' from the report list.';
     end if;

     -- populate value from the page item.
        INSERT INTO star_emps_log (log_message)
           VALUES (mylogmessage);
        commit;

    END;

This is how the page layout was set up:

  • As in your example, I made a {MyPage:SQLReport} region with its supporting elements. The SQL Report represents a query directed at the source data table.
  • {MyPage:SQLReport} is defined by a SQL query, there is also a mock column to use as a place holder for placement of the "edit" buttons.

The TWO Page processes: PROCESS and BRANCH need to be linked with the same settings referencing a BUTTON triggering Item.

Run through the three suggested scenarios to get started. Verify that the system is interpreting the requests correctly. This is what the page layout looks like:

The two processes on the system have a definition that wasn't mentioned in previous discussions may solve our original problem at hand:

It is a good thing this turns out to be a trivial case once broken down. The diagramming method described here should scale to other Apex applications of varying complexity. There is considerable utility in stepping away from the code, locking down on terminology and trying to describe systems and processes without actual code. Please be sure to share any stories if this approach helps with your own Oracle Apex design challenges.

Thank you for the diagram. It is definitely a useful template to encourage me step back and look at the application/page design and interaction of the related pieces. A couple of things to note about the setup of my page in relation to your diagram. 1) MyPage:Report is handled by a standard SQL Query; 2) MyPage:Form is actually MyPage:HTML Region. The problem I was facing, I was not able to retrieve the {MyPage:HTML:ThisItem} for use in the {MyPage:RunCodeBlock} AFTER {MyPage:Report:ThisButton} had been pressed, even though the item contained a value. 'Save Session State' solved this.

So, my question is, why? Is it required to have these values in the session state to use in a Page Process that fires On Submit? I'm still rather new to APEX, so trying to get an understanding of the intricacies of how this works. Thanks!

I like your line of thinking. Keep asking why! I even appreciate your effort to communicate back using the design terminology I threw together. I built a one-page example using the design we have discussed. I will try to explain the "Save Session State" and why it worked, but also how you can accomplish the same task without it.

Thanks, Richard, for the thorough feedback. It's much appreciated.

plsql - Application Express: Anonymous PL/SQL Block and Bind Variables...

plsql oracle11g oracle-apex