Introduction to Oracle Application Express

Oracle Application Express (APEX) is another buzzword in Rapid Application Development (RAD) technologies. I will be doing series of articles on APEX to create Anil's OA Framework tutorials (Person Details) in APEX.

This article is Introduction to APEX.

What is Application Express?
It is formerly called HTML-DB, a web-based RAD tool which resides in Oracle database. Using browser based user interface and limited programming experience, you can build a complete data-centric web application in a very fast development cycle.

From the end user’s perspective, the deployed applications require only a browser and
access to an Oracle database running Application Express.

What are the uses of APEX?
1. Builds professional looking web applications that are both fast and secure.
2. Runs on and lives in Oracle database. APEX framework and meta data are stored in Oracle tables.
3. It is FREE. No licensing required.
4. Fast learning curve for developers to build application.
5. Deployment of application is as simple as 'Export and Import' if hard coded references of values that change between environments are avoided.
6. Lot of scope for customization of application look and feel.
7. Scalable for high user volume. AskTom, former Oracle Metalink (before migrated to Flash version) and many applications are built using APEX.
8. Can be configured to use Oracle SSO and EBS fnd user repository.

Who uses APEX?
If you are running on oracle database and you want to build rich web application with reports, forms, charts, drill downs and dashboards with limited Java experienced developers in a short time frame, Oracle APEX is the most likely candidate for consideration.

How APEX Architecture works?
APEX is installed on Oracle database (above 9.2 version), starting from Oracle 11g it comes pre-installed with the database. It is comprised of meta data in tables, pl/sql code and extensive JavaScript APIs.

The URL request from the browser is translated into appropriate APEX PL/SQL call by either Oracle HTTP Server (Apache) with mod_plsql plugin or Embedded PL/SQL Gateway. This varies by the type of APEX installation on oracle database. After the data is processed, results are relayed back to browser as HTML. This cycle happens each time user request or submit a page. The application session state is maintained in database tables.

APEX installation can be done in two ways. DBAs are more concerned about it than developers. But this determines how the URL is translated. I will talk about it in next article.

Oracle HTTP Server

In this three-tier configuration, mod_plsql in Oracle HTTP Server acts a broker between client web browser and server database.

For each URL that is processed, mod_plsql either uses a database session from its connection pool, or creates a new session on the fly and pools it. For mod_plsql to invoke the appropriate database PL/SQL procedure in a URL-processing session, you must first configure a virtual path and associate that path with a Database Access Descriptor (DAD).

A DAD is a named set of configuration values that specify the information necessary to create a session for a specific database and a specific database user/password. This includes the database service name and the Globalization Support setting (for example, language) for the session.

Embedded PL/SQL Gateway

This is a classic client-server architecture where embedded PL/SQL gateway provides the Oracle database with a Web server and also the necessary infrastructure to create dynamic applications. The embedded PL/SQL gateway runs in the XML DB HTTP server in the Oracle database and includes the core features of mod_plsql, but does not require the Oracle HTTP Server powered by Apache. Inclusion of the embedded PL/SQL gateway simplifies the
architecture and eliminates the middle tier entirely.

Oracle HTTP Server is known and proven technology, it has been used for Self Service applications.

Does APEX works with non-Oracle databases?
No.

What is current version available?
APEX 3.2 is released a couple of weeks back.

What are skills required for APEX Developers?
SQL, PL/SQL are essential. HTML, CSS and JavaScript are good to have skills to understand and customize look and feel (themes, templates, .... ).

Where to learn and get hands on APEX?
There are multitude of sites dedicated to APEX. apex.oracle.com allows anyone to request for free APEX account to build applications or run packaged applications.

Follow the below steps for creating account in apex.oracle.com

Go to apex.oracle.com and click 'sign up for account' link below Login button.
Fill administrator information. This user will have all rights to create new users and manage their accounts.
Enter workspace name (For Ex: XX-APEX). Workspace is dedicated area where you develop applications. A workspace is attached to one or more database schemas. In a typical development environment, you might create a single workspace for all
your developers to share.
Enter name of database schema and its space allocation.
Fill justification and complete by confirming the request.
You will get email once the request is approved. Oracle administrator do great job to approve your request as soon as they can. (Usually few hours ... latest a day).
After clicking on approval link, you get another email with userid/password.
Then you are set on login on apex.oracle.com and run sample application under Application Builder.

I will cover different aspects of APEX in next article.

Finally APEX or OAF or ADF?
Some healthy debates are going on choice of these existing new technologies. APEX embeds business logic and presentation together in oracle database. It has bunch of bugs to be fixed ... for that matter every technology evolves over time. APEX is fast growing platform for RAD. The number of OTN threads and APEX developers in APEX OTN discussion forum gives a fair idea of amount of work going in APEX. Though a quarter of them use APEX with EBS.

If you want to follow Model-View-Controller (MVC) design pattern and have Java / J2EE resources at hand, OAF or ADF is a better choice. With OAF, you can use EBS security model, DFFs etc.

APEX doesn't replace OAF, it's highly productive, wizard driven development and deployment and easy to use navigation cannot go unnoticed.

Set as favorite

Bookmark

Email This

Comments (29)

Subscribe to this comment's feed

Oracle Apps
written by suresh_1784 , March 26, 2009

Can APEX be used for oracle app 11is?

Thanks,
Suresh

report abuse

vote down

vote up

Votes: +2

Re: Oracle Apps
written by kishore Ryali , March 26, 2009

Suresh,
Yes. APEX can be used to report on Apps tables or views, create forms to insert/update data using APIs, integrate with BI Publisher or web services.

If your organization uses Oracle SSO, APEX can use it as well. Otherwise, you need to create custom authentication scheme to validate APEX user against FND users. The custom function calls fnd_web_sec.validate_login function to validate apex user.

Hope that answers your question. APEX is highly productive tool to create web applications for EBS.

Kishore

report abuse

vote down

vote up

Votes: +2

...
written by suresh_1784 , March 26, 2009

Hi Kishore,

this really sounds great ..... we are using SSO smilies/grin.gif

Currently I am developing pages using OAF for End user of Oracle application. So using APEX can we do the same? or how is it differ from OAF

Hope you tutorial cover more considering on Oracle Apps ....And I will nice if you start as Anil with Installation of tool also...

Cheers .... thanks for your effort !!!!!!!!!

Suresh

report abuse

vote down

vote up

Votes: +1

...
written by suresh_1784 , March 26, 2009

How can we go for an account legally for our Org?

You mentioned its free. So how will get one account for our Organization?

Thanks,
Suresh

report abuse

vote down

vote up

Votes: +3

Re: Oracle Apps
written by Kishore Ryali , March 26, 2009

Suresh,
APEX application or APEX engine must be registered as partner application with Oracle AS SSO Server, to authenicate user with
SSO. If you choose this approach, your application will not use an integrated login page. Instead, when a user accesses your application in a new browser session, the Application Express engine redirects to the Single Sign-On login page. After the user is
authentication by SSO, the SSO components redirect back to your application, passing the user identity and other information to the Application Express engine. The user can then continue to use the application until they log off, terminate their browser session, or until some other session-terminating event occurs.

I've not done installation of APEX in our environment. http://download.oracle.com/doc...66/toc.htm URL will help you in the process. We use APEX for financial reporting on Oracle Apps reporting (BI) database. Coming to your question on APEX or OAF, the decision depends on various factors.

1. What is the scope of your custom pages? Are you building a custom application/module or a few pages? In APEX, you cannot utilise DFF/KFF, security rules of Apps.

2. I suggest you to invest some time in architecturing APEX for your environment ... you want to use HTTP Server of current EBS environment or a different HTTP Server? If you want to use APEX for reporting, you wouldn't need to run forms server/concurrent manager to HTTP Server (if it is different to EBS HTTP Server).
APEX is installed in different database schema, so managing synonyms and grant access on apps tables or views has to be considered.

