René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

November 30, 2006: On creating an AJAX Application with Oracle

With the PL/SQL gateway, it's possible to invoke PL/SQL procedures via HTTP (or a browser, for that matter) and to return HTML pages to the browser. Since AJAX seems (or at least seemed) to be quite popular at the moment, I decided to find out if it is possible to create an AJAX web application with the PL/SQL gateway. To make the story short: it is. In this article, I am going to show how I did it. I used Oracle Express Edition 10 (Oracle XE) on Windows more or less out of the box.
First, I connect as system ...
connect system
... in order to create the ajax user:
create user            ajax
  identified by        ajax
  default   tablespace users
  temporary tablespace temp
  quota unlimited on   users;
This user will then create a package, so I grant him the necessary system privileges:
grant create session,
      create procedure
to ajax;
I am ready to create the package.
connect ajax/ajax
The package specification has two procedures only: main and get_sysdate. main will display the HTML page in which the AJAX application lives while get_sysdate will simply return the sysdate in my preferred format.
create package ajax_pck as

  procedure main;

  procedure get_sysdate;

end ajax_pck;
Here's the package body. The body has an additional function javascript which generates the necessary javascript code for the AJAX interaction.
create package body ajax_pck as 

  function javascript return varchar2;

  procedure main is begin

     -- Insert the needed Java Script into the main page:
     htp.p('<script>' || javascript || '</script>');

     htp.p('<div id="sysdate">n/a</div><br>');
     htp.p('<a href="javascript:send_req()">Display Sysdate</a>');
  end main;

  procedure get_sysdate is begin
    htp.p(to_char(sysdate, ' hh24:mm:ss')); 
  end get_sysdate;

  -- See Rasmus' 30 second AJAX Tutorial
  function javascript return varchar2 is begin

    -- The quote operator comes in real handy here:
    return q'#

      function cr_req_obj() {
          var browser = navigator.appName;
          if(browser == "Microsoft Internet Explorer"){
            return new ActiveXObject("Microsoft.XMLHTTP");
            return new XMLHttpRequest();
      var http_req = cr_req_obj();
      function send_req() {
'GET', '/apex/ajax.ajax_pck.get_sysdate');
          http_req.onreadystatechange = handle_response;
      function handle_response() {
          if(http_req.readyState == 4){
              var response = http_req.responseText;
              if(response.indexOf('|' != -1)) {
                  document.getElementById('sysdate').innerHTML = response;


  end javascript;

end ajax_pck;

show errors
Since the PL/SQL gateway runs as the anonymous user, I need to grant the execution object privilege on ajax_pck.
grant execute on ajax_pck to anonymous;
PL/SQL gateway needs to know what procedures it is allowed to invoke. This is done in the wwv_flow_epg_include_mod_local function that belongs to the user flows_020100. flows_020100 cannot connect to the database as its account is locked. So, I connect as system ...
connect system
and alter the procedure being system account (See example 1 for more details):
create or replace function flows_020100.wwv_flow_epg_include_mod_local (
  procedure_name in varchar2
) return boolean
is begin

    return true;

    if procedure_name like 'AJAX.AJAX_PCK%' then 
       return true;
    end if;

    return false;

end wwv_flow_epg_include_mod_local;

show errors
The application can then be started by pointing the browser to:
If the browser is on a different machine than the Oracle Server, localhost must be replaced by the name or IP address of the machine where the Oracle server is located.


I liked Rasmus' 30 second AJAX Tutorial very much when I created that example.
Then there are also example 1 and example 2 of the PL/SQL gateway.