Friday, 20 January 2017

New Book on Oracle Application Express 5.1

Oracle Application Express 5.1 Basics & Beyond

 Oracle APEX 5.1
You will find stuff about workspace, application, page, and so on in every APEX book. But this book is unique because the information it contains is not available anywhere else! Unlike other books, it adopts a stimulating approach to reveal almost every feature necessary for the beginners of Oracle APEX and also takes them beyond the basics.
As a technology enthusiast I write on a variety of new technologies, but writing books on Oracle Application Express is my passion. The blood pumping comments I get from my readers on Amazon (and in my inbox) are the main forces that motivate me to write a book whenever a new version of Oracle APEX is launched. This is my fifth book on Oracle APEX (and the best so far) written after discovering the latest 5.1 version. As usual, I’m sharing my personal learning experience through this book to expose this unique rapid web application development platform.
In Oracle Application Express you can build robust web applications. The new version is launched with some more prolific tools to maximize developers’ productivity. Once again, I’ve left out the boring bits and have adopted the same practical inspirational approach that has exposed the anatomy of Oracle Application Express to thousands of beginners in the past.
The most convincing way to explore a technology is to apply it to a real world problem. In this book, you’ll develop a sales web application that not only reveals the anatomy of Oracle Application Express, but at the same time provides hands-on techniques that build a solid foundation for you to become a web developer.
Since there are lots of changes in the new version, all content has been revised to slot in these changes, including the features new to version 5.1. The short list below summarizes the features of Oracle APEX 5.1 covered in this book:
  • Hands-on exposure to the new features, such as Interactive Grid, Oracle JET Charts, the new declarative Master-Detail-Detail capabilities, dozens of new properties, and new development procedures
  • Teaches how to rapidly develop data-centric web application for desktops, laptops, tablets, and latest smartphones
  • Produce highly formatted PDF reports, including invoices, grouped reports, and pivot tables (not covered in any other APEX book)
  • Design and implement a comprehensive custom security module (unique to this book)
  • Step-by-step instructions to create mobile version of the application using existing desktop application pages (not covered in any other beginner’s guide)
This is a concise yet a concrete book on Oracle Application Express, written for those who want to become web application developers. The sticky inspirational approach adopted in this book not only exposes the technology, but also draws you in and keeps your interest up till the last exercise. I’m grateful to all my readers whose helpful feedback enabled me to further polish my work in this edition.

Wednesday, 11 December 2013

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.

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"