Display Different Information on a Single APEX Page

How To Put Multiple Information Within a Single Region on an Application Page Using Tabbed Navigation List

 

A page is the basic building block of an application. When you build an application using APEX Application Builder, you create pages that contain user interface elements, such as regions, items, tabs, lists, buttons and more. By default, page creation wizards automatically add controls to a Page Definition based on your selection. You can add more controls to a page later on by using the Page Definition interface and can also use the Create Page Wizard to add components such as report, chart, form, calendar, or tree in the Page Definition.

You put items on a page under a specific region. A region is an area on a page that serves as a container for content. You can create multiple regions to visually segregate different sections on a page and to group page elements. A region may carry a SQL report or static HTML content which is determined by the region source. Each region can have its own template applied which controls their respective appearance.

In a previous blog post, you were guided on how to display different views of data on a single APEX application page using multiple regions. In this tutorial, you'll be shown how to put multiple information within a single region on an application page with the help of tabbed navigation list. Just like scroll bars, a tabbed navigation list can be used to increase the window surface area to show related information or different documents on a single web page, as shown in the following illustration. 

This post assumes that you are acquainted with different APEX components such as application, pages, shared components etc. If not, please go through previous posts to acquire the basic knowledge.

Create List

1. Assuming that you already have an application, click Shared Components in the Application Builder.
2. Under the Navigation region, select the Lists option.
3. Click the Create button.
4. Select the From Scratch option and click Next.
5. Enter Shipment Details for the list name, select Static for its type, and click Next.
6. Fill in the Query and Static Values page as shown below and click Next. Note that we used same Page ID for all four options because Page 5 will carry all relevant information.
7. Accept default values on the next page and click the Create List button. A new list named Shipment Details will be created.

Create Page

1. In the Application Builder, click the Create Page button.
2. Select the Blank Page option.
3. Accept the default value for Page Number. In my case, it is 5. I referred to this number in the previous section (see the figure above). Click Next.
4. In the page Name box, type MultiTabs and click Next.
5. On the Tabs Options page, select the first option: Do not use tabs. This option is selected because the page is created just to demonstrate a component's feature and is not directly linked to the application. Click Next.
6. Click the Finish button to create the page, and then the Edit Page button to call its definition.
7. Switch the page template from One Level Tabs - Left Sidebar to One Level Tabs - No Sidebar and apply the change.

Create Region

1. Click the create button (+) in the regions section to add a new one.
2. Select the List option to add a list region to the page.
3. In the Title box, type Shipment Details. Select No Template for Region Template attribute, and click Next.
4. On the Source page, select Shipment Details for List, and Tabbed Navigation List for List Template. Click Create List Region button to finish the wizard.
5. Create another region. This time select the first HTML option.
6. On the Region page, select HTML as the region type.
7. In the Title box, type Items & Expenses. For Parent Region, select Shipment Details to bring the new region under the Shipment Details region. Click Next and then the Create Region button to finish the wizard.
8. In the similar fashion, create three more regions namely: History, Landed Cost, and Workflow. Select Shipment Details as the parent region for all three.

At this stage if you run the page the output will be something like this:

In order to show details of the selected tab, you need to place some conditions for each region. The trick you'll be using here is to create a hidden item and assign it a default value of 1 which represents the first tab i.e. Items & Expenses. Similarly, the value 2 in this item will be used for the History tab and so on. So, let's create the hidden item first.

Create Hidden Page Item

1. Click create (+) in the Items section to create a new one.
2. Select the Hidden option and move on.
3. For Item Name, enter P5_REPORT and for Regions, select Shipment Details. Click Next.
4. Click Next.
5. On the Source page, enter 1 in the Default Value and click the Create Item button to finish the wizard.

Apply Condition

In the following steps, you'll apply a condition to evaluate which region is to be activated. Details under the selected tab will be shown, hiding irrelevant regions.

1. Modify the first region: Items & Expenses.
2. Scroll down to Conditions section and select Value of Item/Column in Expression 1 = Expression 2
3. In Expression 1 box, enter P5_REPORT i.e. name of the hidden item.
4. In Expression 2, type 1 which represents the first tab/region. The condition set here evaluates the value of P5_REPORT hidden item and shows the corresponding region based on it.

Using the same procedure, set conditions for the rest of the regions using 2, 3, & 4 values, respectively.

One last thing. In Shared Components, modify the Shipment Details list to assign appropriate value to the hidden item. Call the first list item i.e. Items & Expenses. Scroll down to the Target section, enter P5_REPORT in Set these items and 1 in With these values. At run time, when you click the first tab, 1 will be assigned to the hidden item and the corresponding condition set in the previous section will come to true resulting in the display of Items & Expenses region. Repeat this process for the remaining three items with respective values (2,3, and 4) for the Set these item attribute.    

Run the page to see the result. Initially, the first region (Items & Expenses) is displayed. When you click the History tab, the corresponding History region comes up. Similarly, the other two regions are displayed when their respective tab option is clicked. Now you can associate content (form controls, reports, etc.) with individual regions. 

Do It Yourself

Create a new list with two members: Items and Expenses. Put this list under the first region as shown below. Add separate content to each region using the techniques mentioned above and in my previous post.

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 a desktop application, it is used in the reports module to display a list of reports 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 the 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 Section |  Lists | 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 the 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.

Desktop Application Global Page

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

1. Click the Create Page button.

2. Click on the Global Page icon.

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

