|
By
Art Collins, OCP
Introduction
With all the rich features Oracle Forms offer
a developer, one of the least understood and
least utilized functionality is the use of
stored procedure data blocks. This functionality
is really not new. It was first introduced
in Forms version 5.0, but has received very
little press coverage so far. This article
will shed some light on stored procedure data
blocks, describing why they are useful, and
how to implement them.
View
the source code for this article
The
Problem
The traditional method of creating a data
block has always been a simple but constrained
process. Prior to version 5.0, the only way
to generate one was to base it from a single
table. This technique does not meet the real-world
needs of relational database systems, since
most forms require data from multiple tables
(often requiring complex joins). Experienced
developers discovered two basic ways to circumvent
this problem:
- Create a database view from these complex
joins, or
- Write queries in post-query trigger to
fetch the remaining data not already retrieved
from the base table.
These two solutions have some serious drawbacks.
First, creating a view for each form is not
always possible. Since a view is basically
a SQL statement, there will be instances where
it's not possible to retrieve all the data
in one well-behaved optimized SQL (for example,
when extensive data processing needs to be
done to compute columns). Data modification
is also an issue because updatable views have
restrictions.
As a result of these constraints, the second
solution of writing post-query trigger is a
more common one. The biggest drawback to this
approach is performance. Within the trigger,
each query fires separately for every row,
causing excessive network traffic between the
client and server computers.
Illustration: Let's
take as an example a table data block with
2 additional queries in the post-query trigger.
In a query where 5 rows are being retrieved
into the form, a post-query trigger will fire
5 times (one for each row), and each trigger
performs the 2 queries. That is 3 fetches per
row, which result in 15 fetches for 5 rows
of data. No wonder most complex forms are slow
to fetch data. There are a lot of queries going
on there!
A
Viable Solution
With the release of Forms version 5, there
is another alternative that is much cleaner
and more efficient. This is the use of stored
procedure data block. Like its name implies,
instead of basing a data block from a single
table, the data block is now based on a stored
procedure in the database. The basic concept
involves writing a database procedure to do
the query (or queries), populating the result
in a PL/SQL table, and passing it to Forms
so it can populate the data block. To provide
update, delete, and insert capabilities, the
opposite happens. Forms pass a PL/SQL table
to an update, delete, or insert procedure in
the database. The procedure loops through all
the rows in the PL/SQL table and applies the
appropriate changes to the tables. Thus, a
stored procedure data block, which needs to
have the ability to query, update, insert,
and delete data, requires separate procedures
(all implemented in a package) to perform each
one of these functions. All this extra work
may seem rather tedious, but the following
benefits illustrate why stored procedure data
blocks are a better choice.
- Increased Security and Query Standards.
At their most extreme, the developer can
use stored procedure data blocks to create
a 'middle tier' or 'data access layer' by
writing sets of procedures to access and
manipulate the data. This approach limits
Forms data access to through these procedures
and eliminates the need of writing SQL in
Forms (with the exception of LOVs, which
still require SQL definition).
- Reduced Network Traffic. Since Forms
is just sending or receiving a PL/SQL table
from the database, network traffic is reduced.
There is no post-query trigger code to deal
with.
- Sharing Across Forms. The same stored
procedure may be shared across different
Form modules. This increases reusability
and improves application maintenance.
- Performance, Performance, Performance.
Since the entire query is performed in the
database, it may be easily optimized. Combine
this with the efficiency gained by having
only one 'round trip' data transfer between
the database and Forms, and overall performance
is greatly increased.
It is important to keep in mind that this
is only a single approach, which may or may
not be applicable for all programming needs.
But in situations where a data block will fetch
and modify information from multiple tables,
using a stored procedure data block may be
the easiest and most efficient solution.
Example
A form needs to be created to query and manipulate
(update, insert, and delete) employee data.
This data comes from two tables with a 1 to
1 relationship.
Employee table
Name Null? Type
Emp_id not null nnumber(10)
First_name not null varchar2(20)
Last_name not null varchar2(20)
Employee_confidential table
Name Null? Type
Emp_id not null number(10)
Ssn not null varchar2(9)
Salary not null number(10)
To create a procedure to perform the query,
a PL/SQL table needs to be defined. This is
the parameter that will get passed from the
database to Forms. Since it is easier to group
all these procedures into a package, we will
do so as follows.
Create or replace package employee_pkg is
type emp_rec is record
(emp_id employee.emp_id%type,
first_name employee.first_name%type,
last_name employee.last_name%type,
ssn employee_confidential.ssn%type,
salary employee_confidential.salary%type);
type emp_tbl is table of emp_rec index by binary_integer;
Notice that emp_rec
is nothing more than the columns which need
to be queried. These columns are what will
appear on the form. emp_tbl
is the PL/SQL table made up of emp_rec
records.
Now that the PL/SQL table type is defined,
creating the query procedure is a simple task.
Simply declare an emp_tbl
argument as IN OUT. This PL/SQL table will
be populated by the query and passed back to
the form. Add additional parameters as needed
to perform the query. In this particular case,
p_emp_id
is needed because the query looks for a specific
employee id.
procedure do_query(p_emp_table IN OUT emp_tbl,
p_emp_id IN employee.emp_id%type) is
cursor emp_cur (p_id employee.emp_id%type) is
select e.emp_id,
e.first_name,
e.last_name,
c.ssn,
c.salary
from employee e,
employee_confidential c
where e.emp_id = c.emp_id and
e.emp_id = p_id;
idx number := 1;
begin
for rec in emp_cur(p_emp_id) loop
-- populate a PL/SQL table with a record
p_emp_table(idx) := rec;
-- increment index
idx := idx + 1;
end loop;
end;
That is it for the query! do_query
procedure is called from Forms with the specified
emp_id.
The procedure then populates p_emp_table
PL/SQL table and passes it back to the form
for display.
There is another method of writing a query
procedure using cursor variables instead of
a PL/SQL table. Its implementation is relatively
straightforward and an example code is provided
in the complete listing below.
If the data block needs data manipulation
capability, then insert, update, and delete
procedures need to be created. The insert procedure
requires putting records into both employee
and employee_confidential
tables (remember that it's a 1 to 1 relationship).
When the forms 'Insert-Procedure' trigger is
fired after the user inserts a record, a PL/SQL
table is sent to the insert stored procedure
where the actual insert queries are issued.
procedure do_insert(p_emp_table IN OUT emp_tbl) is
numOfRows number;
begin
numOfRows := p_emp_table.count;
for idx in 1 .. numOfRows loop
insert into employee
(emp_id,
first_name,
last_name) values
(p_emp_table(idx).emp_id,
p_emp_table(idx).first_name,
p_emp_table(idx).last_name);
insert into employee_confidential
(emp_id,
ssn,
salary) values
(p_emp_table(idx).emp_id,
p_emp_table(idx).ssn,
p_emp_table(idx).salary);
end loop;
end ;
The procedure simply loops through all the
records in the PL/SQL and performs inserts
into the two tables. Code for update and delete
procedures are very similar to this insert
except update and delete statements would be
issued. One final note, if an update or delete
procedure is created, a lock procedure needs
to be created as well. This procedure is called
to lock a record in the database when the user
attempts to modify it. The logic is the same
as the update or delete procedure, which requires
looping through all the records in the PL/SQL
table and issue a select ... for update nowait;
statement for each affected table.
Once the stored procedures are successfully
compiled in Oracle, it's time to create a Form
module to access them. Simply open up the Data
Block Wizard and select Stored Procedure. The
Wizard will prompt for the stored procedures
one at a time (query, insert, update, delete,
and lock). When Forms locates the procedure,
it automatically retrieves the column definitions
from the PL/SQL table as well as any procedure
parameters. Here you may specify binding variables
for these parameters. Once the form is compiled,
it will automatically generate triggers depending
on what stored procedures were used. They are:
- Query-procedure
- Insert-procedure
- Update-procedure
- Delete-procedure
- Lock-procedure
Do not attempt to modify code in these triggers
because they are automatically generated every
time Form compiles. Modifications may be done
through the following block properties:
- Query Data Source Columns - lists the column
definition of the PL/SQL table
- Query Data Source Arguments - lists the
parameters to the query procedure
Once these properties are modified, recompile
the form, and changes should be reflected in
the data block triggers.
The foregoing may seem quite complicated at
first, but it is really straightforward after
you try it. In summary, the first step is to
write the stored procedures to access or manipulate
your data. The second step is to create a data
block that references these procedures. You
will see that once you design one stored procedure
block, all the rest are just slight variations.
So go ahead, play with it, have fun, and most
of all, good luck!
|
