What Do You Base Your Block On?
It is simple to build a block in your Oracle Developer Forms application based on a single table. The native Oracle Developer drivers access Oracle database tables without any extra effort or extensive coding on the developer’s side. This is one of the strengths of the product. However, you may want to display information from multiple tables in the block. Using POST-QUERY triggers to query lookup tables has traditionally satisfied this requirement. However, these triggers create unnecessary network traffic and may cause performance bottlenecks. When trying to performance-tune an application, one of the easiest techniques to improve query performance of a block is to remove POST-QUERY triggers from the block by using one of the techniques outlined in this section.
Once the information is queried into the form, you will often need to manipulate the data before updating it in the database. As long as you are only dealing with columns from a single table, it is relatively straightforward. Using Oracle8 and Developer Release 6.0, there are far more elegant solutions to these problems requiring less developer effort with better performance.
There are many different sources you can use for your blocks. Some use methods that have been available in earlier versions of Oracle Developer; others are new with R.6. The following are things you can base a block on:
· Nothing (a control block)
· A table
· A view (updateable view, view with INSTEAD OF triggers, or non-updateable view)
· A procedure (PL/SQL table type or REF CURSOR type)
· A FROM clause query
· Transactional triggers
This paper explains each of these and provides techniques or tips on how to implement them. All sample code is available from the authors' web sites mentioned at the end of this paper.
There are two categories to examine when determining what to block on. The first is the data source—where data is queried from. The second is the data target—where data in the form goes when the user issues a COMMIT in the form. There are block properties that manage both categories. For example, you set the property Query Data Source Type to the kind of object (such as a table), which you want Forms to use when querying. There is a corresponding property, DML Data Target Type, that specifies what kind of object (such as a procedure), which you want Forms to use when issuing an INSERT, UPDATE, or DELETE statement to the database. Both source and target can be set differently in some cases, based on your needs and on the restrictions of the property value combinations (as documented in the help system). This paper will focus on the data source but will also mention the data target in the implementation discussion.
Forms Query Text
Traditionally, basing a block on a table allows you to further modify the query by adding additional text to the Query Data Source Name, WHERE Clause, or ORDER BY Clause block-level properties. What is Oracle Forms doing when filling in these properties? It is building the following statement:
SELECT <all of the
database column items in the block>
FROM < Query Data Source Name property>
WHERE <WHERE Clause property>
ORDER BY <ORDER BY Clause
property>
You have a number of options for manipulating the string that Forms passes as a query to the database. For example, the WHERE Clause and ORDER BY Clause properties can be manipulated at runtime using the SET_BLOCK_PROPERTY built-in. This allows you to manipulate the block’s sorts and filters at runtime.
The ORDER BY Clause property can be used to change the sort order of records in the block and can also be manipulated at runtime.
Caution: When setting these properties, you are simply providing material that Forms will concatenate to form the SQL statement. Therefore, there is nothing to say that the table source must be a single table. For example, you can use “DEPT,EMP” as the value for the Query Data Source Name property and “DEPT.DEPTNO = EMP.DEPTNO” in the WHERE Clause property. This allows you to select columns from either the DEPT or EMP tables without the need for a view in the database. Using the properties in this way is not a recommended strategy because it is nonstandard and may confuse someone else trying to maintain the form. Also, in Form Builder release 6.0, there are alternate methods of having blocks return information from multiple tables simultaneously without resorting to such workarounds.
Whether you will allow users to change the Order By Clause property at runtime is a GUI design issue. Normally, runtime specification of the ORDER BY clause is not necessary. Most blocks should have this property set when the block is created, particularly if a large number of records must be retrieved. Keep in mind that the Order By Clause block property alters the query statement sent to the database. By adding an ORDER BY clause to the query, there may be a significant performance impact to consider.
Basing a Block on Nothing (a Control Block)
Blocks may be created without a data source. When a block is not being populated from a database source, it is called a control block. Control blocks are used for items that are not explicitly associated with database objects such as buttons, total items, and locator query criteria objects and items for displaying messages. Control blocks can also be used to display values from items selected elsewhere. For example, if you are maintaining a list of selected items and you do not want to store a list of selected items in the database, they can be stored locally and populated in a control block.
Control blocks are almost always single record blocks. However, if you are displaying a list of items as mentioned above, your control block will display a number of records and may have a scroll bar. Any type of item may be contained in a control block.
Utilizing standard GUI techniques, almost every form will have at least two control blocks. Each form has a “STUFF” block holding the authors’ template (available on the web sites mentioned at the end of this paper) null item and other template items. Most forms also have a locator control block to hold locator query criteria items.
Implementing a Block Based on Nothing
If you want to create a new control block, specify that you will be building the block manually when you click on the Create button in the Object Navigator. To create a control block from an existing block, set the Query Data Source Type property in the Database section of the block Property Palette to “None”. By default, when a block is created, the Query Data Source Type property is set to “table” and the Query Data Source Name property is empty. This setting will also work for a control block. For the items in the control block, the Column Name property should be left as “null”, although this will not create any problems if it is set to something else.
If you have many non-database items in your form housed in a control block, you might want to create multiple control blocks to organize the items. In very complex forms, you might have an additional control block for every base table block to house the control items for that block.
Basing a Block on a Table
Basing a block on a table involves associating the block with a specific table in the database and associating some number of items in the block with columns in the table. Additional non-base table items in the block are populated with POST-QUERY triggers. This is traditionally the most common mechanism for attaching a block to data stored in the database. Unfortunately, this method has some serious drawbacks. First, most blocks in production forms require the display of columns not in the base table. Therefore, most base table blocks have POST-QUERY triggers to retrieve additional information from the database.
This is a terrible design method. Recall that a POST-QUERY trigger executes every time a row is fetched into the block. Therefore, if a POST-QUERY trigger contains a cursor to retrieve information from the database, retrieving 200 records into this block entails 200 additional and unnecessary trips to the database. This same situation can be handled far more efficiently through a view discussed later in this paper.
Tip: No POST-QUERY trigger should ever contain a cursor.
The only time that a block should be based on a table is when there are no columns that need to be populated by triggers that access the database. Because of the POST-QUERY trigger restriction, basing a block on a table should be a rare occurrence (less than 10% of your forms).
Implementing a Block Based on a Table
When basing a block on a table, you should usually use the Data Block Wizard. This wizard is re-entrant so that if a column is omitted, rather than having to manually add it to the block, you can use the Data Block Wizard to add it. Just remember that, if you also rerun the Layout Wizard to add the item on the canvas, the wizard will reposition all the items in the block regardless of where you have moved them.
Caution: When
running the Data Block Wizard, the Column
Name property for each database item is filled in. If you change the name
of the item, it will still be pointing to the same column. If the Column Name property is “NULL”, the item
will use the Item Name as the name of the database column.
Although the Column Name property was
null by default in the Data Block Wizard of Forms R.5, it is not null by
default in Developer Forms R.6.0. Leaving the Column Name property set to “NULL” is a better development
practice. This way, if the name of a database column is changed, you can change
the name of the item in the block accordingly (and update the column list in
the Query Data Source Columns property).
You do not need to change the Column Name
property. Therefore, all the authors’ item SmartClasses in the template
have this property set to “NULL”.
When using the Data Block Wizard to implement blocks based on tables, the wizard creates a block with the Query Data Source Type property set to “Table” and the Query Data Source Name property set to the name of the table. For each item, the Database Item property is set to “Yes” and both the Name and Column Name properties of the item are set to the database column name.
On a block that has been based on a table, you can navigate to that block using GO_BLOCK or GO_ITEM built-ins to any item in the block (that has the Keyboard Navigable property set to “Yes”). Onscreen, you can navigate to a block either by tabbing to any item in the block or mouse-clicking on any mouse-navigable item in the block. Buttons are mouse-navigable if the Mouse Navigate property is set to “Yes”. Text items are always mouse-navigable so that there is no Mouse Navigate property on the Property Palette for these item types. Display items are never navigable either by mouse or keyboard so neither property (Mouse Navigate or Keyboard Navigable) appears on the Property Palette.
Querying data into a block is handled via the EXECUTE_QUERY built-in. This built-in operates only on the current block and does not accept a block name as a parameter. Inserting records into a block is handled by navigating to the first open row and entering values. You can also create a blank row using the CREATE_RECORD built-in wherever the cursor is, leaving other records in the block intact.
Deleting the current record from the block is accomplished with the DELETE_RECORD built-in. Modification to a block item can be handled by navigating to a specific record and changing the value of the item. To permanently apply your changes to the database, a COMMIT_FORM built-in is required.
Note: Inserts, updates, and deletes to records are not automatically posted to the database. If you make changes to records in a base table block and then query the database (even in the same session), those changes will not be reflected in the database unless you explicitly execute a POST built-in.
Basing a Block on a View
A view does not contain data. It is just a text string storing a SELECT statement. When a DML command is executed with a reference to the view, the code is parsed in such a way as to take the view into account. You can base a block on a view in the same way you base a block on a table. The view can represent a master-detail relationship, a code description lookup, or any other type of SELECT statement that would make sense for your application. For example, here is the definition for a view based on the EMP table that joins the table to itself so it can show the manager’s name and job title.
CREATE OR REPLACE VIEW emp_v
AS
SELECT emp.empno,
emp.ename,
emp.job,
emp.mgr,
emp.hiredate,
emp.sal,
emp.comm,
emp.deptno,
mgr.ename mgr_name,
mgr.job mgr_job
FROM
emp emp, emp mgr
WHERE
emp.mgr = mgr.empno (+)
This view uses an outer join to retrieve all rows of the EMP table even if they have no value in the MGR column. If you base the EMP block on this view, you will not need to use a POST-QUERY trigger to query the manager information for each row; the manager information will be retrieved as part of the same row in the view. Using a view in this way can save a significant amount of network traffic. This example assumes that you are not updating the manager information, although this would be possible by using INSTEAD OF triggers (described later) or some other mechanism.
You can use views in different ways—as updateable objects or as non-updateable objects. You can also take advantage of an Oracle8 feature that allows you to write database (INSTEAD OF) triggers for the view. Other than the extra step of writing trigger code, there is no difference in syntax for creating views that are in the categories of updateable, non-updateable, or INSTEAD OF. However, the different uses require different design considerations and Forms techniques and each will be discussed separately.
Tip: If you need to sort the records in a block on a non-base table item that does not appear in the table or view that the block is based on, you can create a function in the database that loads the non-base table item and add that function’s name to the ORDER BY Clause property on the block you wish to sort.
Updateable Views
An updateable view is a view created where one or more columns support DML commands. There is nothing special required syntactically to make a view updateable. The Oracle database decides for you which columns are updateable. To find out which columns are updateable, you can query from the system view USER_UPDATABLE_COLUMNS. The documentation on updateable views (in Chapter 3 of the Oracle8 Application Developer’s Guide - Fundamentals manual) includes a very precise technical discussion of key-preserved (or ROW ID preserved) columns. In short, if your view is essentially querying from a single table with some extra display columns, then you can perform INSERT, UPDATE, and DELETE operations to columns in the base table as shown in the following code:
CREATE OR REPLACE VIEW emp_v
AS
SELECT e.empno,
e.ename,
e.deptno,
d.dname
FROM
emp e, dept d
WHERE
e.deptno = d.deptno
In this case, you can insert into this view as if it were the EMP table. However, you cannot insert into the DNAME column. Another way to think about this is that an updateable view is a view that contains a join between master and detail tables. You can update columns that are derived from the detail table (that contains the key-preserved columns) of this view. The view has to be constructed so that the foreign key column comes from the detail table (not the master table). In this example, the detail table is the EMP table. The master table is the DEPT table and the DEPTNO foreign key column comes from the EMP table. Therefore, you are able to update all columns from the detail table (EMP) but not from the master table (DEPT).
When creating an updateable view, it will always have the same number of rows as its core (detail) table unless you are filtering out the records using a WHERE clause. The previous code example could be modified to include:
AND e.deptno < 30
You can still INSERT, UPDATE, or DELETE records in the EMP table using the EMP_V view even though the number of records in the view is potentially less than the number of records in the table.
One very useful technique using updateable views is to embed a function in a view. To do this, you must first place the function into a database package. Next, in the package specification, use a PRAGMA RESTRICT_REFERENCES directive. This function can then be embedded in the updateable view.
The example above could also be rewritten by creating a function called EMPNO_TO_DNAME and storing it in a package called K_EMP. The view definition would be changed as shown below:
CREATE OR REPLACE VIEW emp_v
AS
SELECT empno,
ename,
deptno,
k_emp.empno_to_dname(empno)
FROM emp
This view now operates in the same way as if you had a base table block based on EMP with a POST-QUERY trigger to look up the department name. However, since you are basing your block on the updateable view, the number of network round trips will be reduced. You can still update the columns from the EMP table but you cannot update the column based on the function. Usually, joining the tables will be more efficient than performing lookups using embedded functions. The reason both methods have been presented is to point out how every block with a POST-QUERY trigger (that contains a lookup cursor) can always be restructured as a block based on an updateable view. You can accomplish the lookup by rewriting the POST-QUERY trigger as a function that you place in the SELECT clause of the view. Using functions for lookups instead of POST-QUERY triggers requires no extra operations by the database and will require much less network traffic because all queries are performed on the database server before query results from the view are passed to the form. This demonstrates why POST-QUERY triggers with cursors should never be used.
Caution: When creating an updateable view and joining to a table based on an optional relationship, be sure to use an outer join to ensure that all records are retrieved.
Many developers assert that updateable views are more complex and actually slower than tables with POST-QUERY triggers. This is indeed possible if your updateable view is a multitable join. Even though the overall efficiency of bringing all of the records back is improved, the perceived performance by users may actually degrade. If this problem exists, the solution is not to go back to using a POST-QUERY trigger on a block. Instead, the updateable view should be changed. This can be done by using the same strategy to build the updateable view as was used to build the base table block and POST-QUERY triggers, namely rewriting the triggers as database functions. When this is done, then you can build your updateable view to select all columns from the base table plus any additional formula columns. This mechanism will always be faster than a table-based block with POST-QUERY triggers. The only difference between the two methods is that rather than performing network round trips for every record, instead, the function calls are all happening within the core database query. This strategy can dramatically decrease the number of network roundtrips required by an application.
You should base most of your blocks on updateable views. Any time you have a table with a POST-QUERY trigger requiring database access, it should always be replaced by an updateable view. Not only will network traffic decrease (thereby improving performance) but you will also achieve better encapsulation of the code, which also improves the chances for reuse. The same view built in one context can be reused in reports or other system forms. Because of the reuse possibility, always include all columns from the core table in the updateable view.
Views with INSTEAD OF Triggers
A new feature in Oracle8 allows you to support INSERT, UPDATE, and DELETE operations on any view. This feature enables you to create a view on multiple tables and allow DML operations to be defined in any way desired. The benefit is the elimination of the limitations imposed by updateable views. The disadvantage is the need to write all of the programming logic for the view.
Although you cannot perform DML statements on a view that is not updateable, in Oracle8 (8.0 and 8i) you can write an INSTEAD OF trigger that will fire when DML is attempted on the view. The trigger code will take over when the DML is issued and can manipulate any of the base tables of the view.
Note: The term DML (Data Manipulation Language) technically refers to all operations that you can apply to a data set. It is distinguished from DDL (Data Definition Language) that includes statements to alter and create data structures. While DML operations in SQL include the INSERT, UPDATE, DELETE, and SELECT verbs, many sources use the term DML to mean only the INSERT, UPDATE, and DELETE verbs. You will find this term used this way in block properties such as the DML Target Type. This paper uses the Oracle usage.
The following is an example of an INSTEAD OF trigger (the suffix identifies it as occurring instead of an Insert, Delete, or Update). For simplicity, this code does not insert or update the manager information in the EMP table, but logic for those DML statements could easily be added.
CREATE OR REPLACE TRIGGER emp_v_iidu
INSTEAD OF INSERT OR UPDATE OR DELETE
ON EMP_V
FOR EACH ROW
DECLARE
v_message VARCHAR2(100);
BEGIN
IF INSERTING
THEN
v_message := 'Inserting an EMP
record using EMP_V_BIUD';
-- The manager record must already
exist or the INSERT will fail
INSERT INTO emp (
empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno)
VALUES (
:new.empno,
:new.ename,
:new.job,
:new.mgr,
:new.hiredate,
:new.sal,
:new.comm,
:new.deptno);
ELSIF UPDATING
THEN
v_message := 'Updating an EMP
record using EMP_V_BIUD';
-- Assumes the manager name and job
are not updateable
-- Also assumes the primary key
value of EMP did not change
UPDATE emp
SET ename = :new.ename,
job = :new.job,
mgr = :new.mgr,
hiredate = :new.hiredate,
sal = :new.sal,
comm = :new.comm,
deptno = :new.deptno
WHERE empno = :old.empno;
ELSE
-- DELETING
v_message := 'Deleting an EMP
record using EMP_V_BIUD';
--
DELETE FROM emp
WHERE empno = :old.empno;
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error(-20001,
'Error: ' || v_message ||
' - ' || SQLERRM);
END;
/
The trigger defines INSERT, UPDATE, and DELETE statements for each operation that you allow on the view. It uses the :NEW and :OLD values available in row triggers to build DML statements for the EMP table.
Caution: INSTEAD OF trigger views should only be built by highly skilled developers. It can be very difficult to debug a complex application if a block based on an INSTEAD OF trigger view contains an error. Also, since you are not using an updateable view, the automatic check on the correct coding of the view is not available so you may believe that the view is correct when, in fact, it is not.
An INSTEAD OF trigger view can be used any time there is a complex block based on multiple tables and you want update capability but an updateable view is not sufficient. This is a very powerful technique that will not be required in most applications. If you need many INSTEAD OF views to support your applications, you should probably examine your database design and GUI standards carefully for potential problems.
Non-Updateable Views
It is common to need to view information from the database without the need for updates. You are essentially building an “onscreen report.” As long as you do not need to update the information, basing your block on a view is the best strategy. The view can be as complex as necessary including joins, embedded functions, and so forth. To create a screen report, your block should, in fact, be based on a view. The block should not be based on a table with a POST-QUERY trigger calculating your additional block items since this causes unnecessary network traffic. Having the structure of the block encapsulated in a view also promotes reusability of the structure in other forms or printed reports.
An example of a common situation where you might base blocks on non-updateable views is a query on customer activity. Assume you have three tables where customer activity is stored, structured as follows:
· Contact Structure To record telephone calls and human interactions
· Letter Structure To store details of all correspondence activity
· Financial Structure To track monetary activity
You can create a view that is a union of all three structures sorted by date in order to display a chronological record of all activity associated with a particular customer.
Implementing a Block Based on a View
You build the block as if it were based on a table. The Data Block Wizard includes the columns from the view in the same way that it would for columns from a table. The only difference is the database restriction that you cannot perform DML on non-updateable views. If you need to perform INSERT, UPDATE, and DELETE operations on the tables in the view, you can use one of two strategies mentioned above: an updateable view or an Oracle8 INSTEAD OF trigger. Otherwise, you will be using the view as a non-updateable view.
For an updateable view, the only change you need to make in the form (after creating a block on the view in the same way as if it was a table) is to set the Query Only property to “Yes” for the items from the master (key-preserved) table. This means that these columns will not participate in DML but they will participate in queries. There is nothing special you need to set when defining a block for a non-updateable view or a view with INSTEAD OF triggers.
Caution: If your view restricts the rows available using the value of a column (such as “WHERE EMP.DEPTNO > 30”), an update of that column value (such as “DEPTNO = 20”) could make the row unavailable to the view. This effect, not unique to Forms work, may be undesirable so you may want to write application code to restrict the values for columns that restrict rows in the view.
Properties That the Data Block Wizard Sets
It is useful to know which item and block properties the Data Block Wizard sets specially for tables and views so you know what to change if you need to modify the block. Basing a block on a table or view using the Data Block Wizard sets the block property Query Data Source Name to the name of the table or view. The Query Data Source Type property remains at its default of “Table”. The Query Data Source Columns property is set to the names of the columns in the table or view. The following are Figure 1 shows the column names in the Query Data Source Columns property.
This column list identifies the column names and datatypes that are returned from a query to the table or view in the database. While this list is useful, it is not critical to the block mechanism if the block is based on a table or view. It is critical for blocks based on procedures, as discussed later.
The Data Block Wizard also fills the item property Column Name with the name of the column that supplies the data for the item. This is also optional in most cases because the Form Builder will use the name of the item as the name of the column when constructing the query or DML statement. For tables that use object columns (made of a number of elements) the syntax for the SQL statement uses dot notation to identify a column member. For example, the FIRST_NAME member of the PERSON object inside the EMPLOYEE table would be fully identified as EMPLOYEE.PERSON.FIRST_NAME in a SQL statement. Since this dot notation would not work in the block, the wizard constructs an item named PERSON_FIRST_NAME and place the value “PERSON.FIRST_NAME” in the Column Name property of that item.
Another technique you can use in some circumstances is to base the source block on a view and base the DML target on one of the tables in that view. This allows you to directly update records in that single table. To accomplish this, set the Query Only property to “Yes” for all items in the block that are not associated with the table.
Figure 1: Query Data Source Columns property
Basing a Block on a Procedure
Blocks can be based on PL/SQL procedures that return a REF CURSOR or PL/SQL table variable that contains one or many rows. You can also issue DML from the block to other procedures that will manipulate the records in the table based upon parameters that the block passes to the procedures. Basing a block on a procedure allows you to change everything about the query including the source or target tables. You can also use PL/SQL conditional and loop structures to manipulate values before returning them to the block. When the block issues DML statements, you can intercept them and use the same kind of logic to route the values the way you want. This gives you an enormous amount of flexibility and power beyond what is possible with SQL alone. It is useful to examine details on the two types of procedures that you can use to base a block on: a PL/SQL Table and a REF CURSOR.
PL/SQL Table Procedure
A block can be based on a procedure that returns a PL/SQL table. This is particularly useful when you want a block to display information that would otherwise require a very complex SQL query resulting in terrible performance.
As an example, assume you are using the data model shown in Figure 2. You want
the block to have the employee names as the columns, one row for each calendar
day, and each item in the block containing the total sales for that employee
for that day. This requires a block to function as a crosstab report.
Figure 2: Data model example
Trying to write a SQL query to support such a block would be a challenge to any developer. Once written, this query could take a long time to execute. One solution to this problem is to make the block a non-base table block with a complex client-side PL/SQL routine to populate the block one item at a time. A better approach is to make a PL/SQL procedure with a table as an IN/OUT parameter. This method is both relatively easy to use and easy to optimize performance.
REF CURSOR Procedure
A cursor variable (declared as a REF CURSOR datatype) points to a specific row in a query result set. When you want to perform a query, you declare the REF CURSOR and create a function that retrieves the rows into the cursor variable. A REF CURSOR is a pointer to server-side cursor variables similar to pointers in C that are addresses to a memory location. The stored procedure returns a reference to an open cursor populated by the SELECT statement that is used as the data source for the block.
You can have one or more cursors stored in a PL/SQL procedure. You can then programmatically select which cursor is used to populate the block. The cursors must be pre-defined before runtime but you can modify the cursor using bind variables. You cannot define a REF CURSOR using DBSMS_SQL so that the degree of flexibility is somewhat limited. A further limitation is that the REF CURSOR can only be a data source and not a data target, meaning that you cannot pass information back to the procedure through the REF CURSOR. For this reason, the effectiveness of REF CURSOR is limited. REF CURSORs are appropriately used with a block that may need to be based on one of several data sources, for example, in a multiple database environment, with the same table in different databases, or between development and test instances. You can programmatically switch between the two tables and quickly scan for differences. Everything that can be done using a REF CURSOR can also be done with greater flexibility using a FROM clause query as discussed later in this section. ’
The question remains of when to use a REF CURSOR instead of a FROM clause query. The answer is to use a REF CURSOR whenever you can. As you move from blocks based on tables or views to procedures that return REF CURSORs to blocks based on FROM clause queries, you increase flexibility. When a block is based on a table or view, you can modify the WHERE clause and the ORDER BY clause at runtime by setting block properties. With REF CURSORs, you can select from completely different queries and modify those queries using bind variables. With a FROM clause query, you can change the SELECT statement upon which the block is based at runtime. You should base your block on REF CURSORs whenever you only need the level of flexibility that REF CURSORs offer.
Implementing a Block Based on a Procedure
You can base a block on either type of procedure. The technique described next uses a cursor variable to retrieve rows into the block. The section called “Using a PL/SQL Table” describes the considerations for basing a block on a PL/SQL table. This technique shows an example of the join between the DEPT and the EMP tables. The data requirement is simple enough that it could be filled with one of the other alternatives for block data sources, but is given so the requirement does not distract from the focus on the technique. There are two main focus areas of work: the procedure package and the form.
The Procedure Package
The cleanest way to create and manage the procedures you need for block data is with a package. You can name the package similarly to the table and it will implement an object-oriented-like set of methods used to access the table. The first step to creating the package is to declare type variables in the package specification as follows. (The comment header has been deleted to save space.)
PACKAGE empdept_maint
AS
TYPE emp_t IS RECORD(
empno emp.empno%TYPE,
job emp.job%TYPE,
hiredate emp.hiredate%TYPE,
deptno dept.deptno%TYPE,
dname dept.dname%TYPE);
-- For SELECT
TYPE rc_empdept IS REF CURSOR
RETURN emp_t;
-- For the DML - a table of records
TYPE t_empdept
IS TABLE OF emp_t
INDEX BY BINARY_INTEGER;
--
PROCEDURE slct(
p_empqry IN OUT rc_empdept);
--
PROCEDURE ins(
p_emprec IN OUT t_empdept);
--
PROCEDURE upd(
p_emprec IN OUT t_empdept);
--
PROCEDURE del(
p_emprec IN OUT t_empdept);
--
PROCEDURE lck(
p_emprec IN OUT t_empdept);
--
END;
This specification declares the types based on a record variable that represents the columns in the cursor, the ref cursor itself, and a PL/SQL table of records. The REF CURSOR is used as the return value for the SLCT procedure that queries the table. The PL/SQL table of records is used to pass values in and out of the DML and lock procedures.
Note: The DML and lock procedures must contain at least one table-of-records parameter because the form communicates to the procedures using arrays (tables of records).
The Package Body
The package body contains a procedure for each DML statement as well as one for the SELECT and LOCK statements. The following SLCT procedure used to query the records is excerpted from the package body.
PROCEDURE slct(
p_empqry IN OUT rc_empdept)
IS
BEGIN
-- This can be more complex and
include conditional
-- logic to select from one source
or another.
OPEN p_empqry
FOR
SELECT emp.empno,
emp.job,
emp.hiredate,
emp.deptno,
dept.dname
FROM emp emp, dept dept
WHERE emp.deptno = dept.deptno;
END;
You can also perform DML and lock statements for the form’s records using other procedures in this package. The following shows an additional excerpt of the package. The INS procedure uses a loop through the record variable to check the records to be inserted. It checks that the department record that the employee record refers to is in the DEPT table (using a function not displayed here). It then inserts the EMP record using the values passed in from the form.
PROCEDURE ins(
p_emprec IN OUT t_empdept)
IS
v_message VARCHAR2(100);
BEGIN
FOR v_ct IN 1 .. p_emprec.count
LOOP
-- If the dept doesn't exist,
insert it
IF p_emprec(v_ct).deptno IS NOT
NULL
THEN
IF
dept_not_exists(p_emprec(v_ct).deptno)
THEN
v_message:= 'Insert of
DEPT';
INSERT INTO dept(
deptno,
dname)
VALUES
(p_emprec(v_ct).deptno,
p_emprec(v_ct).dname);
-- Exception handling goes
here.
END IF;
--
v_message := 'Insert of EMP';
INSERT INTO emp(
empno,
job,
hiredate,
deptno)
VALUES (
p_emprec(v_ct).empno,
p_emprec(v_ct).job,
p_emprec(v_ct).hiredate,
p_emprec(v_ct).deptno);
-- add an exception handler
here
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error(-20002,
'Error: Inserting record ' ||
'using EMPDEPT_MAINT.INS.
Inform Technical Support. '|| v_message);
END;
Other DML statements would follow the same pattern of looping through the table of records and performing the DML statement using the values in a row of the table of records. The following is the LCK procedure that locks all records in the table of records.
PROCEDURE lck(
p_emprec IN OUT t_empdept)
IS
v_empno emp.empno%TYPE;
BEGIN
FOR v_ct IN 1 .. p_emprec.count
LOOP
SELECT empno
INTO v_empno
FROM emp
WHERE empno = p_emprec(v_ct).empno
FOR UPDATE;
END LOOP;
END;
Forms Work
Once you have written and installed the PL/SQL package to support the block, you are ready to create a block based on the packaged procedures. In the Data Block Wizard, you select Stored Procedure in the Type page. On the Query page, enter the name of the REF CURSOR procedure (such as EMPDEPT_MAINT.SLCT). The columns in that REF CURSOR will appear after you click the Refresh button, as Figure 3 shows.
Figure 3: Selecting columns from a query
procedure
You set the Insert, Update, Delete, and Lock pages that follow the Query page in a similar way using the names of the corresponding procedures in your package. The Data Block Wizard will assign the values for the block properties as shown in Table 1.
|
Property |
Notes and “Values” |
|
Query Data Source Type |
“Procedure” |
|
Query Data Source Name |
The name of the SELECT procedure (prefaced by the package name) |
|
Query Data Source Columns |
The list of columns in the SELECT procedure’s REF CURSOR |
|
Query Data Source Arguments |
The name of the ref cursor declared as the main parameter of the SELECT procedure |
|
DML Data Target Type |
“Procedure” |
|
Insert Procedure Name |
The name of the INSERT procedure |
|
Insert Procedure Result Set Columns |
The list of columns from the table of records used as a parameter to the INSERT procedure |
|
Insert Procedure Arguments |
The name of the table-of-records parameter used for the INSERT procedure. |
|
Update Procedure Name, |
The same type of contents as the corresponding properties for INSERT |
|
Delete Procedure Name, |
The same type of contents as the corresponding properties for INSERT |
|
Lock Procedure Name, |
The same type of contents as the corresponding properties for INSERT |
Table 1: Block Properties Assigned by the Data Block Wizard
In addition, the Layout Wizard will create items based on the columns in the query procedure’s REF CURSOR variable. When you compile the form, the compiler will create block-level procedures called INSERT-PROCEDURE, UPDATE-PROCEDURE, DELETE-PROCEDURE, and LOCK-PROCEDURE. These contain code to call the procedures you associated with the block in the Data Block Wizard. The following is sample code from an INSERT-PROCEDURE trigger:
DECLARE
bk_data EMPDEPT_MAINT.T_EMPDEPT;
BEGIN
PLSQL_TABLE.POPULATE_TABLE(bk_data, 'EMP', PLSQL_TABLE.INSERT_RECORDS);
EMPDEPT_MAINT.INS(bk_data);
END;
These triggers are maintained by the Forms compiler. If you make a change to the trigger, it will be lost the next time the form compiles. This is why you do not need to worry about formatting this code even though Forms will not generate it according to your code formatting standards.
Tip: Always use the Data Block Wizard to populate the block properties when working with procedures for a block. The wizard will set the properties correctly based on the inputs you provide in the wizard’s pages. You can try to set the properties manually, but it is possible that you or the Form compiler will miss something and you will get unexpected results. This can be avoided if you use the Data Block Wizard.
Queries
The Query-By-Example feature allows the user to place the form into Enter Query mode, enter query conditions in the items, and execute the query to retrieve rows from the database. When you base a block on a table, Forms creates a SELECT statement from the items in the block and constructs a WHERE clause based on query conditions that the user enters.
When you base a block on a procedure, Forms Runtime will not create the normal SELECT statement so query conditions the user enters in Enter Query mode will have no effect. However, you can write code to emulate some of the functionality of the Query-By-Example feature. The code requires a change in the SLCT procedure and a change in the form.
If you want to use additional parameters for the DML and LOCK procedures, you can follow the same steps for those procedures. When you are considering what query criteria to allow the user to use, be careful that the SQL statements that use those criteria are as efficient as possible. Otherwise, you can experience a degradation in performance over what you would see if the block was based only on a table.
Changing the SLCT Procedure
You need to pass parameters to the procedure that you can work into the query’s WHERE clause. Consider that you want to allow the user to query by entering the first few characters of the JOB column. You would create parameters for the job value as shown in the following procedure declaration:
PROCEDURE slct(
p_empqry IN OUT rc_empdept,
p_job IN emp.job%TYPE);
You also need to add the parameter to the WHERE clause of the query in the SLCT procedure body as follows:
AND job LIKE p_job || '%';
This code will retrieve in the tables if the parameter value is in the first part of the job title value. You can construct other scenarios to fit the queries that you decide to support. You can also pass additional parameters and work them into the query in the same way.
Note: If you change the procedure while the form is open, you may have to reconnect to the database or close and reopen Form Builder to be able to see the changes.
Changing the Form
As before, you work in the Data Block Wizard. Select the block in the Object Navigator and choose Tools®Data Block Wizard from the menu. On the Query tab page (Figure 4) click the Refresh button and the new parameter will appear.
Figure 4. Block Wizard Query tab
The new parameter will pop up in the arguments list. You have to supply a value for the argument in the Value column. In this example, the value is the value that the user has filled into the JOB item, so you reference that value using the syntax: :BLOCK.ITEM (or :EMP.JOB in this example). This is the only step required in the form. Now, when the user runs the form, any value entered into the JOB item will be matched with the wildcard in the procedure’s query.
Caution: If you receive an ORA-03114 error when accessing a procedure in an Oracle8i NT server from an Oracle Developer release 6 form, you should check with Technical Support for the availability of a patch to Oracle8 bug number 939287.
Using a PL/SQL Table
The first step in implementing a block based on a PL/SQL table is to create a package to hold the required procedure (as with the REF CURSOR). Then declare a record type and a PL/SQL table type. In the package spec, you may want to declare one or more variables that can be used to pass information to the PL/SQL procedure. Properties must be set the same way as for the REF CURSOR (as listed in Table 4-1), except, instead of using the REF CURSOR’s columns and parameter names, you use the PL/SQL table’s columns and parameter names.
As with the REF CURSOR, you should use the Data Block Wizard to populate this since everything must be typed correctly or the block will not populate. Then, in the code to populate the block, populate the package variables with an EXECUTE_QUERY command on the block as usual.
When you use a PL/SQL table as the data source, you use the same procedures for INSERT, UPDATE, and DELETE as you do for the REF CURSOR. The additional DML procedures are a very powerful feature but they may be rarely used.
Note: The package for the PL/SQL table can reside either in the form (in a program unit or attached library) or in the server. Blocks built based on PL/SQL tables are usually placed client-side during development and then moved to the server for deployment, if appropriate. However, since you can base a block on a PL/SQL table that is local to the form, you can programmatically load that PL/SQL table from any source (database or not). Thus you can use Forms’ built-in query mechanism to load a block based on data that you construct programmatically.
The only tricky part of this method is creating in the PL/SQL table of records. Prior to using this method, you should build a trivial procedure that returns a small PL/SQL table to familiarize yourself with the necessary steps.
Basing a Block on a FROM Clause Query
You can also base a block on a query statement, called a FROM clause query. This is useful if you want to test a SELECT statement for a block or if you cannot create views easily because of database privilege restrictions or company policy. It is particularly useful for CONNECT BY queries when you need to change the START WITH clause at runtime. The effect of this technique is to create an unnamed query-only view in the form. Any SELECT statement that you can create is suitable source material for a block. The items you create in the block are based on the columns returned by the SELECT statement. Using a FROM clause query allows you to completely change the SQL statement upon which a block is based at runtime.
This feature implements the ability of SQL in Oracle7 and Oracle8 to include a SELECT statement in the FROM clause. For example, the following shows how a SELECT statement can be embedded in the FROM table list:
SELECT e.ename, d.dname, s.salary, s.start_date
FROM emp e,
dept d,
(SELECT salary, start_date,
empno
FROM emp_sal
WHERE saldate > SYSDATE - 90) s
WHERE e.deptno = d.deptno
AND e.empno = s.empno
This query in the FROM clause acts just as a view would—as a source for data and for joining with other tables.
FROM clause query blocks only allow queries. You cannot UPDATE, INSERT, or DELETE in them. Frequently, you can set a specific table as the DML target, depending upon the FROM clause query. Anything that can be done with a REF CURSOR can be done using FROM clause queries.
There is a performance impact to using FROM clause queries that REF CURSORs are not subject to. A FROM clause query is implemented as a SELECT from SELECT statement. Rather than using a table name for the SELECT statement, the query that supports populating the block is of the form:
SELECT <all columns in block>
FROM <Query Data Source Name property>
The Query Data Source Name property contains the SELECT statement used as the data source. Using FROM clause queries, you can easily create an application allowing you to view the contents of any columns in any table. If you are particularly ambitious, this capability could be used to build a rudimentary ad hoc query tool. With the exception of query-only blocks based on recursive tables, FROM clause query blocks are rarely used.
As mentioned earlier, FROM clause queries are somewhat more flexible. However, because they are implemented as “SELECT FROM (SELECT …)” queries, they may take longer to execute. FROM clause queries should only be used when the application requires this level of flexibility and cannot be supported by basing the block on a table, view, or procedure that returns a REF CURSOR.
Implementing a Block Based on a FROM Clause
Creating a block from a SELECT statement is just a matter of changing a few properties from the usual values. There are two ways to do this. The hard way, which you will have to use in some cases, is to use the steps shown next.
The Hard Method
Although this method contains more steps and is more time consuming, you may need to use it if there is no table or view that you can use as a model for the block. The steps for the hard method follow.
1. Create the block manually and change its name to describe its purpose.
2.
Change the block’s Query Data Source Type
property to “FROM clause query”. This value is one of the allowed values
for this property as the following shows.
3. Change the block’s Query Data Source Name property to the SELECT statement. You can optionally fill in the Query Data Source Columns with the names of the columns in the SELECT statement.
4. Create items with names based on the columns in the SELECT statement.
5. Flag an appropriate item as the primary key by changing the item’s Primary Key property to “Yes”.
6. Set the item properties for Prompt, Data Type, Maximum Length, Canvas, Required, and other properties to appropriate values.
The Easy Method
You can save much work in the layout and property-setting steps by creating a block based on a table using the Data Block Wizard for step 1. You would then perform steps 2 and 3, skip step 4, and clean up the appropriate properties and names in steps 5 and 6. This is much faster and you will be more likely to correctly set values for necessary properties. Remember that the Data Block Wizard adds a value to the Column Name property. Therefore, if you name a column in the SELECT statement differently from the column in the table you based the block on, you will have to change the Column Name property as well.
How Does the Query Work?
Although there are many more manual steps to complete this method, you have complete flexibility in constructing the SELECT statement that the form will use. When the form queries the database, it will construct a SQL statement with the following format:
SELECT item1_column_name,
item2_column_name, …
FROM (SELECT statement in the Query Data Source Name property)
WHERE (WHERE Clause property)
ORDER BY (ORDER BY Clause property)
How Does DML Work?
There is a block property DML Target Name in the Advanced Database section that allows you to specify a data target for DML separately from the query source. If you leave this property blank, the form will use the name in the Query Data Source Name as the DML target. In the case of an updateable view or INSTEAD OF trigger view, this default behavior works because the query source is an actual object that can have DML issued to it.
In the case of a FROM clause query (sometimes misleadingly called a Sub-Query in the help system), the data source is a SELECT statement and issuing DML to a SELECT statement does not make sense. Therefore, you get an error message if you use a FROM clause query and do fill in the DML Target Name property. If you want to use DML on a block with a FROM clause query, you can specify a table or view name in the DML Target Name and turn off the DML for columns that the SELECT statement constructs. In the example, you would make the following changes in the form:
· Set the DML Data Target Name as “EMP”. The DML Target Type is left as the default “Table”.
· Set the Query Only property of the HIRE_TYPE item to “Yes” to indicate that this item participates only in the queries but not in the DML.
Programmatic Control
You can use the SET_BLOCK_PROPERTY built-in to change the DML target and the query source for the block programmatically as follows:
SET_BLOCK_PROPERTY('block_name',
QUERY_DATA_SOURCE_NAME,
'name_of_source');
In this example, block_name is the name of your block and name_of_source is the value (table or view name or SELECT statement) for the Query Data Source Name property. This is documented to work only if the value of the Data Query Source Type property is not “Transactional Triggers”.
You can change the DML target using the same built-in with the same format but with the DML_TARGET_NAME property. This is documented to work only if the DML Data Target Type is set to “Table”. Be sure to commit and clear the block before attempting to change any of these properties programmatically.
Caution: If you are typing a FROM clause query in the Property Palette, this can be done normally (SELECT… FROM ….WHERE…). However, if you are populating the property using a SET_BLOCK _PROPERTY built-in at runtime, you must pass the query enclosed in parentheses. When you are typing the property in using the Property Palette, Forms adds the parentheses automatically. For example, if your query was “SELECT empno, ename FROM emp” you would pass the string “(SELECT empno, ename FROM emp)” to the SET_BLOCK_PROPERTY built-in.
Basing a Block on Transactional Triggers
It is possible to base a block on a view (or table) and then override the default INSERT, UPDATE, DELETE, and LOCK operations that Forms usually uses with any PL/SQL procedure written by the developer. This is effectively the same functionality supported through INSTEAD OF trigger views. However, if you provide this functionality within the application, you have failed to encapsulate the logic in the view (where it properly belongs) and instead are calling the code from within the application.
The only reason to use this functionality is if you are attaching to a non-Oracle database. This functionality may also be required as the shift to object-relational databases is made when these procedures would correspond to the associated methods in the object table. Further discussion of this topic is beyond the scope of this paper.
Implementing a Block Based on Transactional Triggers
Set the Query Data Source Type and DML Data Target Type properties to “Transactional Triggers” and write the ON- triggers required to perform all SQL to the table or tables represented by items.
About the Authors
Peter Koletzke is a practitioner and self-proclaimed evangelist for Oracle Designer and Developer. He is a Consulting Manager and Principal Instructor for Millennia Vision Corporation (MVC), of Redwood Shores, CA. He is also a member of the Board of Directors of the International Oracle Users Group — Americas, a frequent contributor to national and international Oracle newsletters and users group conferences, and co-author, with Dr. Paul Dorsey, of two Oracle Press books: the Oracle Designer Handbook, Second Edition, (1998), and Oracle Developer Advanced Forms and Reports (1999) which was a basis for this paper. He won an award with Paul Dorsey at the ECO 1996 conference for his work on Forms templates, won the ODTUG 1999 Editor's Choice Award for a paper on Help Systems, was voted the Best Overall Presentation at Oracle OpenWorld 1998, was voted the ECO/SEOUC 2000 conference "Best Oracle Designer Presentation." http://ourworld.compuserve.com/homepages/Peter_Koletzke
MVC is a new breed of business consulting firm dedicated to improving its clients' business performance and position in the expanding e-business economy. MVC's unique, integrated approach enables companies to align their people, processes, and technologies in support of their business strategy. Global leaders in a wide variety of industries rely on MVC's proven methodologies, knowledge products, professional expertise and technical depth to extend their existing business models and seize new opportunities. Sample files for this paper are available at the MVC web site: http://www.mvsn.com.
Dr. Paul Dorsey is the founder and President of Dulcian, Inc. Dulcian specializes in Oracle Client-Server and Web custom application development and data migration. Paul is co-author with Peter Koletzke of The Oracle Designer Handbook (now in its second edition) and with Joseph R. Hudicka of Oracle8 Design Using UML Object Modeling, both from Oracle Press, 1999. Paul is an Associate Editor of SELECT Magazine. He is the President of the New York Oracle Users’ Group. Paul and Peter shared the Pinnacle Publishing Technical Achievement Award at ECO ‘95 for their work on a Forms template that became the basis for this book. Paul has won best presentation awards at both ECO and IOUW conferences.
Dulcian, Inc. provides a wide variety of consulting services, customized training, and products for the Oracle development environment. Dulcian provides products and services to large government and private sector companies worldwide. Services include developing new projects, auditing existing efforts, and rescuing failed projects. Dulcian’s vision is to deliver top-quality systems in record time. To this end, Dulcian has automated or streamlined every possible portion of the development life cycle to create a flexible strategy that is adapted for each project. http://www.dulcian.com