Friday, 20 September 2013

The Role of Global Page in Mobile Web Applications

The global page is a new feature incorporated in Oracle Application Express and functions as a master page in your application. You can define a separate Global Page, Login Page, and Home Page for each user interface. This facilitates different pages being shown to end users when they access the application from a mobile device as opposed to a desktop system. The Application Express engine renders all components you add to a Global page on every page within your application. You can further control whether the Application Express engine renders a component or runs a computation, validation, or process by defining conditions. In this book you'll use two separate global pages for desktop and mobile interfaces. In desktop application, it is used in the reports module to display a list of report on every report page. In the mobile interface, however, it is used to display footer controls on every mobile page. You can also add CSS code to style your application pages, as you'll we will do by adding cascading rules to the global page to style the mobile shopping cart. 

Create Global Page for Desktop Web Application

Prior to creating reports, you will create the reports menu. This menu will be displayed to the left on all report pages. In order to achieve this task, you need to create a Global Page.  Global Page components are displayed on all pages if their region's display point is contained within the selected page template. Conditional Display attributes can be used to suppress display. This is a very simple and effective way of sharing APEX objects throughout the application. In this exercise you will create a Global Page for your application to display the Reports List. Since Global Page components are shared across the application, you are going to put a condition to display the reports list only on pages related to the Reports tab. 

Creating Reports List for Desktop Application

This list contains six links (shown in the above figure) that lead to different graphical reports in our desktop application and appears on all pages associated with the Reports tab. 

1. Go to Shared Components | Navigation SectionLists | and click the Create button.

2. Select From Scratch. Enter Reports List for Name, select Static as the list Type, and click Next.

3. Enter the following values in Query or Static Values page. Here, you're defining a label and the target page id, the user will be taken to. For example, when a user clicks Customer Orders, he/she will land on Page 17.

List Entry Label                        Target Page ID or Custom URL
Customer Orders                        17
Sales by Category and Product     16
Sales by Category / Month           5
Order Calendar                          10
Customer Map                           15
Product Order Tree                     19

4. Click Next, accept the default values in the next screen and click the Create List button. The wizard allowed you to create five entries. The sixth one - Product Order Tree - will be created like this:

5. In the main Lists interface, click the newly created Reports List icon.

6. Click the button: Create List Entry.

7. Enter Product Order Tree in List Entry Label.

8. Enter 19 in the Page attribute under the Target section and click the Create List Entry button. That’s it. Your Reports List should look like the following figure where each entry appears as a link. Now that you've created the list, it's time to create a global page to place the list in.

Desktop Application Global Page

Follow the steps mentioned below to create a global page for desktop application.

1. Click the Create Page button.

2. Click on Global Page icon.

3. Enter 100 in Page Number and click Finish. Global Page definitions should appear.

4. Click Create in Regions section to create a new region.

5. Select the List option.

6. Enter Reports in Title, set Template to Standard Region, Display Point to Page Template Body (3) and click Next.

7. Set List to Reports List and List Template to Vertical Sidebar List

8. Set Condition Type to Current Page Is Contained Within Expression 1 (comma delimited list of pages) and enter 5,10,15,16,17,19 in Expression 1 and click the Create Region button. The Expression 1 attributes specifies the six report pages. When you call these reports (under the Reports tab), the condition evaluates to true, and displays the Reports List on the page.

9. Modify the region and set Grid Layout attributes as follows. These attributes will place the Reports List on a new row starting at column number 1. The value "Automatic" in the Column Span attribute automatically controls the list display width.
Start New Row: Yes
Column: 1
Column Span: Automatic

Once you create the pages specified in the Reports List (taught in Chapter 8 - Graphic Reports), you'll see the list appearing to the left on every report page as illustrated below:


Create Footer Control List for Mobile Application

This static list carries two options (Full Site and Logout) and will be displayed at the bottom in the Footer Controls region. The first option will take you to the Home page of the desktop version, while the second one will log you out of the mobile application and will show the mobile login page. Here are the steps to create the static list to be incorporated in the mobile application.

1. Go to Shared Components.

2. In the Navigation section, click the List link.

3. Click the Create button to create a new list.

4. Select the option From Scratch.

5. Enter Footer Controls in Name and select Static as Type.

