Set value of items by changing select list using Ajax and JSON

For a client I needed to develop an Apex page were some items were set when the user changed the value of a select list. Because of user-experience I did not want to do a post to the server. Instead I wanted to use Javascript and Ajax. This blog tells how I implemented this.

Thanx to Roel’s comment, there is a more easy way; just make a (advanced) dynamic action on the list-item which sets the items based on a select statement. So, this blog is just to get some more depth in javascript-code & JSON.

Basically the page consists three items:

  1. A select list were the user can pick a product.
  2. A display item were the price of the product is shown.
  3. A display item were the supplier of the product is shown.

Task to accomplish
When the user selects a product, the values of price and customer had to change to the corresponding values in the database.

Steps to accomplish this task:

  1. Create a procedure which sends the price and supplier in Json-format depending on the product-id
    create or replace procedure get_product_info (p_product_id in number)
      t_sql varchar2(256);
      t_sql := 'SELECT price, supplier FROM products WHERE id = '||p_poduct_id;
      /*Print JSON result set*/
      apex_util.json_from_sql (t_sql);
  2. Create a pl/sql page-process productInfo (point: on-demand)
    get_product_info(p_product_id => :P3000_PRODUCT_ID);
  3. Add javascript to your page
    function ChooseProduct()
    //the application proces to be executes (prepare ajax-call)
    var get = new htmldb_Get(null,&APP_ID., 'APPLICATION_PROCESS= productInfo',&APP_PAGE_ID.);
    //set the value of product-id to the value of the item 
    //exceute the ajax-call and get the reponse in JSON format
    var gReturn = get.get();
    // make from the response string a JSON object
    var obj = eval ("(" + gReturn + ")");
    //set the value of the items, based on the response
  4. Add a dynamic action to the select list which executes the javascript function “ChooseProduct();”, when the value is changed.

That’s it!
Sergei Martens

2 thoughts on “Set value of items by changing select list using Ajax and JSON”

  • What version of APEX are you using?
    Because I think you can accomplish the same result by creating a Dynamic Action on that select list that does either a “Set Value” or a PL./SQL process. Just use those two fields as the return fields.
    So no need for a single line of JavaScript…just one select statement would be sufficient…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>