Since installation, the Oracle 10g XE on my desktop has mostly been used for quickly testing how database stuff works. Last week I finally got around to digging a bit deeper into the HTMLDB/Application Express part.
Lets say you have batch jobs on your system. Every morning management requires a report of the previous day’s run. Fortunately, your batch jobs log their status in a table. Now you could always run a query against this table, spool the data and email it to management. Or you could create a report using Application Express. Consider:
- No software to deploy on management desktops (need a browser though)
- You aren’t the messenger that delivers bad news about batch job failures
- A browser based report looks a lot more impressive than spooled CSV (especially when your boss is trying to impress his boss)
- Not wastefully repetetive or mind numbingly boring
So here is how you go about impressing management:
- Login to your desktop Oracle XE instance as a user with DBA privileges
- Create a new user – say rss
- Create a database link to your application database(say remdb) that has the batch log table(app_batch_log)
- Create a public synonym (app_batch_log_remdb) for this remote table (app_batch_log@remdb)
- Grant select on app_batch_log_remdb to user rss
- Logout and login to Oracle XE as user rss
- Choose Application Builder >> Create Application >> Create Application from the (rather picturesque) XE menu
- Enter your application name and click Next
- Select Report as Page Type, SQL Query as Page Source and enter your query (SELECT * FROM app_batch_log_remdb). Click Add Page.
- Click Next thrice
- Change Authentication Scheme to No Authentication and click Next twice
- Click Create
- Click Run Application
Here is a screen capture of the application creation process (Steps 7 thru 13).
To deploy:
- Enable HTTP access to your XE instance as described here
- Copy the URL from the last step and replace the 127.0.0.1 with your desktop IP address/hostname
- Send off to those interested.