6. Fill in the Static Values as shown below:

7. Click Create List.

Utilize Shared Component List on a Global Page

The list, created above, is displayed on every mobile application page by creating a region on on a global page like this:

1. Call the mobile Global Page (Page 1001).

2. Click the Create button in the Regions section.

3. Select the List option.

4. In Display Attributes page, enter Footer Controls in Title, select Footer Toolbar (Fixed) for Region Template, Page Template Body (3) for Display Point, and enter 30 in Sequence. Click Next.

5. In the Source page, select Footer Controls as List and Button Control Group as List Template.

6. For Condition Type, select Current Page Is Not in Expression 1 and enter 1001 in Expression 1 to suppress the appearance of footer controls on the login page.

7. Click Create Region.

8. Edit this region. Scroll down to the Attributes section and enter style="text-align:center" for Region Attributes box. This inline CSS style rule will bring the list (being shown as button control group) in the center of the page.

9. Apply Changes.

The list would appear at the bottom of the mobile application as button control group just like this:

Using Global Page to Style Web Application Pages 

You can also use APEX global page to add styles to all or specific application pages by putting CSS code. For this purpose, you need to create a separate region on the global page. In the following exercise, you'll add CSS rules to style the shopping cart page.

1. Create another region in the mobile global page.

2. Select HTML as the type of region.

3. Select HTML as region container.

4. In Display Attributes page, enter Mobile Styles in Title, select No Template for Region Template, Page Template Body (3)  for Display Point, and enter 40 in Sequence. Click Next.

5. In Region Source, enter the style sheet defined below and create the region. Note that these rules were created and described earlier in the desktop version to style the Select Items page (Page 12), and were defined in the inline page attribute. Here, you added it to the global page to test another approach to style Select Items page (Page 217) of the mobile version. Since the classes defined in these rules are referenced only by Page 217, other pages in the application will not be affected.

div.CustomerInfo strong{font:bold 12px/16px Arial,sans-serif;display:block;width:120px;}
div.CustomerInfo p{display:block;margin:0; font: normal 12px/16px Arial, sans-serif;}
div.Products{clear:both;margin:16px 0 0 0;padding:0 8px 0 0;}
div.Products table{border:1px solid #CCC;border-bottom:none;}
div.Products table th{background-color:#DDD;color:#000;font:bold 12px/16px Arial,sans-serif;padding:4px 10px;text-align:right;border-bottom:1px solid #CCC;}
div.Products table td{border-bottom:1px solid #CCC;font:normal 12px/16px Arial,sans-serif;padding:4px 10px;text-align:right;}
div.Products table td a{color:#000;}
div.Products .left{text-align:left;}
div.CartItem{padding:8px 8px 0 8px;font:normal 11px/14px Arial,sans-serif;}
div.CartItem a{color:#000;}
div.CartItem span{display:block;text-align:right;padding:8px 0 0 0;}
div.CartItem span.subtotal{font-weight:bold;}
div.CartTotal{border-top:1px solid #FFF;margin-top:8px;padding:8px;border-top:1px dotted #AAA;}
div.CartTotal span{display:block;text-align:right;font:normal 11px/14px Arial,sans-serif;padding:0 0 4px 0;}
div.CartTotal p{padding:0;margin:0;font:normal 11px/14px Arial,sans-serif;position:relative;}
div.CartTotal p.CartTotal{font:bold 12px/14px Arial,sans-serif;padding:8px 0 0 0;}
div.CartTotal p.CartTotal span{font:bold 12px/14px Arial,sans-serif;padding:8px 0 0 0;}
div.CartTotal p span{padding:0;position:absolute;right:0;top:0;}

The order entry form page of the mobile web application would be presented as illustrated in the following figure:

Wednesday, 18 September 2013

Creating Mobile Web Application Interface in Oracle Application Express

Oracle APEX allows you to create two types of interfaces: Desktop and jQuery Mobile Smartphone. Each page in an application is associated with one user interface. If a user logs into the application with a mobile device, the pages created with jQuery Mobile Smartphone interface will be rendered; if a desktop is used, the desktop user interface is delivered. You created and used the desktop interface in previous chapters. Here, you’ll use jQuery Mobile interface for your mobile application.  

1. Click the button Edit Application Properties  in the main Sales Web Application interface.

2. Click on the User Interface tab.

3. Click the button Add New User Interface, located at the bottom of the page.

4. On the User Interface Page, set the attributes as follows and click Next.

Type: jQuery Mobile Smartphone
Selected a user interface type for the application. Select Desktop for applications primarily designed for desktop use. For mobile devices, such as smartphones or tablets, select the jQuery Mobile Smartphone interface.

Display Name: jQuery Mobile Smartphone
Specified a display name for the user interface. The display names is shown in wizards.

Auto Detect: Yes
Select whether the appropriate user interface should be automatically detected. If auto-detection is enabled, the user will be redirected to the corresponding login page or home page.

Specifies the home page of the application for the current user interface.

Points towards the login page of the application for the current user interface.

5. In Identify Theme, select Standard Themes as Theme Type and jQuery Mobile Smartphone (Theme 50) as the mobile application theme. Click Next.

6. Click Create.

Recall that when you initially created the desktop application, the application wizard created two pages for you (Home and Login). The mobile interface too, creates two default pages: Home (Page 13 in my scenario) and Login (Page 1001). In addition to these pages, the wizard creates a third one: Global Page - jQuery Mobile Smartphone (Page 0). Recall that this page was created manually for the desktop application in chapter 8 section 8.2. The Global page of your application functions as a master page. You can add a separate Global page for each user interface. The Application Express engine renders all components you add to a Global page on every page within your application.

Run the Login Page 1001 that will look like the following figure:

Enter the usual credentials (admin/asa), you’ve been using so far, and hit the Login button. A blank mobile home page will appear resembling the one illustrated below.

Click the Logout link. You’ll be landed to the desktop login page, which is not correct. Call definitions of the Global Page. Click the Logout link in the Buttons section. Scroll down to Action When Button Clicked section. Set Action to Redirect to Page in this Application and enter 1001 in Page. Click Apply Changes. These two settings inform APEX where to land user when he clicks the Logout button. Call and run the Home page again, login using the same admin account, and click the Logout button. This time you’ll see the correct login page i.e. Page 1001. Use your browser’s back button to move back to the development environment.

An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"

Build Web Applications for Smartphone and Mobile Devices

In the last few years there has been an eruption in the market for smartphones and mobile devices demand. As the sales are touching new heights every day, smartphone vendors are trying their level best to provide the excellent product with latest technology. Things that used to be done traditionally by people on their laptops are now being done increasingly on mobile devices. Feeling the heat, application development companies are under pressure to quickly deliver web applications for smartphone and mobile platforms. For this, they need tools and frameworks to roll out new mobile enabled applications and adapt existing applications to mobile devices. The good news is that just like traditional web applications, mobile web applications are also developed using the same core technologies - HTML, CSS, and JavaScript. 

With mobile web usage incrementing every year, there is a huge demand in the market for applications supported on smartphones and tablets. To help develop new applications and extend existing web applications for mobile use, the APEX development team has enhanced the product with mobile development features. Probably the most significant new feature incorporated in APEX 4.2 is the ability to build applications specifically aimed at mobile devices. Now you can easily build applications for modern smartphones and tablets, such as iPhone, iPad, Android, and BlackBerry using Oracle APEX. The jQuery Mobile framework is integrated to render an application for the vast majority of mobile devices. Besides, a new mobile-specific theme is incorporated to support touch input and gestures such as swipe, tap, and orientation change. Another theme takes care of responsive design, to automatically adjust the interface according to different screen dimensions, which aids in using the same interface on desktop, tablet, and smartphone devices.

Types of Mobile Applications

Mobile Applications are split into two broad categories:

Web-Based: The application you’ll be creating in this chapter is known as a web-based application or simply a mobile web application. These types of applications are accessed using browsers in mobile devices. In this chapter, you’ll declaratively build a mobile web application. APEX 4.2 allows you to rapidly build application that can be accessed on the desktop, a mobile device, or both. The mobile development interface uses a collection of templates based on the jQuery Mobile framework. This framework is designed to seamlessly run and correctly deliver mobile web application on varied mobile devices with different operating systems. For you, as a developer, the good news is that you develop such applications with the tools you’re already familiar with. To build a mobile application, you use the same application builder, the same SQL and PL/SQL code, and with similar methods that you applied while developing the desktop version. Because of a single codebase, a mobile web application can be accessed from any mobile device, irrespective of operating system. The process of accessing such applications is very simple. All that is needed is to have the correct URL, that you put into the mobile browser, and respective id with password. The application code is not stored on the device but is delivered by the application server. This way you can easily handle application updates. You only need to update the application on the server, allowing potentially thousands of users to enjoy the latest version. The second advantage to this approach is that you are not required to send updates to every client (as required in native applications), which ensures that the accessed application is current with all provided features. Here are some pros and cons to web-based applications:

- Updates are uploaded only to the application server and become instantly available for all platforms and devices.
- Same application code for all browser-enabled mobile device.
- Use of same application building procedures and core web technologies.
- Doesn’t need app store approval. 

- To access these applications you need a reasonable Internet connection.
- Slower than native applications because these applications are based on interpreted code rather than compiled code.
- Not available in the app stores.
- Cannot interact with device hardware such as camera, microphone, compass, file uploading etc.

Native (On-Device): These applications are on the other side and are built for specific mobile operating system, such as Windows Mobile, Android, iOS, or BlackBerry. Native mobile applications are written for a specific target operating system in its own supported language. For instance, to develop an application for Windows device, you’ll use C# (C Sharp), for iOS devices it is Objective-C, and for Android, you need to be a master of Java. This means that your app is tied to a specific platform and won’t run on another. Native applications are downloaded and stored locally on the device. Because of this capability, these applications are considered better performers. Additionally, these applications have the biggest advantage to interact with different  device hardware (camera, compass, accelerometer, and more). Using a local data store (SQL Lite), these applications can even work when disconnected from the Internet. As a developer you have to handle version discrepancies because updates of these applications are downloaded manually. Let’s see what pros and cons this category has:

- Being native, it performs better than its counterpart.
- Offline availability.
- Complete access to device’s hardware.
- Can be added to and searched in an app store.

- Expensive to develop.
- Single platform support. Need to build a separate app for a different OS, which means additional time and cost.
- To get space on the device’s app store, your app is required to undergo an approval process. Continue >>>

An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"

Tuesday, 17 September 2013

Implement Custom Authentication Scheme in Oracle Application Express - Part IV


After receiving login information, the APEX engine evaluates and executes the current authentication scheme i.e. Custom. The scheme makes a call to a function named CUSTOM_AUTH. In conjunction with the CUSTOM_HASH function, the function authenticates the user using his/her credentials stored in the DEMO_USERS table. Here are the two functions that you are required to store in your database to implement custom authentication mechanism. The CUSTOM_HASH function is a subordinate function to the CUSTOM_AUTH function and is called from the parent function to obfuscate users’ password using hash algorithm. Besides assisting the custom authentication scheme, this function is used in section 10.3.9 in chapter 10 to obfuscate updated password in the DEMO_USERS table.

create or replace function custom_hash (p_username in varchar2, p_password in varchar2) return varchar2
l_password varchar2(4000);
l_salt varchar2(4000) := 'XV1MH24EC1IHDCQHSS6XQ6QTJSANT3';
-- This function should be wrapped, as the hash algorithm is exposed here. You can change the value of l_salt or the method of which to call the DBMS_OBFUSCATOIN toolkit, but you must reset all of your passwords if you choose to do this.
l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
(input_string => p_password || substr(l_salt,10,13) || p_username || substr(l_salt, 4,10)));
return l_password;

create or replace function custom_auth (p_username in VARCHAR2, p_password in VARCHAR2) return BOOLEAN is
l_password varchar2(4000);
l_stored_password varchar2(4000);
l_expires_on date;
l_count number;
-- First, check to see if the user is in the user table
select count(*) into l_count from demo_users where user_name = p_username;
if l_count > 0 then
-- Fetch the stored hashed password & expire date
select password, expires_on into l_stored_password, l_expires_on
from demo_users where user_name = p_username;
-- Next, check whether the user's account is expired. If it isn’t, execute the next statement, else return FALSE
if l_expires_on > sysdate or l_expires_on is null then
-- If the account is not expired, apply the custom hash function to the password
l_password := custom_hash(p_username, p_password);
-- Finally, compare them to see if they are the same and return either TRUE or FALSE
if l_password = l_stored_password then
return true;
return false;
end if;
return false;
end if;
-- The username provided is not in the DEMO_USERS table
return false;
end if;

The function receives username and password on line # 1 as parameters from the login form and compares this information with the values stored in the DEMO_USERS table after applying the CUSTOM_HASH function. If the provided information matches with the table values, the user is authenticated and is allowed to access the application.

Uploading Scripts in Oracle APEX

Follow the instructions mentioned below to add the two database functions using SQL Scripts utility.

1. Save the above two functions as files (Custom_Hash.sql and Custom_Auth.sql) on your PC’s desktop.

2. Select SQL Scripts from SQL Workshop.

3. Click the Upload button.

4. Click the Browse button, select Custom_Hash.sql file, and click Open.

5. Click Upload.

6. Repeat the same process for the Custom_Auth.sql file. After the upload, press the Run button individually for each file.

This action will create and store the two functions in your database that you can see using SQL Workshop | Object Browser and selecting Functions from the drop-down list. Now, when you enter the credentials (mentioned in the previous post), you'll be authenticated as legitimate users and will be provided access to the application. This way you can implement custom authentication scheme in Oracle Application Express to have a much better control over the process.

An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"

Implement Custom Authentication Scheme in Oracle Application Express - Part III


While creating the application we used the default Application Express Authentication Scheme (step 9) to access the application. In this exercise, you will create a custom authentication scheme based on a table named DEMO_USERS. This table will carry all users of our application with their respective obfuscated passwords. The key components of this authentication scheme are CUSTOM_AUTH and CUSTOM_HASH functions. These two functions compare the given username and password to the stored values in the DEMO_USERS table. If there is a match, the user is authenticated to access the application. Besides authentication, the table also contains couple of columns (Products and Admin_User) to incorporate authorization. Follow the steps mentioned below to create this important table to fulfill the custom authentication and  authorization requirements of our application. In SQL Commands under SQL Workshop, enter the following statements individually (ignoring the underlined text):

Statement # 1 - Create DEMO_USERS table:

Statement # 2 - Create Trigger
insert on "DEMO_USERS"
for each row
for c1 in (
select DEMO_USERS_SEQ.nextval next_val
from dual
) loop
:new.USER_ID := c1.next_val;
:new.admin_user := 'N';
:new.created_on := sysdate;
end loop;

Statement # 3 - Enable Trigger

Statement # 4 - Create Another Trigger
insert or update on "DEMO_USERS"
for each row
:NEW.user_name := upper(:NEW.user_name);

Statement # 5 - Enable Trigger

Statement # 6 - Create Sequence to auto-generate User_ID column

Statement # 7 - Add Application Administrator Record
('ADMIN','0CF8137A4E6A77A777C30D4AA85AC5DE', TO_DATE('01-01-2013','MM-DD-YYYY'), 0, 'Y', TO_DATE('12-31-

Statement # 8 - Add Application Developer Record
('DEMO','25F743BE60A13BC099A61DF1B8E734F7', TO_DATE('01-01-2013','MM-DD-YYYY'), 1000, 'Y', TO_DATE('12-31-
2020','MM-DD-YYYY'), 'N')

Statement # 9 - Add Application User Record
('TEST','988CB30F1EDE09BD710366F12475FED1', TO_DATE('01-01-2013','MM-DD-YYYY'), 2000, 'N', TO_DATE('12-31-
2020','MM-DD-YYYY'), 'N')

In the last three statements (7,8, and 9), you created three application users: Admin, Demo, and Test. You added these records to the table with obfuscated passwords: asa, demo, and test respectively. Once you add these three records, you can access the application with any of these credentials. But, first you need to add couple of database functions to handle the custom authentication scheme.
Continue >>>

An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"

Implement Custom Authentication Scheme in Oracle Application Express - Part II


After the successful login attempt, the username is stored in a built-in substitution string named APP_USER which can be referenced in subsequent processes to retrieve the username of the current user. Follow the steps mentioned below to create the custom scheme.

1. Go to Shared Components.

2. Click the Authentication Schemes link under the Security section.

3. Edit the existing Application Express Authentication scheme.

4. Change the Name to Custom Scheme.

5. Select Custom from the Scheme Type drop down list.

6. Type custom_auth in Authentication Function Name under the Settings pane.

7. Click the Apply Changes button. 

NOTE: In case of multiple schemes, the currently applied authentication scheme contains a check mark on its icon. Please make sure that the CUSTOM SCHEME we created in this exercise carries that check (as indicated in the above figure). If not, edit the scheme and click the Make Current Scheme button.
Continue >>>

An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"

Implement Custom Authentication Scheme in Oracle Application Express - Part I


Authentication is the process of identifying an individual, usually based on a user id and password. In a web application, you implement authentication to allow access only to authorized users. Form-based authentication in the most common type of authentication where a web form is presented to the user to provide his/her id and password. To prevent hackers from intercepting this sensitive information, a secure connection is used along with an encryption algorithm to send the information to the server.

To make your application secured you need to determine the identity of the user trying to access the application. As your first line of defense, provide a login form to the users where they input their identity along with password. For additional security you may also include an SSL certificate to your login page. An SSL Certificate (Secure Sockets Layer), also called a Digital Certificate, creates a secure link between a website and a visitor's browser.

With SSL, the browser encrypts all data that's sent to the server and decrypts all data that's received from the server. Similarly, the server encrypts all data that's sent to the browser and decrypts all data that's received from the browser. By ensuring that all data passed between the two remains private and secure, SSL encryption prevents hackers from stealing private information. SSL can also determine if data has been tampered with during transit and can also verify both client and server.

A digital secure certificate can be purchased from certification authorities (CA) like VeriSign, Thawte, Instantssl, Entrust etc. Once you get the certificate, provide it to your hosting provider who will set up the certificate in your Web server so that every time a page is accessed via the https protocol, it hits the secure server. Once that is set up, you can start building your Web pages that need to be secure.

In Oracle Application Express, the provided login details are validated against the selected scheme to check whether the user credentials are correct. The user is allowed to access the application after passing these checks. In APEX, this process is implemented through the use of following Authentication Schemes:

Application Express Accounts: Recall that we opted to use this scheme for our application while creating the blank structure of the application and provided Admin/Manager_2013 credentials to access the application (see Figure 2-18 in chapter 2). The application express engine authenticates this information using its built-in repository which stores and manages user accounts.

Database Accounts: This authentication scheme requires that a database user (schema) exists in the local database. When using this method, the user name and password of the database account is used to authenticate the user.

HTTP Header Variable: Authenticate externally, where the username is stored in a HTTP Header variable set by the web server.

LDAP Directory: Authentication of user/password with an authentication request to a Lightweight Directory Access Protocol (LDAP) server.

No Authentication (using DAD): This scheme authenticates users by adopting the current database user. This can be used in combination with a mod_plsql DAD configuration that uses basic authentication to set the database user session.

Open Door Credentials: Implementing this scheme allows all users to access the application just by entering a user name in the login page.

Oracle Application Server Single Sign-On: This delegates authentication to the Oracle AS Single Sign-On (SSO) Server. To use this authentication scheme, your site must have been registered as a partner application with the SSO server.

Custom: We will use this scheme in our application. It is based on the username and password values stored in the DEMO_USERS table and uses CUSTOM_AUTH and CUSTOM_HASH functions incorporated in chapter 2 section 2.37. Following the best practice method, you'll encrypt the data before storing it in the database. That way, if a hacker gains access to the database, he will not be able to easily read the password.
Continue >>>

An excerpt from the book "Create Rapid Web Applications Using Oracle Application Express - Second Edition"

Sunday, 15 September 2013

How to create a Dashboard for your Web-based Database Application - Part IV

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition 

Sales by Category: This region demonstrates the use of bar charts and presents sale figures of each category through this chart type.

1. Click the Create icon in the Regions section to create a new Region.

2. In the Region page, click on the Chart icon.

3. From Chart Rendering list, select Flash Chart. And click the Column chart option. 

4. Click on 3D Column.

5. Enter Sales by Category in Title, select Standard Region - No Padding for Region Template, 40 in Sequence and click the Next button. 

6. Select Look6 for Color Scheme, un-check the Show Labels and Show Values boxes, and click Next

7. Enter the following SQL Query in the Enter SQL Query or PL/SQL function returning a SQL Query box. Click Next.

select null, p.category label, sum(o.order_total)
from demo_orders o, demo_order_items oi, demo_product_info p
where o.order_id = oi.order_id
and oi.product_id = p.product_id
group by category order by 3 desc

8. Click the Create Region button.

9. Click the link of the newly created region and set the following attributes.

Start New Row: No -  used the same row
Column: Automatic - Next position is 9
New Column: Yes
Column Span: 4 - The region will span from column 9-12

10. Click the Chart Attributes tab on the same page to set the following chart attributes:

Chart Width: 300
Chart Height: 180
Animation: Appear

11. Save your changes by clicking the Apply Changes button.

NOTE:  In APEX you create a chart using a SQL query which has the following syntax: 
SELECT link, label, value
The link column helps you drill-down to further details. You’ll use the link column in chapter 8 - Generate Graphical Reports. Right now the drill-down functionality is not being used, therefore, a null is put in the link position to follow the syntax rule.

How to create a Dashboard for your Web-based Database Application - Part III

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition

Sales by Product Region: This region is intended to show individual product sale figures using a pie chart. Hovering the mouse pointer over the pie slices display those figures, as illustrated below.

1. Click the Create icon in the Regions section to create a new Region.

2. In the Region page, click on the Chart icon.

3. From Chart Rendering list, select Flash Chart and click the Pie & Doughnut option. In order to view the Flash impact, you need to have Flash Player 9 or above installed on your PC.

4. Click on 3D pie.

5. Enter Sales by Product in Title, select Standard Region - No Padding for Region Template, 30 in Sequence and click the Next button.

6. Select Look6 for Color Scheme, un-check the Show Labels and Show Values boxes, and click Next.

7. Enter the following SQL Query in the Enter SQL Query or PL/SQL function returning a SQL Query box. Click Next.

select null, p.product_name||' [$'||p.list_price||']' product, SUM(oi.quantity * oi.unit_price) sales
from demo_order_items oi, demo_product_info p
where oi.product_id = p.product_id
group by p.product_id, p.product_name, p.list_price
order by p.product_name desc

8. Click the Create Region button.

9. Click the link of the newly created region (Sales by Product) in the Regions section and set the following attributes. The region will be placed on the same row next to the existing one: This Month’s Sales. The next column position (5) will be selected automatically. This region will also span 4 columns from 5-8.

Start New Row: No
Column: Automatic
New Column: Yes
Column Span: 4

10. Click the Chart Attributes tab on the same page to set the following chart attributes:

Chart Width: 300
Chart Height: 180
Animation: Appear

11. Save your changes by clicking the Apply Changes button.  Continue>>>

NOTE:  You can define custom color scheme by adding your own colors to the chart with the help of color picker palette or by adding hex color values. Custom color scheme is used by setting the following two attributes:

Color Scheme: Custom
Custom Colors: 05FA05,#FF0000,#00ABFA,#87FA03,#FF8400,#DBF705,#0857F7,#7700FF,#112E01,#781200

Thursday, 12 September 2013

How to create database web application in Oracle APEX

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition
Follow the steps mentioned below to create a blank structure of a web application in Oracle Application Express. These instructions will create two application page: Home and Login. 

1. Login to the APEX development environment by entering http://localhost:8080/apex or  or http://yourcomputerIP:8080/apex in your browser. The localhost parameter specifies the server running your database. Since we have the database on the same machine, we'll set this parameter to localhost. In computer networking, localhost (meaning this computer) is the standard hostname given to the address of the loopback network interface. This mechanism is useful for programmers to test their software during development.

2. Enter ASA for Workspace, Admin for Username, the case-sensitive password (asa) that you set in step 8 in the previous section, and click the login to Application Express button. Once again, you’ll be asked to change the admin user password. Do so as you did before. To follow the password complexity rules, put a strong password for example: Manager_2013. Click the Return button after successfully changing the password and login to the develop environment with the new password.

3. Click the Application Builder icon.

4. In the Application Builder page, click the Create button to create a new application.

5. Click the Database icon.

6. Enter Sales Web Application in the Name box and match other attribute values with those set in the following figure. Click Next.

7. Accepting default values in the “Pages” page, click Next to move on.

8. In Shared Components Page, select No for Copy Shared Components from Another Application and click Next.

9. In the Attributes page, set Date and Time formats using the adjacent LOV buttons. Accept default values for Authentication Scheme and other attributes as shown in the figure below. Oracle APEX provides a number of predefined authentication mechanisms, including a built-in authentication framework and an extensible custom framework. In the selected default scheme (Application Express Accounts) users are managed and maintained in the APEX workspace. Note that in a subsequent section we will replace the default Application Express Authentication Scheme with custom authentication for this application. For the time being, click Next to proceed.

10. For User Interface Theme, select Blue Responsive (Theme 25) and click Next.

11. Click the Create Application button in the confirm page. Your screen should resemble the following figure. Note that APEX created the application with two pages, Page 1 (Home) and  Page 101 (Login). This main application interface can be viewed differently using the three buttons: icon, report and detail. By default, the page (as shown in the figure below) is presented in report view with relevant details.

12. Click the Run Application icon. Type admin and Manager_2013 for username and password, respectively. Click the Login button. You’ll see the Home page of your application (as illustrated below) displaying the currently logged in user (admin) along with the Logout link. Click the Logout link and then the Application 101 link at the bottom of your screen to move back to the development environment.

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition

How to create workspace for Oracle APEX application

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition
A workspace is a virtual private container allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private. You have to create a workspace before you create an application. It is necessary because you have to specify which workspace you want to connect to at the time of login. Without this piece of information, you are not allowed to enter Oracle Application Express.

1. Open a browser session and type http://localhost:8080/apex/apex_admin in the address bar to access the administrator’s control panel.

2. In the Username box type admin and for password, type Manager_2013. The next screen asks you to change the admin user password. Accede to the request by entering Manager_2013 in the Current Password box and providing a new password in the next two boxes. I set this Application Express ADMIN password to Gemini_2013. Click Apply Changes and then the Return button.

3. The new login screen will come up with an additional Workspace box carrying the INTERNAL value. Making sure the Username is Admin, enter the new password you set in the previous step to login. This will bring up the admin control panel interface.

4. Click on the Manage Workspace icon.

5. Under Workspace Actions, select the option: Create Workspace.

6. In the Workspace Name box type ASA and click Next.

7. On Identify Schema page set the following attributes and click Next. A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL statement. You should consider a schema to be the user account and collection of all objects therein. In this step you specified a schema ASA (Apex Sample Application) that APEX will create for you with some default objects including data tables carrying dummy data.

Re-use existing schema? No
Schema Name: ASA
Schema Password: asa
Space Quota (MB): 100 (accept the default values)

8. On Identify Administrator page set the following values and click Next. Note that currently you’re logged in as APEX administrator whereas, in this step, you’re creating administrator for the Sales Web Application workspace to manage workspace tasks such as creating the application, users and so on.

Administrator Username: ADMIN
Administrator Password: asa

9. Click the Create Workspace button followed by the Done button.

10. Click the Logout link located at the top right corner.

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition

How to upgrade Oracle Application Express (APEX) to a new release

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition
Oracle Database 11 g Express Edition (Oracle XE), that you just installed, includes Oracle Application Express release 4.0. In order to take advantage of the latest features you need to upgrade APEX to the most recent release. The following set of steps guide you how to do that:

1. Download the latest version of Application Express from the Oracle Technology Network.

2. Unzip the downloaded zip file ( and save its content under c:\apex. Make sure that all the files and folders go into c:\apex and not into c:\apex\apex.

3. Open a command prompt and type cd c:\apex and hit the Enter key. At C:\apex>  type: sqlplus /nolog and hit the Enter key. At SQL> type: CONNECT SYS as SYSDBA and hit Enter. For Password, type manager and hit Enter. You’ll be connected to your database. The following screen-shot illustrates this step.

4. At the SQL> prompt type: @apexins SYSAUX SYSAUX TEMP /i/ to install the latest Application Express version. The upgrade process may take approximately 30 minutes and will end-up with the message: Disconnected from Oracle Database 11g.

5. Log back into SQL*Plus (as above) and load images: SQL>@apxldimg.sql C:\  hit Enter.

6. Upgrade Application Express admin password:  SQL>@apxchpwd hit Enter. Type password for Application Express ADMIN account. I entered and used Manager_2013 in later sections of this book.