As Published In July/August 2006

DEVELOPER: Browser-Based

Single-Step Deployment

By Raj Mattamal

Oracle Application Express deploys an application with a single file.

With Oracle Application Express 2.2, when you build an application you want to share, you can create a single file that comprises the export—database objects, application components, and all. To demonstrate how simple it is to take advantage of this new convenience feature, this column walks you through the process of building a simple employee lookup application and then packaging up that application for single-step deployment.

Step 1: Set Up the Table, Photo-Rendering Procedure, and Seed Data

To get started, create (or have your Oracle Application Express administrator create) a new Oracle Application Express workspace. Once you've logged in to the workspace, run om_employees.sql to create the OM_EMPLOYEES table in your schema, along with its trigger and constraints. To run the script

1. Click SQL Workshop .
2. Click SQL Scripts .
3. Click Upload .
4. Click Browse; select the om_employees.sql script, available with the sample code for this article; click Open ; and click Upload .
5. On the resulting page, click the om_employees.sql icon.
6. Click Run , and then click Run on the Confirmation screen.

Repeat steps 2 through 6, uploading and then running the om_download_pict.sql and seed_data.sql scripts (available in the o46browser.zip file).

Step 2: Create the Application

Now create a simple two-page application based on OM_EMPLOYEES:

1. Click the Application Builder tab.
2. Click Create .
3. Accept the default Create Application option, and click Next .
4. Enter Oracle Magazine Employees in the Name field, and click Next .
5. Select Report and Form as the page type, select OM_EMPLOYEES as the tablename, and click Add Page .
6. Click Next to continue. Click Next again on each of the subsequent pages (Tabs, Shared Components, Attributes, User Interface), accepting the defaults, to complete the wizard. (On the User Interface page, select a theme other than the default— Theme 1 , if you like.)
7. Click Create on the last page (Confirmation) of the wizard.

Step 3: Modify the Application to Allow Employees to Upload Photos

The next step is to modify the two generated pages so that the application is read-only, while allowing users to upload photos to their respective detail pages.

1. In the listing of pages shown for the application, click the OM_EMPLOYEES link for page 1.
2. In the Buttons section, click the Create link and then click Delete to delete page 1's Create button. Confirm the deletion when prompted.
3. In the Regions section, click the OM_EMPLOYEES link to edit the query used by the report. In the Region Source text box, add a table alias of OME to the OM_EMPLOYEES table used in the query and replace the MANAGER_ID column with this inline select statement: 

(select userid from OM_EMPLOYEES ome2 where ome2.employee_id = 
ome.manager_id)

4. Add a column alias Manager to the inline query above, so it can serve as a column heading in the resulting report. Listing 1 shows an excerpt of the relevant code (in bold) for the modified query to add to the Region Source text box.

Code Listing 1: _Modified query 

select "EMPLOYEE_ID",
"NAME_FIRST",
"NAME_LAST",
"TITLE",
"EMAIL",
"PHONE_OFFICE",
"PHONE_MOBILE", 
"STREET_ADDRESS",
"CITY",
"STATE",
"ZIP_CODE",
"HIRE_DATE",
"USERID",
(select userid from OM_EMPLOYEES ome2 where ome2.employee_id=ome.manager_id) 
"Manager",
"MIME_TYPE",
"BLOB_CONTENT"
from   "OM_EMPLOYEES" OME
where 
. . .
<snip>

5. Click the Report Attributes tab.
6. Deselect the Show check box for the MIME_TYPE and BLOB_CONTENT columns.
7. Click Apply Changes .

Make page 2 read-only. Enter 2 in the Page field, and click Go to navigate to page 2 of the application.

1. In the Buttons section, click the Create link and then click Delete to delete page 2's Create button. Confirm the deletion when prompted.
2. In the Buttons section, click the Delete link and then click Delete to delete page 2's Delete button. Confirm the deletion when prompted.
3. In the Items section, click the P2_ NAME_FIRST item link and make it read-only by first clicking the Read Only navigation button and then clicking the rust-colored [always] link.
4. Click the right arrow (>) in the navigation section to go to the next item.
5. Repeat steps 3 and 4 for each item until you finish P2_USERID and get to P2_MANAGER_ID.
6. For P2_MANAGER_ID, in the Name region for Display As , select Display as Text (based on LOV, does not save state) .
7. For P2_MANAGER_ID's Label attribute, enter Manager .
8. In P2_MANAGER_ID's List of Values section, in the List of values definition field, enter this query: 

select userid, employee_id 
from om_employees

