Monitor batch jobs with Oracle XE

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:

  1. Login to your desktop Oracle XE instance as a user with DBA privileges
  2. Create a new user – say rss
  3. Create a database link to your application database(say remdb) that has the batch log table(app_batch_log)
  4. Create a public synonym (app_batch_log_remdb) for this remote table (app_batch_log@remdb)
  5. Grant select on app_batch_log_remdb to user rss
  6. Logout and login to Oracle XE as user rss
  7. Choose Application Builder >> Create Application >> Create Application from the (rather picturesque) XE menu
  8. Enter your application name and click Next
  9. Select Report as Page Type, SQL Query as Page Source and enter your query (SELECT * FROM app_batch_log_remdb). Click Add Page.
  10. Click Next thrice
  11. Change Authentication Scheme to No Authentication and click Next twice
  12. Click Create
  13. Click Run Application

Here is a screen capture of the application creation process (Steps 7 thru 13).

To deploy:

  1. Enable HTTP access to your XE instance as described here
  2. Copy the URL from the last step and replace the 127.0.0.1 with your desktop IP address/hostname
  3. Send off to those interested.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: