Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

February 11, 2008

Warning for the Oracle DBAs and Database Developers, taste and enjoy the rant of Application Express :)

Filed under: Oracle Express Edition(XE) and Application Express,Oracle How To — H.Tonguç Yılmaz @ 12:51 pm

In my recent post I discussed Oracle’s SQL and PL/SQL features’ powers. With this post I will mention some Apex specific supplied packages like; HTMLDB_APPLICATION, HTMLDB_ITEM and HTMLDB_COLLECTION.

HTMLDB_APPLICATION package is a PL/SQL package that implements the Application Express rendering engine. You can use the HTMLDB_ITEM package to create form elements dynamically based on a SQL query instead of creating individual items page by page. With HTMLDB_COLLECTION every collection contains a named list of data elements(or members) which can have up to 50 attributes(or columns). You insert, update, and delete collection information using the this API. And all HTMLDB_% supplied packages have APEX_% synonyms after the product’s name changed from Html Db to Application Express.

Before going into details of these packages with some examples please let me remind you that I am not even a beginner for web technologies, I have just simple HTML and Javascript knowledge lets say. Also no object orientations for me, all my career is based on data orientation, as a result I may say I have a little SQL and PL/SQL knowledge ;) And my hero Application Express is my enabler to create browser based Oracle database applications.

With this background I have, after completing the chapters of 2 days plus Apex developer’s guide and studied the sources of the sample application provided with Apex installation it was a child game, really fun for me to build Apex applications.

The chapter 10 of the 2 day plus Apex developer’s guide was like my cookbook, step by step I applied the rules mentioned to develop. Of course Application Express Oracle Magazine Articles, Apex OTN forum and Apex Evangelists’ blogs helped me a lot whenever I faced a trouble. And special thanks goes to my colleagues Hüsnü Şensoy, Ersin Ünkar and Hakan Ağdere for their creativity, advises and supports on some of the below examples.

First of all this application is a Terminal(simply a cellular phone) Management application; you define Terminal’s catalog properties, menus, test scenarios and results on this application. All these informations produced are supplied to the channels like call center and data warehouse over views and pl/sql package apis. So with this supplied data for example you can design campaigns to your customers based on the catalog information you have about their terminals since you have their terminal type based on their service usages etc.

Example A. Creating a Dynamic(runtime tailored based on user defined parameters) Update Form

In order to minimize the development need end users wanted to define new terminal parameters and based on their inputs update forms should be generated on runtime. From first day we knew this need could be handled within Apex because Apex’s own development environment is also written with Apex :) By the way when we say Apex remember we are talking about pure PL/SQL inside a schema of your Oracle database.

In order to tailor the screen we used HTMLDB_ITEM and to update the dynamic form generated back to the database HTMLDB_APPLICATION apis.

Example B. A Custom Excel Report

Within Apex it is only a click away to export your regions’ data to Excel. But one of the end user report need was to create an Excel sheet with lots of tabs having grouped all the information for a spesific terminal.

With the help of DBMS_LOB, OWA_UTIL, HTP and WPG_DOCLOAD supplied PL/SQL packages we created an XML based output file and let the users to download this file to their client to open and work with Excel.

Example C. Creating a Dynamic(runtime tailored based on user inputs) Report Form

Here the users wanted to have reports based on their dynamic filters like “report me the terminal having an internal antenna and 100 grams weighted and supporting browser types of HTML, WAP 1.2”

In order to record the user’s dynamic filters we used HTMLDB_COLLECTION api, with each filter iteration we added the filter to the collection and when user requested the report’s output we had the dynamic query ready to be passed as a source of a report region.

Example D. AJAX Tree Based Input Form

Apex has tree region type but when you have some thousands of leaves manipulated frequently, users didn’t like the performance of this component since it always submits the whole page when you want to move on the tree. So after a little research we learned about some technology called AJAX(which I still do not have any detail idea:) but an Oramag article Building an Ajax Memory Tree by Oracle ACE Scott Spendolini helped us to solve this need.

Some Sample Pseudo Code like references from the Application

Some Sample Screen shots from the Application

If you are an Oracle DBA or a Database Developer, for happy customers and management I will advise you to try Application Express as soon as possible because;
– Apex is FREE to use with any Oracle Editions and with Apex you do not need any other kind of orientation to develop browser based applications,
– Apex is strong since it is completely SQL and PL/SQL integrated, and will be stronger with its new releases,
– Some think Apex is just a desktop application alternative, like you may use Oracle database as a data pump you can use Apex for an Access or Excel alternative of course. But there is a very important potential if you want to take its advantages and I think no extra proof is needed for this potential when you consider my own background I mentioned above and what I am enabled to do with Apex!

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: