| Oracle® Database Express Edition 2 Day Plus Application Express Developer Guide Release 2.1 Part Number B25310-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day Plus Application Express Developer Guide Release 2.1 Part Number B25310-01 |
|
|
View PDF |
A tabular form enables users to update multiple rows in a table at once using a single page. You can use the Tabular Form Wizard to create a tabular form that contains a built-in multiple row update process. This built-in process performs optimistic locking behind the scenes to maintain the data integrity.
This tutorial explains how to create a tabular form within a new application and then how to change one of the updatable columns from a text field to a select list.
This section contains the following topics:
To create the tabular form in this exercise, you need to log in to the sample database user account HR. Oracle Database Express Edition (Oracle Database XE) comes with a sample database user account called HR. This user owns a number of database tables in a sample schema that can be used to create applications for a fictional Human Resources department.
Topics in this section include:
For security reasons, the HR user account is locked after installation. You need to unlock this account before you can build a new application. If you have already unlocked the HR account, go to "Creating an Application".
To unlock the sample user account:
Open the Database Home Page login window:
On Windows, from the Start menu, select Programs (or All Programs), then Oracle Database 10g Express Edition, and then Go To Database Home Page.
On Linux, click the Application menu (on Gnome) or the K menu (on KDE), then point to Oracle Database 10g Express Edition, and then Go To Database Home Page.
At the Database Home Page login window, enter the following information:
Username: Enter system for the user name.
Password: Enter the password that was specified when Oracle Database XE was installed.
Click Login.
The Database Home Page appears.
Click the Administration icon, and then click Database Users.
Click the HR schema icon to display the user information for HR. (See Figure 1-1.)
Under Manage Database User, enter the following settings:
Password and Confirm Password: Enter hr for the password.
Account Status: Select Unlocked.
Roles: Ensure that both CONNECT and RESOURCE are enabled.
Click Alter User.
To log in to the HR user account:
Log out from the database administrator account by clicking Logout in the upper right corner of the page.
In the window, click Login.
In the Login window, enter hr for both the user name and password.
Click Login.
The Database Home Page appears.
First, you need to create an application using the Create Application Wizard.
To create an application using the Create Application Wizard:
On the Database Home Page, click the Application Builder icon.
The Application Builder home page appears.
Click Create.
Select Create Application and click Next.
Specify the page name.
For Name, enter Tabular Form.
For Application, accept the default.
For Create Application, select From scratch.
For Schema, accept the default.
Click Next.
Next, you need to add a page. You have the option of adding a blank page, a report, a form, a tabular form, or a report and form. For this exercise, you add one blank page.
Add a blank page:
Under Select Page Type, select Blank and click Add Page as shown in Figure 1-2.
The new page appears in the list at the top of the page.
Click Next.
For Tabs, accept the default, One Level of Tabs, and click Next.
For Copy Shared Components from Another Application, accept the default, No, and click Next.
For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.
For User Interface, select Theme 2 and click Next.
Review your selections and click Create.
The Application home page appears.
The Tabular Form Wizard creates a form to perform update, insert, and delete operations on multiple rows in a database table. Additionally, the wizard creates a multiple row update process that checks for MD5 checksum values before doing the update in order to prevent lost updates. The following exercise creates a tabular form on the HR table.
To create a tabular form using the Tabular Form Wizard:
Navigate to the Application home page. On the Database Home Page, click the Application Builder icon.
Select Tabular Form.
Click Create Page.
For the page type, select Form and click Next.
Select Tabular Form and click Next.
For Table/View Owner:
From Table/View Owner, select HR.
From Allowed Operations, accept the default, Update, Insert, and Delete.
Click Next.
For Table/View Name, select EMPLOYEES and click Next.
For Displayed Columns:
For Select Columns, press CTRL and select the following columns:
FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY, DEPARTMENT_ID
Note:
This exercise limits the number of columns to optimize the display on-screen. For a real form, you would probably want to include additional columns.Click Next.
For Primary Key, accept the default ID (Number) and click Next.
For Source Type, accept the default, Existing trigger, and click Next.
For Updatable Columns, select all columns and click Next:
On Identify Page and Region Attributes:
For Page, accept the default.
For Page Name, enter Tabular Form.
For Region Title, enter Tabular Form.
For Region Template and Report Template, accept the defaults.
Click Next.
For Tab, accept the default, Do not use tabs, and click Next.
For Button Labels:
Accept the defaults for the Cancel, Delete, and Add Row buttons.
For the Submit button, enter Apply Changes.
Click Next.
Accept the remaining defaults, click Next.
Confirm your selections and click Finish.
Next, run the page to view your new form.
To run the page:
Click the Run Page icon as shown in Figure 1-3.
If prompted to enter a username and password:
For User Name, enter hr.
For Password, enter hr.
Click Login.
The tabular form appears as shown in Figure 1-4.
As shown in Figure 1-4, note the tabular form contains four buttons. Cancel, Delete, and Apply Changes display in the upper left corner and Add Row displays in the bottom right corner. Additionally, a check box appears to the left of each row enabling the users to select one row. Users can also select all rows at once by selecting the check box to the left of the column headings. That same check box is also used in conjunction with the Delete button to identify the rows to be deleted.
When the Tabular Form Wizard creates a tabular form, updatable columns display by default as text fields. You can change this default display by editing report column attributes. In the next exercise, you change the default display of the Department Id column to a select list.
To change the default display of a column to a select list:
Navigate to the Page Definition for page 2. Click Edit Page 2 on the Developer toolbar as shown in Figure 1-5.
Under Regions, click Report next to Tabular Form as shown Figure 1-6.
The Report Attributes page appears.
Under Column Attributes, click the Edit icon next to the DEPARTMENT_ID column as shown in Figure 1-7.
The Column Attributes page appears. Next, change the default display of this column to a select list.
Scroll down to Tabular Form Element.
From Display As, select Select List (query based LOV).
Next, create your query.
Scroll down to List of Values and enter the following in LOV Query:
SELECT DISTINCT department_id a, department_id b FROM employees
Scroll up to the top of the page and click Apply Changes.
Click the Run Page icon in the upper right corner the page.
As shown in Figure 1-8, notice the Department Id column now displays as a select list.
Figure 1-8 Tabular Form with Department Id Column Changed to a Select List
Note:
Do not modify the select list of a SQL statement of a tabular form after it has been generated. Doing so can result in a checksum error when altering data of the form and applying updates.Consider the following example:
SELECT first_name FROM employees;
Note that this should not be altered to:
SELECT lower(first_name) FROM employees
