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

Calling a REST webservice with pl/sql

For a customer i needed to call a external REST webservice to update an external database with some specific information.

With the use of the utl_http package Oracle has made this very simple.

I tried to make the procedure i made, self-explaining.

create or replace procedure call_rest_webservice
  t_http_req     utl_http.req;
  t_http_resp    utl_http.resp;
  t_request_body varchar2(30000);
  t_respond      varchar2(30000);
  t_start_pos    integer := 1;
  t_output       varchar2(2000);

  /*Construct the information you want to send to the webservice.
    Normally this would be in a xml structure. But for a REST-
	webservice this is not mandatory. The webservice i needed to
	call excepts plain test.*/
  t_request_body := 'the data you want to send to the webservice';

  /*Telling Oracle where the webservice can be found, what kind of request is made
    and the version of the HTTP*/
  t_http_req:= utl_http.begin_request( 'http://the_url_of_the_webservice'
                                     , 'POST'
                                     , 'HTTP/1.1');

  /*In my case the webservice used authentication with a username an password
    that was provided to me. You can skip this line if it's a public webservice.*/

  /*Describe in the request-header what kind of data is send*/
  utl_http.set_header(t_http_req, 'Content-Type', 'text/xml charset=UTF-8');

  /*Describe in the request-header the lengt of the data*/
  utl_http.set_header(t_http_req, 'Content-Length', length(t_request_body));

  /*Put the data in de body of the request*/
  utl_http.write_text(t_http_req, t_request_body);

  /*make the actual request to the webservice en catch the responce in a
  t_http_resp:= utl_http.get_response(t_http_req);

  /*Read the body of the response, so you can find out if the information was
    received ok by the webservice.
    Go to the documentation of the webservice for what kind of responce you
    should expect. In my case it was:
  utl_http.read_text(t_http_resp, t_respond);

  /*Some closing?1 Releasing some memory, i think....*/

Execute Page process with Ajax callback

Sometimes you want to call a page-proces, but do need to make a compleet refresh of the page by making a request.

In my case I made a simple master-detail screen with Tabs (Thank you Richard). In the tabs there are reports with a delete button for each row. For the delete operation I did not want refresh the complete page, but only wanted to call the page-proces I had written for the delete-operation and only wanted to refresh the report-region affected.

This is wat i did:

  • I made a normal report and added an icon for the delete operation. The icon for the delete operation is the id-column in the report (PRODUCT_VERWIJZING_ID) and has the follwoing column link properties:
    • link text: <img src=”#IMAGE_PREFIX#del.gif” alt=””>
    • target: URL
    • URL: javascript:del_verwijzing(#PRODUCT_VERWIJZING_ID#)

Continue reading

Create collapsible list (alternative to apex tree)

After some testing en struggling with the Apex-tree item I decided to look for an alternative for an expanding and collapsable html-list.

Because of the integration with Jquery in apex, i obviously looked for an apex plug-in and found a really ease one.

The plug-in is written by Milaju and the source and documentation can be found at:

You can find a demo at:

How to integrate:

  1. Download the source-file from
  2. Extract the zip-file
  3. Go to Shared Components – Static files
  4. Upload the files “jquery.collapse.js” and “jquery.easing.js”. You do not need to upload the file “jquery.js”, because it’s already available within Apex
  5. Go to the page where you want to make the list
  6. Create a new HTML-region named “_Javascript”; display point: After Header (I always use a separate region were I put custom javascript. So when I open a page I can see if there is any custom javascript).
  7. Put in the region source:
    <script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.easing.js"></script>
    <script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.collapse.js"></script>
    <script type="text/javascript">
    	      slide: true,
    		  speed: 500,
    		  easing: 'easeOutCubic'
  8. Make a new report region which makes the list-output as shown under. (the most important thing is to give the first ul-element the id “collapser” and keep the structure of the elements UL en LI the same)
    <ul id="collapser">
      <li>Item 1
    	  <li>Item 1.1</li>
    	  <li>Item 1.2
    		  <li>Item 1.2.1</li>
    		  <li>Item 1.2.2</li>
    	  <li>Item 1.3</li>
      <li>Item 2
    	  <li>Item 2.1
    		  <li>Item 2.1.1</li>
    		  <li>Item 2.2.2</li>
      <li>Item 3</li>
  9. And voilà! The list is done! Easy, not?!
<ul id=”collapser”>
<li>Item 1
<li>Item 1.1</li>
<li>Item 1.2
<li>Item 1.2.1</li>
<li>Item 1.2.2</li>
<li>Item 1.3</li>
<li>Item 2
<li>Item 2.1
<li>Item 2.1.1</li>
<li>Item 2.2.2</li>
<li>Item 3</li>

Manage HTTP Access in Application Express 3.0

Wow, Richard and I have been strugling with APEX 4 on XE for hours before we came across this port:

It would have saved us hours.. thanks Marcin!


After default installation of Oracle APEX 3.0, remote users are not allowed to access Database home page (APEXconsole)

  • http://localhost:8080/apex
  • (not allowed by default from remote location)

This is because the listener local access setting is being set to local only by default.

To set HTTP Access to allow remote connects:

exec dbms_xdb.setListenerLocalAccess (l_access => FALSE);

To set HTTP Access back to Local only:

exec dbms_xdb.setListenerLocalAccess (l_access => TRUE);

Another way to set this up is to login to APEX dashboard as a sys user and navigate to: Home > Administration > Manage HTTP Access (Tasks’ list on the right side of the page) and change the settings: