Table Of Content

1- Introduction

2- Create a Tabular Form using Wizard

3- Create Tabular Form manually

4- Handling Tabular form

4.1- Add rows
4.2- Multiple row update
4.3- Deleting multiple rows immediately
4.4- Deleting multiple rows - Prompt before deleting

5- Master-Details

6- Maybe you are interested

Oracle APEX Tabular Form Tutorial

View more Tutorials:

Oracle APEX Tutorials

1- Introduction

2- Create a Tabular Form using Wizard

3- Create Tabular Form manually

4- Handling Tabular form

4.1- Add rows
4.2- Multiple row update
4.3- Deleting multiple rows immediately
4.4- Deleting multiple rows - Prompt before deleting

5- Master-Details

6- Maybe you are interested

1- Introduction

You are viewing guidelines for programming Oracle APEX 5.0. This is the 2nd document which next of:

Oracle APEX Tutorial for Beginners (APEX 5.0)

Included in this document, I will continue to guide you on Oracle APEX Database Desktop Application, using Tabular Form.

2- Create a Tabular Form using Wizard

http://localhost:8080/apex

Go to "Database Application".

Next, going to "Hello Database Desktop Application" that you created in the previous guidance

In this application, there are a few pages created in the previous tutorial. Click " Create Page" to create another page.

Create a Tabular Form:

Enter:

Page: 7
Page Name: Emp Tabular Form (7)

Your web page has been created, click RUN to test the newly created page.

Page 7 is running:

Here, the question arises as to how to change some input fields into SELECT LIST or POPUP LOV (List of values). For example, you want to choose a Manager (MGR) through POPUP LOV, and select a Job through SELECT LIST.

Return to the design screen of page 7. Set the properties for the JOB column so that it displays as a SELECT LIST.

Identification:

Type: Select List

List of Values

Type: Static Value
Static Value:

STATIC:
CLERK;CLERK,
SALESMAN;SALESMAN,
PRESIDENT;PRESIDENT,
MANAGER;MANAGER,
ANALYST;ANALYST

The syntax used to declare the static values

STATIC:Display1;Return1,Display2;Return2

Next, you need to set the properties for the MGR so that it shows up as POPUP LOV (list of values)

Identification:

Type: Popup LOV (shows display values)

List of Values:

Type: SQL Query
SQL Query: Select emp.ename, emp.empno from Emp

Similarly set the properties for DEPTNO, so that it displays as a POPUP LOV.

Identification:

Type: Popup LOV (shows display values)

List of Values

Type: SQL Query
SQL Query: Select d.dname, d.deptno from Dept d

Save and run the Page 7:

3- Create Tabular Form manually

Above, I showed you how to create a Tabular Form by using the wizard of Oracle Apex. Next we will create completely from scratch a Tabular Form, not using the wizard, which will help you understand more about Tabular Form & Oracle APEX.

Enter:

Page Number: 8
Name: Emp Tabular Form (8)

A blank page has been created.

Create a Tabular Form in the "Content Body" region:

Title: Emp Tabular Form (8)
Type: Tabular Form
SQL Query:

select EMPNO,
       EMPNO EMPNO_DISPLAY,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP

You can test page 8, result is as follows:

Add a column contains Checkbox, which used to select rows.

Set attributes for EMPNO, hide this column, and set the primary key attribute for it.

Identification

Column Name: EMPNO
Type: Hidden Column (saves state)

Primary Key Source

Type: Existing sequence
Name: EMP_SEQ

Column ENAME:

Identification

Column Name: ENAME
Type: Text Field

Column JOB:

Identification

Column Name: JOB
Type: Select List

List of Values

Type: Static Values
Static Values:

STATIC:
CLERK;CLERK,
SALESMAN;SALESMAN,
PRESIDENT;PRESIDENT,
MANAGER;MANAGER,
ANALYST;ANALYST

Column MGR:

Identification

Column Name: MGR
Type: Popup LOV (shows display values)

List of Values

Type: SQL Query
Static Values: Select emp.ename, emp.empno from Emp

Column HIREDATE:

Identification

Column Name: HIDEDATE
Type: Date Picker

Appearance

Format Mask: YYYY-MM-DD

Default

Type: PL/SQL Expression
PL/SQL Expression: sysdate

Column SAL:

Identification

Column Name: SAL
Type: Text Field

