| |
 |
|
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:
-
: 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.
-
: 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.
-
: 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.
-
: 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.
-
: 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
http://www.rampant-books.com/book_2005_2_html_db.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