9. Click the right arrow to navigate to the next item (P2_MIME_TYPE).
10. For P2_MIME_TYPE, click the Conditions navigation button, select Never as the condition type, and then click the right arrow to navigate to the P2_BLOB_CONTENT item.

Modify page 2 to support photo display. The application should display a photo only if one is actually stored in the table. If there's no photo stored, and the userid in the table matches that of the logged-in user, the page should display an Upload button. To provide these capabilities, make these changes to the P2_BLOB_CONTENT item:

1. Click the Label navigation button, and enter Photo in the Label field.
2. Click the Source navigation button, and for the source type, select PL/SQL Expression or Function .
3. Then in the Source value or expression field, enter 

htf.img ('#OWNER#.om_download_pict?
p_id='||:P2_EMPLOYEE_ID)

4. Click the Conditions navigation button, and for the condition type, select Exists (SQL Query returns at least one row) .
5. In the Expression 1 field, enter this query: 

                               
select 1 from OM_EMPLOYEES where :P2_EMPLOYEE_ID
 = employee_id and blob_content is not null

                            

6. Click Apply Changes .

Add an item for the photo. Next, the application must provide a means for users to upload their photos:

1. Click the Create icon in the Items section of the page 2 definition screen.
2. Select File Browse , and click Next .
3. Enter P2_PHOTO as the item name, select the OM_EMPLOYEES region, and click Next .
4. Click Next and then Create Item on the subsequent page.
5. Click the P2_PHOTO item link.
6. Click the Conditions navigation button; for the condition type, select Exists (SQL Query returns at least one row) ; and in the Expression 1 field, enter this query: 

                               
select 1 from OM_EMPLOYEES where :P2_EMPLOYEE_ID =
  employee_id and userid = :APP_USER and blob_content is null

7. Click Apply Changes .

Modify other page 2 UI components for photo handling. Just a few more steps to complete the application:

1. In the Buttons section of page 2's definition, click the Apply Changes link to edit the SAVE button's properties.
2. In the Text Label field, enter Upload Photo .
3. To ensure that the button renders only if users are viewing their own records and that there's no photo already stored in the database, change the condition for rendering the SAVE button. Click the Conditions navigation button; for the condition type, select Exists (SQL query returns at least one row) ; and enter this query in the Expression 1 box: 

                               
select 1 from OM_EMPLOYEES where :P2_EMPLOYEE_ID =
  employee_id and userid = :APP_USER and blob_content is null

                           

4. Click Apply Changes .
5. In the Buttons section of page 2's definition, click the Cancel link to edit the Cancel button.
6. In the Text Label field, enter Go Back .
7. Click Apply Changes .
8. In the Page Processing, Processes region, under After Submit , click the Process Row of OM_EMPLOYEES link, and click the Delete button. Confirm the deletion when prompted.
9. Click the Create icon in that same Processes region to create a new After Submit process.
10. Select PL/SQL , and click Next .
11. Enter Store Photo as the name of the process, and click Next .
12. Copy and paste the code from Listing 2 into the Enter PL/SQL Page Process text block, and click Next .

Code Listing 2: _Condition text for photo upload 

IF ( :P2_PHOTO is not null ) THEN 
update OM_EMPLOYEES e
set (blob_content, mime_type) = ( 
select blob_content, mime_type
from htmldb_application_files 
where name = :P2_PHOTO)
where employee_id = :P2_EMPLOYEE_ID;
DELETE from HTMLDB_APPLICATION_FILES WHERE name = :P2_PHOTO;
END IF;

13. Click Next and Create Process to finish creating the Store Photo process.

At this point, you should have a functional employee lookup application. Run the application, and you'll see that you can search for employees and view detail information about them. Create an Oracle Application Express user account to match one of the userids in the OM_EMPLOYEES table and log in to the application using that account, and you'll see the photo upload capability and display.

Step 4: Package Up the Application

After you try out the application, you might decide that others might find it useful enough to install in their own Oracle Application Express instances. To this end, the last step is packaging up the application for single-step deployment to another instance.

1. In the breadcrumb links in the upper left-hand corner of the screen, click the Application nnn link (where nnn corresponds to the ID of the employee lookup application in your Oracle Application Express instance).
2. Click the Edit Attributes icon, and then click Supporting Objects .
3. Click Edit .

18. Click Apply Changes .

And that's it. Now when you export this application, everything comes with it: the DDL, the seed data, and the Oracle Application Express application definition. To move the application to another Oracle Application Express 2.2 instance, simply import and install the single export file. 

Send us your comments