Oracle Apex - Tabular Form

1 - About

A form that performs update, insert, and delete operations on multiple rows in a database table.

A tabular form is rendered as a report having updatable columns shown using various form elements.

3 - Creation

3.1 - Wizard

This form includes a built-in multiple row update process that performs optimistic locking behind the scenes to maintain the data integrity.

Using the Create page Wizard you determine how rows are identified. You can either specify:

  • a primary key defined on the table, (PK Source: trigger, PL/SQL function, and sequence)
  • or a ROWID pseudo column,

which uniquely identifies a row in a table.

3.2 - Manually

manually by creating a updatable report region or standard report region with updatable columns with custom PL/SQL page processes to process the tabular form data.

4 - Processing

Unlike a single record from, tabular forms do not store data in session state. When a tabular form page is submitted, the tabular form data is processed using:

  • built-in data manipulation language (DML) (MRU and MRD),
  • or a custom PL/SQL page processes.

4.1 - Process

Processen

4.2 - MRU/MRD

A tabular form with automatic DML has two processes for multi-row operations:

  • A Multi Row Update (MRU) process performs create and update operations
  • Multi Row Delete (MRD) process deletes requests on one or more rows

MRU and MRD processes reference the underlying table name and the primary key columns.

4.2.1 - Requirement

  • The primary key columns must be part of the tabular form SQL query,
  • The report columns must be either hidden or display-only (save state) (in order for the DML processes to be able to identify the correct records)

4.2.2 - Insert

For new records, the primary key values must be NULL to be identified as new records.

4.2.3 - Delete

Delete operations are performed by referencing row number of the row to be deleted.

Users can check one or more rows on the current page and when they click the Delete button, the row numbers identify the corresponding primary key value(s) and the matching rows are deleted.

4.3 - Validation

You can reference the items values posted by an HTML form using the PL/SQL variable APEX_APPLICATION.G_F01 to APEX_APPLICATION.G_F50. Because this element is an array, you can reference values directly, for example:


FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP 
    htp.p('element '||I||' has a value of '||APEX_APPLICATION.G_F01(i)); 
END LOOP;

See:

5 - API

  • APEX$ROW_NUM refers the currently processed row number of a submitted tabular form data.
  • Use APEX$ROW_SELECTOR in validations, processes, and conditions associated with a tabular form to refer to the row selector check box in a tabular form. This placeholder returns X if the tabular form row selector check box of the currently processed tabular form row is checked and NULL if it unchecked

6 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap