René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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, 'dd.mm.yyyy 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"); }else{ return new XMLHttpRequest(); } } var http_req = cr_req_obj(); function send_req() { http_req.open('GET', '/apex/ajax.ajax_pck.get_sysdate'); http_req.onreadystatechange = handle_response; http_req.send(null); } 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:
http://localhost:8080/apex/ajax.ajax_pck.main
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.
Links
I liked Rasmus' 30 second AJAX Tutorial very much when I created that example.
|