|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 ...
... 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.
The package specification has two procedures only:
create package ajax_pck as procedure main; procedure get_sysdate; end ajax_pck; /
Here's the package body. The body has an additional function
Since the PL/SQL gateway runs as the anonymous user, I need to grant the execution object privilege on
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
and alter the procedure being system account (See example 1 for more details):
The application can then be started by pointing the browser to:
If the browser is on a different machine than the Oracle Server,
I liked Rasmus' 30 second AJAX Tutorial very much when I created that example.