4. Click Create in the 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 specify 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 into 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 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 the 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 a button control group) to the center of the page.

9. Apply Changes.

The list would appear at the bottom of the mobile application as a 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 on the mobile global page.

2. Select HTML as the type of region.

3. Select HTML as a region container.

4. In the 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 the 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.

<style>
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;}
</style>

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

Watch the latest Oracle APEX videos

Creating Mobile Web Application Interface in Oracle Application Express

Oracle APEX allows you to create two types of interfaces: Desktop and jQuery Mobile smartphones. 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 the 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 the 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 are 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.

Home URL: f?p=&APP_ID.:HOME_JQM_SMARTPHONE:&SESSION.
Specifies the home page of the application for the current user interface.

Login URL: f?p=&APP_ID.:LOGIN_JQM_SMARTPHONE:&SESSION.
Points towards the login page of the application for the current user interface.

5. In Identify Theme, select Standard Themes as the 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 also 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 on 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 the user when he clicks the Logout button. Call and run the Home page again, log in 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"
 

Watch the latest Oracle APEX videos

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 sales are touching new heights every day, smartphone vendors are trying their level best to provide an excellent product with the 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 iPhones, 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 the 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 on mobile devices. In this chapter, you’ll declaratively build a mobile web application. APEX 4.2 allows you to rapidly build applications that can be accessed on a 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 applications 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 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 the 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 the respective id with a 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 of web-based applications:

Pros:
- 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 devices.
- Use of the same application-building procedures and core web technologies.
- Doesn’t need app store approval. 

Cons:
- 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 systems, 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 a 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:

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

Cons:
- 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"

Watch the latest Oracle APEX videos

Implement Custom Authentication Scheme in Oracle Application Express - Part IV

ADD DATABASE FUNCTION THROUGH ORACLE APEX

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.

CUSTOM_HASH Function
create or replace function custom_hash (p_username in varchar2, p_password in varchar2) return varchar2
is
l_password varchar2(4000);
l_salt varchar2(4000) := 'XV1MH24EC1IHDCQHSS6XQ6QTJSANT3';
begin
-- 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;
end;


CUSTOM_AUTH Function
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;
begin
-- 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;
else
return false;
end if;
else
return false;
end if;
else
-- The username provided is not in the DEMO_USERS table
return false;
end if;
end;
The function receives the 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 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 the 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 a legitimate user and will be provided access to the application. This way you can implement a custom authentication scheme in Oracle Application Express to have much better control over the process.

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

Watch the latest Oracle APEX videos

Implement Custom Authentication Scheme in Oracle Application Express - Part III

CREATE TABLE IN ORACLE APEX

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 a 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:
CREATE TABLE "DEMO_USERS"
( "USER_ID" NUMBER,
"USER_NAME" VARCHAR2(100),
"PASSWORD" VARCHAR2(4000),
"CREATED_ON" DATE,
"QUOTA" NUMBER,
"PRODUCTS" CHAR(1),
"EXPIRES_ON" DATE,
"ADMIN_USER" CHAR(1),
CONSTRAINT "DEMO_USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)

Statement # 2 - Create Trigger
CREATE OR REPLACE TRIGGER "BI_DEMO_USERS"
BEFORE
insert on "DEMO_USERS"
for each row
begin
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;
end;

Statement # 3 - Enable Trigger
ALTER TRIGGER "BI_DEMO_USERS" ENABLE

Statement # 4 - Create Another Trigger
CREATE OR REPLACE TRIGGER "DEMO_USERS_T1"
BEFORE
insert or update on "DEMO_USERS"
for each row
begin
:NEW.user_name := upper(:NEW.user_name);
end;

Statement # 5 - Enable Trigger
ALTER TRIGGER "DEMO_USERS_T1" ENABLE
 
Statement # 6 - Create Sequence to auto-generate User_ID column
CREATE SEQUENCE "DEMO_USERS_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 121 CACHE 20 NOORDER NOCYCLE
 
Statement # 7 - Add Application Administrator Record
INSERT INTO demo_users (USER_NAME, PASSWORD, CREATED_ON, QUOTA, PRODUCTS, EXPIRES_ON, ADMIN_USER) values
('ADMIN','0CF8137A4E6A77A777C30D4AA85AC5DE', TO_DATE('01-01-2013','MM-DD-YYYY'), 0, 'Y', TO_DATE('12-31-
2020','MM-DD-YYYY'),'Y')

Statement # 8 - Add Application Developer Record
INSERT INTO demo_users (USER_NAME, PASSWORD, CREATED_ON, QUOTA, PRODUCTS, EXPIRES_ON, ADMIN_USER) values
('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
INSERT INTO demo_users (USER_NAME, PASSWORD, CREATED_ON, QUOTA, PRODUCTS, EXPIRES_ON, ADMIN_USER) values
('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 a 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"
 

Watch the latest Oracle APEX videos

Implement Custom Authentication Scheme in Oracle Application Express - Part II

CREATE CUSTOM AUTHENTICATION SCHEME IN ORACLE APEX

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 the Authentication Function Name under the Settings pane.

7. Click the Apply Changes button. 

NOTE: In the 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"
 

Watch latest Oracle APEX videos

Implement Custom Authentication Scheme in Oracle Application Express - Part I

AUTHENTICATION SCHEMES IN ORACLE APPLICATION EXPRESS

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"

Watch latest Oracle APEX videos

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
FROM … 
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.

Watch latest Oracle APEX videos