Review Oracle Apex Best Practices- Packt Publishing

Oracle-Apex-Best Practices has been released by Pact-Pub recently. First impression on me was that it’s not a beginner guide to APEX. It’s an enterprise level Apex development features book. When developers begin to learn APEX, they think “we can do simple web projects with APEX but not more”. But this book shows that everything can be done with APEX. If you don’t have experience about APEX, I recommend reading a beginner APEX books before you read this book.

– In the first chapters it tells the APEX architecture and gives the key points how to design and manage single or multiple applications. I wish there could be much more examples on this chapter.

– The book covers also advanced level oracle reporting features such as analytic and grouping functions and tells how to use them in APEX.

– There is a large chapter about printing of reports on different platforms and many examples about how to implement that.

– All enterprise applications must have good implemented security levels. Application level security steps from database to user interface items are well-explained with examples.

– Many APEX developers complain about debugging on APEX. There is a chapter about how to debug and alternative debugging tools from database level to user interface level. Also that is so helpful to understand what happens when user clicks a button from end to end.

– If it’s an enterprise application, we should have source version system and deployment procedure. Last chapter examines how to manage this with examples.

Everything is understandable and is easy to implement for our production environment. Screen shots are taken from 4.1 version of APEX but all can be used in 4.2 too. Also there is a mini appendix for RESTfull web services which is the new great feature in 4.2 version of APEX. I think ever APEX developers should read this book for better designed APEX application. Many thanks to writers of this book: Learco Brizzi, Illon Ellen-Wolf and Alex Nuijten for this great job.

ps. this review is written by my collegue A.Yavuz Barutçu


Application Express 3.2 :)

Today I saw this warning when I try to login to my workspace at; This site will be unavailable on Wednesday, 11-FEB-2009 from 0900 – 1300 EST (1400 – 1800 GMT). During this time, this site will be upgraded to Application Express 3.2.

Happy to hear about 3.2 and wanted to share :)

Apex Knowledge Test Written in Apex :)

If you want to have some fun try this application.(login needed as demo/demo)

There are 12 questions, I missed one, maybe soon there can be an Apex certification?
Questions Answered: 12 Correct: 11 Pass
Minimum Required Percent: 60% Percent Correct: 91.7%
Unanswered: 0 Incorrect: 1
Allocated Time in seconds 600 Elapsed Time in seconds 185
Test: APEX General Knowledge Grade: A-

Apex is the most relaxing development tool I used and still use, without any competition, daily I search for new needs&wants just to have the opportunity to develop database centric web based applications with apex in my organization, this is like a virus, each time as I get more experienced with Apex the customers are happier with the results and new requests flow :)

Apex is free, Apex is easy to learn, Apex comes as a part of the 10gR2 XE and 11gR1 SE&EE, if you are a database developer or administrator and looking for an extreme love try Apex!

ps: if you need more fun check this one also :)

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

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!

Oracle Express Edition(XE) and Application Express(ApEx) Part 2

For quick wins and a brief introduction please first read part 1 of this series. Express Edition and Application Express, I think because of this “Express” common word, usually perceived to be the same by the beginners. So it may be important to point out that XE is a limited but free Oracle database edition of Oracle 10g Release 2, where as ApEx is a rapid web based wizard driven development environment bundled with 10g XE but also can be installed and used for any editions of Oracle. Since this part is an overview, I want to share some stories of mine, please hold on :)

Application Express enabled me to challenge J2EE developers in my company once upon a time. After reading its user’s guide and 2 day plus apex developer’s guide and playing with the sample application on 1.6 release, immediately I fell in love with this development environment. The primary reason behind this was, at last one of my dreams was becoming real, I was also able to build web based database applications only with my SQL and PL/SQL knowledge. Okey, some HTML and Javascript basics also helped me a lot, I do confess. In order to introduce this tool to the developer community in my company, I challenged with both time and head count constraints; I proposed that I will do the project at half time whatever time others will be giving and I will be the only developer for every layer(database-middle layer-front ends we may say). When you fall in love, you may harm yourself :)

Since I am one of the old wolfs in my company, project leader believed in me and the project was an important success. I will be going through the best practices I accepted and used, also some tricks behind the project’s success during the series. But first of all let me mention that as a starter I targeted a similar project which was mentioned at chapter 10 of 2 days plus apex developer’s guide. Before starting this series I really want to warn all DBAs, Forms&Reports or PL/SQL developers to check part 1 for the introduction and warm up with ApEx. ApEx is now installed as part of the 11g installation and Oracle will continue to invest in this beauty with its 4.0 new version.

Express edition also enabled me to challenge MySQL in a project once upon time. Express edition has limitations yes, but be open minded and please see the full side of the glass, this free edition is completely Oracle. Meaning lots of unique features are included and the kernel is the same;
a. with its Undo mechanism which enable you to create high-concurrent applications(readers never wait for writers and writers only wait for writers for the same row and this is done automatically, also phantom reads are not permitted and of course multi-versisioning)
b. with its Redo mechanism which enable you to have high-available applications(archivelog mode, instance and media recovery, RMAN etc.)
c. with its SQL and PL/SQL supplied features enables you to be time to market(CBO, SQL Model and Analytic Functions, Oracle Text and Locator, Connect By Prior, UTL_% and DBMS_% packages, who has resources to re-invent a dumper wheel as Mr.Kyte mentions).
After reading the Licensing guide available under XE documentation you will also reconsider on using XE for some of your projects.

