Oracle APEX 23 Course For Beginners

Oracle APEX 23 Course For Beginners
Oracle APEX 23 Course For Beginners

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










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






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"






Tuesday 17 September 2013

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"
 
 



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"
 






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"
 






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"





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






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 http://127.0.0.1:8080/apex  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





Display Data Dynamically In A Gauge Chart

In this tutorial, we will learn how to display customer's ordered data in a gauge chart dynamically. As you choose a customer name from ...