TECHNOLOGY: Browser-Based

As Published In January/February 2011

Extend Your Applications

Use plug-ins with Oracle Application Express 4.0 to add custom components.

Oracle Application Express 4.0 introduced a plug-in architecture that enables developers to extend their applications with custom item and region types, processes, and dynamic actions. Several plug-ins are free for downloading from Oracle Technology Network, and more than 40 plug-ins developed by members of the Oracle Application Express community are available from an external Website.

This column walks you through building an application that implements and uses three existing plug-ins. You can run through these steps on the hosted instance of Oracle Application Express 4.0 at apex.oracle.com. (You must request a free workspace to use this hosted instance.) You can also use a local instance of Oracle Application Express 4.0. This exercise is based on the EMP table, which is available by default in the database. 

Modifying the EMP Table

You’ll build a database application that enables users to view and edit employee data. Before you create the application, you need to add two new columns to the EMP table and change some user interface defaults. Log in to Oracle Application Express, and follow these steps to add PHONE_NUMBER and RATING columns to the EMP table: 

1. From the Oracle Application Express home page, select SQL Workshop -> Object Browser.
2. Select the EMP table from the list.
3. Click Add Column. Enter PHONE_NUMBER for Add Column, change its Datatype to VARCHAR2, enter 14 for Length, click Next, and click Finish.
4. Select the EMP table from the list again. Click Add Column, enter RATING for Add Column, enter 1 for Precision, click Next, and click Finish.

Now you’ll change some of the EMP table’s column labels to add some visual appeal to the demonstration application. With the EMP table still open in the Object Browser, follow these steps:  

1. Click UI Defaults on the menu.
2. Click Edit. 
3. Click EMPNO in the table to open the Column Defaults page for the EMPNO column.
4. Under General Defaults, replace EMPNO in the Label field with Employee Number and then click Apply Changes.

Repeat the preceding step to change the labels for the ENAME, MGR, HIREDATE, SAL, COMM, and DEPTNO columns to, respectively, Employee Name, Manager, Hire Date, Salary, Commission, and Department.

Creating the Demonstration Application

To create the demonstration application, follow these steps: 

1. From the Oracle Application Express home page, select Application Builder.  

2. Click Create. For Application Type, select Database and From Scratch.  

3. For Name, enter Employees, and then click Next.  

4. For Page Type, select Report and Form. Enter EMP for Table Name, click Add Page, and click Create.  

5. On the summary page, click Create.

Next you’ll install two plug-ins into the Employees application that will help users input data easily into the application’s Phone Number and Rating fields. You’ll also install a plug-in that generates an employee organization chart and adds it to the application’s report page.

Downloading and Installing Plug-Ins

Download the following plug-ins from Oracle Technology Network at oracle.com/technetwork/developer-tools/apex/plugins-155231.html, and unzip them to a convenient location on your system:

• Masked Text. This item-type plug-in makes it easy for users to enter fixed-width, formatted data (such as dates, phone numbers, and Social Security numbers).  

• Star Rating. This item-type plug-in enables your application’s users to rate something by clicking a star icon representing a rating in any range from 0 to 99, inclusive. 

Next, download and unzip the Google Visualizations plug-ins from apex-plugin.com, the community plug-in site. This set of dynamic-action, region-type plug-ins includes one that generates an organization chart from the EMP table.

To install the plug-ins, follow these steps: 

1. From Application Builder, select the Employees application.  

2. Click Shared Components. Under User Interface, click Plug-Ins.  

3. Click Import, click Browse, navigate to the location where you unzipped the Masked Text plug-in, and select item_type_plugin_com_oracle_apex_masked_field.sql. Click Next, click Next, click Install Plug-In, and then click Apply Changes.  

4. Click Import, click Browse, navigate to the location where you unzipped the Star Rating plug-in, and select item_type_plugin_com_oracle_apex_star_rating.sql. Click Next, click Next, click Install Plug-In, and click Apply Changes.  

5. Click Import, click Browse, navigate to where you unzipped the Google Visualizations plug-ins, and select region_type_plugin_com_logica_apex_googlevis_orgchart.sql. Click Next, click Next, click Install Plug-In, and click Apply Changes.   

Using Plug-Ins

Now you’ll replace standard Oracle Application Express components with the item-type plug-ins and define a plug-in region. To implement the item-type plug-ins, follow these steps:  

1. Click the Employees application in Application Builder, and click 2-Emp to navigate to Page 2.  

2. In the Page Rendering section, double-click the P2_PHONE_NUMBER item.  

3. Under Name, change the Display As value to Masked Field [Plug-in] . Note that the Settings section changes to show custom attributes for Input Mask and Placeholder. Click Apply Changes.

4. Double-click the P2_RATING item.  

5. Under Name, change the Display As value to Star Rating [Plug-in]. Under Settings, enter 5 for Number of Stars, and then click Apply Changes.

To implement the region-type plug-in, follow these steps: 

1. From Application Builder, click the Employees application and navigate to Page 1.  

2. Click Create, and select Region on this page.  

3. Select Plug-ins, select Google OrgChart, and then click Next.  

4. Enter Organization Chart for Title, and click Next.  

5. Select Small for Size, select Yes for Allow HTML, and click Next. 

Click Examples. Copy the second block of SQL code, paste it into Region Source, and then click Create Region.

Click the Run button to run the application. You can see the organization chart, shown in Figure 1, at the bottom of the report page.

Figure 1: Organization chart generated by the Google OrgChart plug-in

In the tabular portion of the report page, click the edit link (the pencil icon) for the employee named Allen to modify his record. Click in the Phone Number field in the form, and enter a phone number (including area code) by typing its 10 digits. Note that the field will not accept any nonnumeric characters; the parentheses that surround the area code and the hyphen that separates the phone number’s first three digits from the last four are supplied by the plug-in. Now hover the cursor over the star icons in the Rating field and click the fourth icon from the left to give Allen a four-star rating. Figure 2 shows data being entered into the Phone Number field and a star being selected from the star icons.
 

Figure 2: Data entry assisted by plug-ins

Click Apply Changes. In the report, you can now see 4 as Allen’s rating, and the phone number you entered appears as Allen’s phone number. 

Conclusion

Oracle Application Express plug-ins enable you to extend your applications easily with customizable components. Once you’ve imported and installed plug-ins, you use them in much the same way as you use standard Oracle Application Express components. You can choose among plug-ins developed by the Oracle Application Express team or the community. And you can build your own plug-ins by using PL/SQL. For information on building plug-ins, see Patrick Wolf’s blog posting “Oracle APEX 4.0: How to create a Plug-in”.

Send us your comments