Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Data Manipulation

Data Manipulation is the built-in process types designed to reduce the amount of manual programming.  When wizards are used to build forms and tabular forms, these types of processes are built by the wizard to INSERT, UPDATE, and DELETE data from the database.  Although they are a little tricky to learn to use and build manually, many benefits will be gained with their use.

These types of processes are dynamic in nature.  What that means is, if after the web page form is initially built and another page item is added to the page, then the dynamic process will include that new field in the SQL, generated behind the scenes.  The single most beneficial feature gained from using the dynamic processes is row level concurrency.

Row Level Concurrency is a built-in functionality with the dynamic processes that have not been seen in other development languages.  It prevents two different users from updating the same row of data.  For example, assume two users open an employee record for Jean Peters at the same time.

  • User #1:  Since Jean Peters has married Howard Hughes, user #1 is changing her last name to Hughes and her marital status to married.

  • User #2:  Jean Peters got a raise last week and is making the change to her employment record.

User #1 makes the changes and saves the data.  At this point in time, User #2 is now looking at data on the screen that is not up to date.  When user #2 attempts to update the data, she will get a checksum error.  This is because HTML DB recognizes the data in the database has changed since the data was originally selected from the database.  User #2 is prevented from updating the record, and it is a good thing because if it were allowed, the name would have been updated back to Jean Peters thereby erasing the work done by user #1.

Automatic Row Processing is covered in more detail in a previous chapter of this text.

The types of processes available in the Data Manipulation wizard are:

  •  Automated Row Fetch:  When built by the HTML DB wizard, this type of process is created as an After Header page process which executes during page rendering.  It dynamically builds the SQL select statement, executes the statement, and sets the session state for the appropriate page items.  Even though this type of process is usually built by an HTLM DB wizard, it can also be created manually.

  • Automatic Row Processing (DML):  This is the partner to the Automated Row Fetch process described above.  When the wizard is used to build a form page, both of these processes will be created.  This process, Automatic Row Processing (DML) process, will be created as an After Submit – After Computations and Validations process.  It will dynamically build the SQL to perform INSERT, UPDATE and DELETE statements against the database.

  • Multi Row Update:   This type of process will update multiple rows of modified data from a Tabular Form.  A tabular form is a report, but has the region type set to SQL Query (updateable report).  The properties are set for each of the columns in the Column Attributes page, in the Tabular Form Element region.

  • Multi Row Delete:   This is similar to the Multi Row Update process described above, except it performs the SQL delete statement.  This process needs to know the names of the primary key columns to know how to dynamically build the delete statement.

  • Add Rows to Tabular Form:   This type of process will cause the creation of an empty row at the bottom of the tabular form so that data can be entered for a new row.  When using this type of process, a process should be available that would update any currently changed data prior to creating the empty row.  Otherwise the changes would be lost.  If the wizard is used to build the tabular form, this extra Multi Row Update process is created automatically.

Although the dynamic processes described in this section are powerful and beneficial, there is one drawback.  They are limited to use on tables that have a maximum of a two-column unique index or primary key.  If a table exists with three or more columns defined on the unique index, custom code in a PL/SQL type process will need to be written.

The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty  

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.