Oracle APEX | How to change select list value and Submit it dynamically

Question

I have two select lists

1. The first one P4_country_id_B contains countries names

    select country_name as d, country_id as r 
    from countries

2. The second one P4_CITY_ID_B contains cities of a country based on selected value in P4_CITY_ID_B.

    select city_name as d, city_id as r 
    from cities 
    where country_id = :P4_country_id_B

Everything goes OK without any problem.

BUT

I use Execute PL/SQL Code Dynamic Action to change selected values of those lists like this (for example):

:P4_country_id_B:=country_returned_value;
:P4_CITY_ID_B:=city_returned_value;

Where

country_returned_value : is a one value of countries list values for example (USA)
city_returned_value    : is a one value of cities list values for example (NewYourk).

The first selected list value changes but the second list never changes.

Notes:

I used P4_country_id_B,P4_CITY_ID_B as Page Items to Submit for the dynamic action.
- I don't whan to submit the page instead of dynamic action

How can I change list values in this case please?.

Thanks in advance.

Tom Tom · Accepted Answer · 2016-08-16T06:42:08

Cascading select lists are refreshed through ajax.
Change select list 1, select list 2 will be refreshed.
You execute plsql, which in turn will set the value of the items involved. Both are select lists, and one is dependent on the other.
So while both will be set, the change of the first one will cause the second to be refreshed.
In other words, you're doing it too fast. And while there is a solution, the proper way is a bit complex and I wouldn't build it in DA's personally.

You haven't specified how or when you call the code which sets the values for the items. So here I'll just assume a DA with an action of type "Execute JavaScript" (for example)

// perform a call to the ajax callback process to get the country and city id
// there are several ways to provide values to the process if necessary.
// for example through x01, which can be used in the proces like
//   apex_application.g_x01
apex.server.process('GET_COUNTRY_DEFAULTS', {x01:""}).done(function(data){
  // process returns JSON which contains 2 properties: country_id and city_id
  // data.country_id 
  // data.city_id

  // bind a ONE-TIME handler to the after refresh event of the city
  // cascading LOVs fire the before and after refresh events like any other
  // refreshable element in apex
  // a one time handler since the values are different each time this code will
  // execute
  apex.jQuery("#Px_CITY_ID").one("apexafterrefresh",function(){
    // after refresh of the list, attempt to set the value of the list to the
    // value retrieved earlier
    apex.item(this).setValue(data.city_id);  
  });  

  // finally, set the value of the country. Doing this will also trigger the 
  // refresh of dependent elements
  apex.item('Px_CITY_ID').setValue(data.country_id);

  // since a handler has been bound, the refresh will occur, the after refresh
  // triggers, and the value will be set properly
});

Finally, create a new process on the page under "AJAX Callback", and name it GET_COUNTRY_DEFAULTS

DECLARE
  l_country_id NUMBER;
  l_city_id NUMBER;
BEGIN
  l_country_id := 8;
  l_city_id := 789;

  htp.p('{"country_id":'||l_country_id||',"city_id":'||l_city_id||'}');
EXCEPTION WHEN OTHERS THEN
  -- returning an error while using apex.server.process will prompt the user
  -- with the error and halt further processing
  htp.p('{"error":"'||sqlerrm||'"}');
END;

That should tie everything together.

Oracle APEX | How to change select list value and Submit it dynamically

2 Answers