SAL (Salary) Column is a type of numeric, so you need to add validation:

Identification

Name: SAL must be numeric

Validation:

Type: Column is numeric
Column: SAL

Error

Error Message: #COLUMN_HEADER# must have a value.
Display Location: Inline with Field and in Notification

Note :

#COLUMN_HEADER# is one that acts as a Place Holder, it will be replaced by the title of the corresponding column at runtime.

Column COMM:

Identification

Column Name: COMM
Type: Text Field

Create Validation for COMM, COMM must be numeric.

Identification

Name: COMM must be numeric

Validation:

Type: Column is numeric
Column: COMM

Error

Error Message: #COLUMN_HEADER# must have a value.
Display Location: Inline with Field and in Notification

Column DEPTNO:

Identification:

Type: Popup LOV (shows display values)

List of Values

Type: SQL Query
SQL Query: Select d.dname, d.deptno from Dept d

4- Handling Tabular form

Next we need to add more processing includes create row, edit rows, delete rows

Change name of the buttons, as shown below:

For Cancel button:

When the user clicks the Cancel button, website will refresh the current page (Here is page 8).

4.1- Add rows

When users click on the ADD button, table on page will automatically add a new row, which allows user to enter data. You need to call javascript function to accomplish this.

Idetification

Button Name: ADD
Label: Add

Behavior

Action: Redirect to URL
Target: javascript:apex.widget.tabular.addRow();

4.2- Multiple row update

Idetification

Button Name: SAVE
Label: Save

Behavior

Action: Submit Page

SAVE button need to save the information of several records at once, including new records, modified records. You need to declare a process to handle this. This process will be run once immediately after the page is called, it will update the records that have changes and insert new records.

Create Process:

Idetification

Name: ApplyMRU
Type: Tabular Form - Multi Row Update

Settings

Table Name: EMP
Primary Key Column: EMPNO

Execution Options

Tabular Form: Emp Tabular Form (8)
Run Process: Once Per Page Visit (default)

Success Messages

Success Message: #MRU_COUNT# row(s) updated, #MRI_COUNT# row(s) inserted.

Condition

When Button Pressed: SAVE (Name of button)
Execution Scope: For Created and Modified Rows

4.3- Deleting multiple rows immediately

Handling button to immediately delete the selected records, no need to confirm:

To delete many rows you need to create a Process to handle this. This process will be executed once as soon as the page is called.

Create Process:

Idetification

Name: ApplyMRD_IMMEDIATELY
Type: Tabular Form - Multi Row Delete

Settings

Table Name: EMP
Primary Key Column: EMPNO

Execution Options

Tabular Form: Emp Tabular Form (8)
Run Process: Once Per Page Visit (default)

Success Messages

Success Message: #MRD_COUNT# row(s) deleted.

Condition

When Button Pressed: IMMEDIATELY_MULTY_ROW_DELETE (Name of button)
Execution Scope: For Created and Modified Rows

4.4- Deleting multiple rows - Prompt before deleting

Before deleting records, the program asks whether the user agrees to delete or not?

Idetification

Button Name: MULTI_ROW_DELETE
Label: Multi Row Delete

Behavior

Action: Redirect URL
Target: javascript:apex.confirm('Are you sure to delete?','MULTI_ROW_DELETE');

apex.confirm (...) is a JavaScript function used to ask the user before executing the command of button.

// The function includes 2 parameters, message and Button Name.

apex.confirm('Message ...', 'Button Name');

// Example:

apex.confirm('Are you sure to delete?','MULTI_ROW_DELETE');

// Using variable.
// htmldb_delete_message is a javascript variable
// with value: 'Would you like to perform this delete action?'
// (You need to declare this variable).
apex.confirm(htmldb_delete_message,'MULTI_ROW_DELETE');

// Declare a variable named htmldb_delete_message
var htmldb_delete_message='Would you like to perform this delete action?';

// Or:
// "DELETE_CONFIRM_MSG" is a constant available in APEX.
// It has value: Would you like to delete this thực action?


var htmldb_delete_message='"DELETE_CONFIRM_MSG"';


// Then can use this variable in the whole page.

apex.confirm(htmldb_delete_message,'MULTI_ROW_DELETE');

To delete many rows you need to create a Process to handle this. This process will be executed once as soon as the page is called.

Idetification