| 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.
|