3. How many users access your application? Tuning your SQL queries which you use in your application is crucial for performance. Better to use views. Create views in apps schema and synonyms to them in apex schema.

4. If you have java environment already setup for OAF, stick to OAF. Because you cannot extend OAF pages using APEX. Their architecture is completely different. If you are building a custom module or looking for reporting alternative, APEX is a choice for agile development.

Once you have architecture charted down and have environment setup, APEX is very useful tool and it doesn't take long to appreciate its power.

Kishore

report abuse

vote down

vote up

Votes: +0

Re: Free apex account for organization
written by Kishore Ryali , March 26, 2009

Suresh,

apex.oracle.com is only for learning the tool and doing prototypes. You can get free account for yourself by clicking on 'sign up for an account' link. Please follow instructions in the article. You can download apex 3.2 from otn, install it in your organization, develop application and use it without any licensing. But for BI Publisher integration with APEX report printing, you would require license.

Refer Metalink note 761836.1 for installation steps. For more details on apex license, log a SR with oracle support.

Kishore

report abuse

vote down

vote up

Votes: +0

Can APEX reports be used to effectively replace bad performing Discoverer report
written by JustinM , March 26, 2009

Hi Kishore,
I am currently trying to integrate APEX with 11i and trying to mimic the features of discoverer (like drilldown reports, reports with multiple workbooks etc.,) in APEX.
Any help will be much appreciated.

Thanks and Regards
Justin

report abuse

vote down

vote up

Votes: +0

...
written by Kishore Ryali , March 26, 2009

Justin,

In APEX, for creating drill downs you define column link on report column to link it to another page. You can imitate multiple workbooks in discoverer by creating multiple pages or multiple regions in a page and conditionally displaying them based on various items.

Please post a specific question or any issue you stuck with.

Kishore

report abuse

vote down

vote up

Votes: +0

Consultant
written by Shanker , March 27, 2009

All

Here are some good examples for APEX. The following link has all the tutorials drilldown reports (using Link), Master Detail Reports

You can download the sample data from the same link and start working with examples.

http://download.oracle.com/docs/cd/E10513_01/doc/appdev.310/e10497/toc.htm

OBE'S

http://www.oracle.com/technology/products/database/application_express/html/obes.html

report abuse

vote down

vote up

Votes: +0

Consultant
written by Shanker , March 27, 2009

report abuse

vote down

vote up

Votes: +0

Installing Apex
written by Ashok Gunasekaran , April 28, 2009

Hi,

I have installed Oracle APEX with background of Oracle XE database and Oracle Application Server.

But after installation, when i access http://localhost:8080 I get the login page but when i provide the username, password and workspace and click submit,

It is not going to next page. Kindly guide me with any installation document or sceen shots

report abuse

vote down

vote up

Votes: +0

Re: Installing Apex
written by Kishore Ryali , April 28, 2009

Ashok,

What is the URL you are using for login? Go to Start > All Programs > Oracle Database 10g Express Edition > Go To Database Home Page, for login page URL. Do you see invalid login message after you enter username/password?

For step-by-step installation of APEX on XE, see the below link.
http://www.scribd.com/doc/5456221/Stepbystep-guide-to-install-Oracle-APEX-on-XE-on-Windows

Kishore

report abuse

vote down

vote up

Votes: +0

Re: Oracle Apps
written by Raghu Raman , May 07, 2009

Kishore,

Thank you for your efforts.

In Oracle HRMS, we have a concept called Security Profiles that provides/restricts user access to the applications. Can we use it in APEX?

Regards
Raghu

report abuse

vote down

vote up

Votes: +0

Re: Oracle Apps HRMS Security Profiles
written by Kishore Ryali , May 07, 2009

Raghu,

You cannot utilize Oracle HRMS Security Profiles to restrict information in APEX, as APEX security architecture is different from EBS. Though you can write a function using Security Profile API or Join Security profile tables, while writing SQL query for displaying HR information in APEX.

Kishore

report abuse

vote down

vote up

Votes: +1

Re: Oracle Apps HRMS Secuirty Profiles
written by Raghu Raman , May 08, 2009

Kishore,

Thank you for the information. I will be shortly creating an Portal Application with FND_USER authentication and HRMS Security Profiles. I'm trying to figure out which tool would be a best one to use, either APEX or OAF or Web Center/ADF. I know there are some java work involved in OAF and ADF. I'm not much into Java, but I could if i needed.

I see you are doing a good job in APEX and digging more and more day by day. Did you came across something that you felt that APEX is not supporting it yet like you had mentioned KFF, DFF or Security Profiles.

Any information would be highly appreciated.

Regards
Raghu

report abuse

vote down

vote up

Votes: +0

Re: Oracle Apps
written by Raghu Raman , May 08, 2009

Hi Kishore,

I have another question on APEX. Could we be able to deploy the APEX application/page in Oracle Apps server like we do for OAF? Like we attach it to a Form Function which in turn attached to the Responsibility?

Regards
Raghu

report abuse

vote down

vote up

Votes: +0

Re: Oracle Apps HRMS Secuirty Profiles
written by Kishore Ryali , May 08, 2009

Hi Raghu,

APEX runs in Oracle database, there are small gaps in integration of APEX with Oracle EBS. Honestly I have not come across any articles on APEX using EBS security model, KFF/DFF. APEX is not replacement to OAF. Future releases of Oracle EBS will be built on OAF/ADF. Having said that high usability and productivity of APEX cannot be ignored for the scenarios where seamless integration with Oracle EBS is not required.

Coming to your custom portal requirement, does DFF/KFF requirement necessary for a custom application? They can be created as text fields and that information can be inserted/updated for DFF Attribute columns in Oracle EBS tables using APIs. APEX can use FND_USER for authentication when custom function is created. It is covered in article http://apps2fusion.com/at/kr/3...s-section. For HRMS security profiles, you can create your database view using HRMS security profile tables to restrict users accessing data they are not supposed to view. Before considering APEX, comptemplate on

1. Whether your environment is already setup for OAF development? How much effort you can afford to architect or setting up environment for new technology like APEX?
2. How fast your application has to be delivered? APEX is faster than anything I've ever used for Oracle EBS.
3. How much traffic you are expecting for your application?
4. APEX heavily uses JavaScript for different events, Page Page Rendering etc., Does your client workstations enable JavaScript? What browsers they use?
5. Do you have resources with Java, J2EE and XML skillsets if you go with OAF? APEX developers can be more productive in lesser time with just SQL knowledge. Though JavaScript and CSS skillset would become necessary in building rich UI.

I would give APEX a short. You would definitely appreciate it in no time.

For thoughts on OAF vs ADF, Steven Chan's article http://blogs.oracle.com/steven...r_adf.html summaries sticking to OAF as long as you build applications for Oracle EBS.

Thanks
Kishore

report abuse

vote down

vote up

Votes: +0

Re: Calling APEX application from Oracle EBS menu functions
written by Kishore Ryali , May 08, 2009

Raghu,

Everything about APEX resides in database. You wouldn't have to deploy it on Oracle AS. But when you install APEX, you need to architect it to use HTTP Server or Embedded PL/SQL Gateway (resides in database). For calling apex application/page for Oracle EBS menu function, check this article http://www.oracle.com/technolo...ns_11i.pdf

Kishore

report abuse

vote down

vote up

Votes: +1

Re:
written by Raghu Raman , May 09, 2009

Kishore,

Thanks for the detailed information. So it means that the APEX will reside on the Oracle EBS database or will it reside on a separate database and access the Oracle EBS database through PL/SQL Functions?

Basically I do not wanted to get stuck on the middle of the development. So I'm trying to gather as much information as I could.

Regards
Raghu

Regards
Raghu

report abuse

vote down

vote up

Votes: +0

Re: APEX with Oracle EBS
written by Kishore Ryali , May 09, 2009

Raghu,

Where does APEX reside is part of Architecture consideration? You can choose either to

* Install APEX on same database as Oracle EBS and utilize Oracle Application Server as HTTP server for APEX.

* Install APEX on separate database than Oracle EBS. You may use same Oracle AS of EBS or separate AS for APEX HTTP Server. In this case, you would require to create database links in APEX database to access Oracle EBS database objects.

My article on database links will walk through the steps. http://apps2fusion.com/at/kr/3...ws-in-apex

Which approach to follow is merely a choice than a requirement. Discuss with your DBAs on your pros and cons of each approach.

Kishore

report abuse

vote down

vote up

Votes: +0

Custom Login
written by New , May 13, 2009

Hi Kishore,

We are planning to create an application in APEX. I need to know how do we go about creating a custom login.I have my Users and Passwords in a database table.I want to authenticate the users through this table instead of using the standard apex creadentials.
Any help regarding this will be appreceiated.

~Thanks in advance
Priyanka

report abuse

vote down

vote up

Votes: +0

Re: Custom Login
written by Kishore Ryali , May 13, 2009

Priyanka,

Please refer to article http://apps2fusion.com/at/kr/3...ts-section

If you are planning to default login page (101) and using authentication based on custom table, you can create custom authentication function as done in the article. In the function, validate username and password against your custom table.

If you want to create custom login page, then it should have username, password fields along with login button. Check default login page (101) to mimic its processes under login button.

Thanks
Kishore

report abuse

vote down

vote up

Votes: +0

Oracle APEX integration with Oracle EBS R12
written by Pankaj2009 , June 04, 2009

Hi Kishore,

Can you please share your views or document, regarding to accessing Oracle APEX application (i.e. Developed form/report) from any Oracle EBS responsibility.
Also migration process for developed objects(i.e. Form/report) from one instance to another instance.

I look forward to hearing from you.

Many thanks,
Pankaj

report abuse

vote down

vote up

Votes: +1

Re: Oracle APEX integration with Oracle EBS R12
written by Kishore Ryali , June 05, 2009

Pankaj,

Please check the section Linking directly to Apex Forms and Reports from Applications 11i Menus in below document.
http://www.oracle.com/technology/products/database/application_express/pdf/Extend_Oracle_Applications_11i.pdf

Kishore

report abuse

vote down

vote up

Votes: +0

Problem migration of oracle forms & report using 10g xe & apex 3.2
written by Manoji , June 26, 2009

Dea Sir

While I am trying to Application Migration Project Details in
Type Select Column
only Access Type is is coming
no Form Type is Coming.
So How I could do it so that i will upload my oracle forms compiled xml file in project

WBR

Manoj

report abuse

vote down

vote up

Votes: +0

Desig Pattern
written by tozi , June 29, 2009

Hi. Can anybody tell me, if OAE application is based on Autonomous View design pattern or not? And if not, which is his design pattern?

report abuse

vote down

vote up

Votes: +0

Correction
written by tozi , June 29, 2009

Sorry. I had in mind architectural pattern. Thank you in advance for your reply.

Tozi

report abuse

vote down

vote up

Votes: +0

Not able to view database home page after apex installation
written by Upendra Mishra , August 26, 2009

HI Kishore,
I vae installed the Apex on XE database. Every thing is working fine. But prior to installation , when to goto database home page I was able to login using SYS and see the menu for managing datbase and its object. But after the apex installation I am only ble tro see the apex login page where I can only login with apex admin not from sys. Is there any way or url to see the database homepage as well as apex home page.
Thanks in advance
Upendra

report abuse

vote down

vote up

Votes: +1

system engineer
written by mike , March 07, 2010

Kishore:

Is apex better or easier than pl/sql web toolkit. Does not wizards sometime force limitation instead of writing code.

can i migrate a ppower builder app to APEX and integrate it with pl/sql web toolkit application.

APEX sounds so easy that customers may not need a developer after that. Is not it bad for developer job.

thanks,

report abuse

vote down

vote up

Votes: -1

Write comment

Name

Title

Comment