11g Express Edition is expected to be available on OTN with the second release and until that time requesting some add-ons to 11g XE is also free :) You may post your requests under the XE forum. Since XE and Apex are free, support may be a question for you, if so just monitor their forums a little, you will see how valuable Oracle experts will be interested and answering your questions.

This will be the draft content of this series –
Part I – Quick Wins and Introduction to XE and ApEx
Part II – Overview of XE and ApEx
Part III – Installing, Upgrading and Configuring XE and ApEx on OEL4
Part IV – Sample IMEI and Terminal Management Application Implementation

Also last words are for an important change in my life; just one day after I read this post from one of my important idols, I learned that I became an Oracle ACE. Bad timing let’s say, I completely understand Mr.Rogers points. So at least I may say I will be doing what I was doing for some time, which I believe was important to get this award. Turkish Oracle Users community had a 10g OCM, Aykut Çelik, and this is another first for the community. But since I know the potential and proficiency of this community, I may guarantee soon there will be others for both OCM and ACE levels.

Oracle Express Edition(XE) and Application Express Part 1

Oracle Database 10g Express Edition(Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that’s free to develop, deploy, and distribute; fast to download; and simple to administer. In 2005 Oracle XE was announced by Oracle as a free and limited Oracle 10gR2 database. Oracle Database XE can be installed on any size host machine with any number of CPUs(one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine. Also some features like Partitioning, Bitmap Indexes and Materialized View Query Rewrite are not included, but before making a decision on this release check the list of features included from Licience Documentation before making a decision for XE.

Also be sure to register for an exclusive Oracle Database 10g Express Edition Discussion Forum hosted by Oracle expert Tom Kyte. When I asked Mr.Tom Kyte about XE’s future he said; “there will be future releases, but only with the next major release of the database. Security patches may cause us to re-release XE, but it’ll be the patched version as it exists now. APEX is a pretty flexible environment, I know the guys that work on it, they are very careful not to have too many complex dependencies on the database version.” So since Oracle 11g is announced we will be enjoying a new XE version soon.

Oracle Application Express(Oracle APEX, previously named Oracle HTML DB) is a free software development environment based on the Oracle database. It was also previously known as “Project Marvel”, in 1997 with Oracle version 8i WebDB introduced and eventually browser-based development and deployment tools of Oracle matured into Portal and ApEx today. It allows a very fast development cycle to be achieved to create web based applications. This viewlet is a demostration on turning an excel sheet into a web application in seconds.

Oracle Application Express can be installed on an Oracle 9.2 or higher database. In January 2006 Oracle renamed HTML DB to Oracle Application Express. One of the most well known two applications developed in HTML DB is the askTom with Thomas Kyte and Best Practice PL/SQL with Steven Feuerstein

Apex comes standard with Oracle XE database, no need for another installation. The url used to access the XE database homepage typically takes one of the following forms:

Here are the instructions how you may check the information from SQL*Plus;

SQL> conn hr/hr@xe

SQL> select dbms_xdb.gethttpport, dbms_xdb.getftpport from dual;

———– ———-
8080 0

2    l_dad_names DBMS_EPG.varchar2_table;
4    DBMS_OUTPUT.put_line(’DADs’);
5    DBMS_OUTPUT.put_line(’==========’);
7    DBMS_EPG.GET_DAD_LIST (l_dad_names);
9    FOR i IN 1 .. l_dad_names.count LOOP
10      DBMS_OUTPUT.put_line(l_dad_names(i));
11   END LOOP;
12 END;
13 /


PL/SQL procedure successfully completed.

Also if you want to open your local ApEx applications on XE instance to remote clients, you should follow instructions given at Oracle® Database Express Edition Installation Guide. With Apex comes some great Demostration Applications which help understanding. And also there is an online development center, free to request a workspace and start building internet applications. With Apex you may shortly start with examples and learn, try this Survey Application for example.

The Application Express 2.2.1 latest patch set, to be applied to an Application Express 2.2 instance, can be downloaded from MetaLink(Patch Number 5500033) and is about 2.6MB. Due out in the first half of 2007, the next major release of Oracle Application Express will be version 3.0.

So if you are after a browser based and wizard driven rapid application development(RAD) tool on the Oracle database, Oracle XE and ApEx, with no additional charge, is waiting you for a start. Enjoy the qualities of a personal database like;

  • Productivity
  • Ease of Use
  • Flexibility

with the qualities of an enterprise database like;

  • Security
  • Integrity
  • Scalability
  • Availability
  • Portability

Continue reading with Part 2

For more informations please check these links

Advanced Programming Examples with ApEx

The list of Apex Blogs I suggest you to follow