Base Tables

This section explains techniques concerning base tables for a data block. It begins by explaining how to change the base table of a block at runtime. The subsequent subsections elaborate on the techniques for basing a block on a FROM clause query and on a stored procedure. The procedure for performing DML in each case is outlined.

Changing the Base Table of a Block Dynamically

That the base table of a block cannot be changed dynamically was really a hindrance to Forms development before version 5.0. Forms programmers had to rely on alternative methods like changing the DEFAULT_WHERE or creating multiple blocks and hiding or showing them at runtime. However, changing the DEFAULT_WHERE does not always accomplish the task, especially when there is no link between the current base table and the new base table. To avoid this, Forms 5.0 has provided two new attributes, QUERY_DATA_SOURCE_NAME and DML_DATA_TARGET_ NAME , to the SET_BLOCK_PROPERTY built-in. This is an easy solution to the problem.

You use SET_BLOCK_PROPERTY and specify the base table/view/procedure name for the QUERY_DATA_SOURCE_NAME and DML_DATA_TARGET_NAME. You can also base a block on a stored procedure, a FROM clause SELECT, or a transactional trigger.

Tip

In Forms 5.x and above , a block can have separate sources for each of the SELECT, INSERT, UPDATE, DELETE, and LOCK DML operations, and each of these can be changed dynamically.

In Forms 6.x, a block can be based on object tables, but cannot be based on nested tables and VARRAY S.

 

Basing a Block on a FROM Clause QUERY

This is a requirement when columns from multiple tables must be displayed in the block and the conditions for selecting the individual columns vary. Specifying a SELECT instead of a base table has the following advantages:

• A database view can be eliminated.
• Multilevel look-up columns and look-ups based on mutually non- related column sets can figure as part of the same record, thus eliminating the use of a POST-QUERY, which would have been costly otherwise .
• Sorting and ad hoc querying on foreign key look-up columns are easily implemented as a base table operation.

Although an explicit option in Forms 5.x and above, you could indirectly base a block on a FROM clause QUERY in Forms 4.5. In Forms 4.5, you specify a SELECT statement to substitute a table name as a base table (that is, in the FROM clause).

Specify a SELECT statement involving multiple table joins instead of a base table for a block, provided that the SELECT statement is specified within single parentheses. Column ambiguity is not automatically resolved. In Forms 4.5, enclose this SELECT statement within parentheses.

The Query Database Source columns have to be specified in the block's Property Palette. This can be done by using the Data Block Wizard or by using the block Property Palette when creating the block manually.

The WHERE condition has to be specified properly and should be contained as part of the INLINE query instead of the DEFAULT_WHERE for the block, especially when selecting mutually non-related column sets and joining on non “foreign key columns. Consider the SELECT statement:

SELECT DEPT.DEPTNO, DNAME, EMP.EMPNO, ENAME, JOB, HIREDATE, SAL

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO)

This SELECT should be specified as it is (in Forms 4.5, also) rather than specifying EMP as the base table with DNAME as a foreign key look-up column. (This is the second method referred to in Chapter 1 for querying by nonbase table items.)

Here is an additional tip that will help as you work with base tables based on a FROM clause query:

• A FROM clause query specified as a base table for a data block is executed as an inline view and facilitates faster execution. Also, querying and ordering by foreign key look-up items are thus simplified, similar to querying and ordering in an ordinary base table block.

DML Operations on a Block Based on a FROM Clause Query

Because a FROM clause query is based on a SELECT statement and not on a database table, the default insertion, updating, deletion, and locking of records no longer hold good. These DML operations are carried out by writing ON-INSERT , ON-UPDATE , ON-DELETE , and ON-LOCK triggers for this block. These are similar to the INSTEAD-OF database triggers for doing DML on a view based on multiple tables in the database. These ON- transactional triggers replace the default processing of the respective DML operation, and the code inside each is executed instead.

A block based on a FROM clause query is treated as being based on a non-key preserved table, and no INSERT, UPDATE, or DELETE is allowed by default. Because no database table is involved and also multiple tables might be involved in the SELECT, the base table for the data block becomes one without a key column, therefore, the term non-key preserved. There is no rowid pre-reserved for each row.

The query operation is allowed by default, including the ad hoc query. An ad hoc query is the method by which users specify runtime criteria on which to base their queries. Querying records is similar to querying a view.

The sample form for this section's technique, FROMQUERY.FMB, is available online at this book's Web site. This technique is outlined in the following steps:

1. Consider the following query:

   SELECT DEPT.DEPTNO, DNAME, EMP.EMPNO, ENAME, JOB, HIREDATE, SAL
   
   FROM EMP, DEPT
   
   WHERE EMP.DEPTNO = DEPT.DEPTNO)
   

   Create a block named FROMCLAUSE_BLK based on this query. The columns in the SELECT statement automatically become items in the block.

2. The querying of records, including ad hoc querying, is taken care of by default.
3. To perform INSERT , UPDATE , and DELETE operations on the individual DEPT and EMP tables, you write ON-INSERT, ON-UPDATE, and ON-DELETE triggers .

   The code is as follows :

   CREATE OR REPLACE PACKAGE PkgDeptEmp_fromclause AS
   
   
   
   /* The following is a RECORD type having fields equivalent
   
   to the FROM clause SELECT columns that the block is based on */
   
   
   
    TYPE Dept_Emp IS RECORD (
   
    empno NUMBER(4),
   
    ename VARCHAR2(10),
   
    job VARCHAR2(9),
   
    hiredate DATE,
   
    sal NUMBER(11,2),
   
    deptno NUMBER(2),
   
    dname NUMBER(14));
   
    SUCCESS CONSTANT NUMBER := 0;
   
   
   
   /* The following four procedures take care of the LOCK, INSERT,
   
   UPDATE, and DELETE operations on the block */
   
   
   
    PROCEDURE lock_procedure (lock_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2);
   
    PROCEDURE insert_procedure(insert_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2);
   
    PROCEDURE update_procedure(update_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2);
   
    PROCEDURE delete_procedure(delete_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2);
   
    FUNCTION get_success RETURN NUMBER;
   
   
   
   END PKGDEPTEMP_FROMCLAUSE;
   
   
   
   CREATE OR REPLACE PACKAGE BODY PKGDEPTEMP_FROMCLAUSE AS
   
   
   
   /* The function below returns the constant SUCCESS */
   
    FUNCTION get_success RETURN NUMBER IS
   
    BEGIN
   
    RETURN(SUCCESS);
   
    END;
   
   
   
    PROCEDURE lock_procedure(lock_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2) IS
   
    v_temp NUMBER(4);
   
    BEGIN
   
   /* Lock the row corresponding to the input EMPNO */
   
    SELECT empno
   
    INTO v_temp
   
    FROM emp
   
    WHERE empno=lock_rec.empno
   
    FOR UPDATE;
   
    retcd := SUCCESS;
   
    EXCEPTION WHEN OTHERS THEN retcd := SQLCODE;
   
    errm := SQLERRM;
   
    END lock_procedure;
   
    PROCEDURE insert_procedure (insert_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2) IS
   
    CURSOR csr_deptemp IS SELECT deptno
   
    FROM dept
   
    WHERE deptno=insert_rec.deptno;
   
    v_temp NUMBER(4);
   
    BEGIN
   
   /* If input DEPTNO already exists, insert into EMP from input record;
   
   else insert into both DEPT and EMP from input record */
   
    OPEN csr_deptemp;
   
    FETCH csr_deptemp INTO v_temp;
   
    IF csr_deptemp%NOTFOUND THEN
   
    INSERT INTO dept(deptno,dname)
   
    VALUES (insert_rec.deptno, insert_rec.dname);
   
    END IF;
   
    CLOSE csr_deptemp;
   
    INSERT INTO emp (empno,ename, job,hiredate, sal, deptno)
   
    VALUES (insert_rec.empno, insert_rec.ename, insert_rec.job,
   
    insert_rec.hiredate, insert_rec.sal, insert_rec.deptno);
   
    retcd := SUCCESS;
   
   EXCEPTION WHEN OTHERS THEN
   
    retcd := SQLCODE;
   
    errm := SQLERRM;
   
   END insert_procedure;
   
   
   
    PROCEDURE update_procedure(update_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2) IS
   
    CURSOR csr_dept IS SELECT deptno
   
    FROM dept
   
    WHERE deptno=update_rec.deptno;
   
    v_temp NUMBER(4);
   
    BEGIN
   
   /* If input DEPTNO already exists, then update EMP columns from
   
   input record, or else insert into DEPT and update EMP from input record
   
   based on EMPNO */
   
    OPEN csr_dept;
   
    FETCH csr_dept INTO v_temp;
   
    IF csr_dept%NOTFOUND THEN
   
    INSERT INTO dept(deptno,dname)
   
    VALUES(update_rec.deptno,update_rec.dname);
   
    END IF;
   
    CLOSE csr_dept;
   
    --
   
    UPDATE emp
   
    SET ename=update_rec.ename,
   
    job=update_rec.job,
   
    hiredate=update_rec.hiredate,
   
    sal =update_rec.sal,
   
    deptno=update_rec.deptno
   
    WHERE empno=update_rec.empno;
   
    retcd := SUCCESS;
   
    EXCEPTION WHEN OTHERS THENretcd := SQLCODE;
   
    errm := SQLERRM;
   
    END update_procedure;
   
   
   
    PROCEDURE delete_procedure(delete_rec IN OUT dept_emp,
   
    retcd OUT NUMBER,
   
    errm OUT VARCHAR2) IS
   
    BEGIN
   
   /* Delete from emp based on input EMPNO. We do not delete from DEPT. */
   
    IF (delete_rec.empno IS NOT NULL) THEN
   
    DELETE emp WHERE empno=delete_rec.empno;
   
    END IF;
   
    retcd := SUCCESS;
   
    EXCEPTION WHEN OTHERS THEN
   
    retcd := SQLCODE;
   
    errm := SQLERRM;
   
    END delete_procedure;
   
   
   
   END PKGDEPTEMP_FROMCLAUSE;
   
   
   
   ON-INSERT trigger
   
   DECLARE
   
    dept_emp_rec pkgdeptemp_fromclause.dept_emp;
   
    retcd NUMBER;
   
    errm VARCHAR2(100);
   
   BEGIN
   
    dept_emp_rec.empno := :fromclause_blk.empno;
   
    dept_emp_rec.ename := :fromclause_blk.ename;
   
    dept_emp_rec.job := :fromclause_blk.job;
   
    dept_emp_rec.hiredate := :fromclause_blk.hiredate;
   
    dept_emp_rec.sal := :fromclause_blk.sal;
   
    dept_emp_rec.deptno := :fromclause_blk.deptno;
   
    dept_emp_rec.dname := :fromclause_blk.dname;
   
    Pkgdeptemp_fromclause.insert_procedure(dept_emp_rec, retcd, errm);
   
    IF (retcd != pkgdeptemp_fromclause.get_success) THEN
   
    MESSAGE(errm);
   
    RAISE FORM_TRIGGER_FAILURE;
   
    END IF;
   
   END;
   
   ON-UPDATE trigger
   
   DECLARE
   
    dept_emp_rec pkgdeptemp_fromclause.dept_emp;
   
    retcd NUMBER;
   
    errm VARCHAR2(100);
   
   BEGIN
   
    dept_emp_rec.empno := :fromclause_blk.empno;
   
    dept_emp_rec.ename := :fromclause_blk.ename;
   
    dept_emp_rec.job := :fromclause_blk.job;
   
    dept_emp_rec.hiredate := :fromclause_blk.hiredate;
   
    dept_emp_rec.sal := :fromclause_blk.sal;
   
    dept_emp_rec.deptno := :fromclause_blk.deptno;
   
    dept_emp_rec.dname := :fromclause_blk.dname;
   
    Pkgdeptemp_fromclause.update_procedure(dept_emp_rec, retcd, errm);
   
    IF (retcd != pkgdeptemp_fromclause.get_success) THEN
   
    MESSAGE(errm);
   
    RAISE FORM_TRIGGER_FAILURE;
   
    END IF;
   
   END;
   
   
   
   ON-DELETE trigger
   
   
   
   DECLARE
   
    dept_emp_rec pkgdeptemp_fromclause.dept_emp;
   
    retcd NUMBER;
   
    errm VARCHAR2(100);
   
   BEGIN
   
    dept_emp_rec.empno := :fromclause_blk.empno;
   
    dept_emp_rec.ename := :fromclause_blk.ename;
   
    dept_emp_rec.job := :fromclause_blk.job;
   
    dept_emp_rec.hiredate := :fromclause_blk.hiredate;
   
    dept_emp_rec.sal := :fromclause_blk.sal;
   
    dept_emp_rec.deptno := :fromclause_blk.deptno;
   
    dept_emp_rec.dname := :fromclause_blk.dname;
   
    Pkgdeptemp_fromclause.delete_procedure(dept_emp_rec, retcd, errm);
   
    IF (retcd != pkgdeptemp_fromclause.get_success) THEN
   
    MESSAGE(errm);
   
    RAISE FORM_TRIGGER_FAILURE;
   
    END IF;
   
   END;
   
   
   
   ON-LOCK_trigger
   
   
   
   DECLARE
   
    dept_emp_rec pkgdeptemp_fromclause.dept_emp;
   
    retcd NUMBER;
   
    errm VARCHAR2(100);
   
   BEGIN
   
    dept_emp_rec.empno := :fromclause_blk.empno;
   
    dept_emp_rec.ename := :fromclause_blk.ename;
   
    dept_emp_rec.job := :fromclause_blk.job;
   
    dept_emp_rec.hiredate := :fromclause_blk.hiredate;
   
    dept_emp_rec.sal := :fromclause_blk.sal;
   
    dept_emp_rec.deptno := :fromclause_blk.deptno;
   
    dept_emp_rec.dname := :fromclause_blk.dname;
   
    Pkgdeptemp_fromclause.lock_procedure(dept_emp_rec, retcd, errm);
   
    IF (retcd != pkgdeptemp_fromclause.get_success) THEN
   
    MESSAGE(errm);
   
    RAISE FORM_TRIGGER_FAILURE;
   
    END IF;
   
   END;
   

Basing a Block on a Stored Procedure

As far as base tables are concerned , basing a block on a stored procedure is by far the most advanced extension of Forms over the earlier releases of 4.5 and below. The purpose of basing a block on a stored procedure is to provide user -defined logic for replacing the default functionality of SELECT , LOCK, INSERT, UPDATE, and DELETE. This is required when

• a block must be based on multiple tables tied together by complex application logic. In this case, a view or a FROM clause query cannot be used because of the complexity of the underlying logic involved.
• DML must be performed on the server side ”either dynamic DML or DML encapsulating application logic.

This method of basing a block on a stored procedure involves getting and returning result sets of data rather than processing one record at a time, thus reducing network traffic. This is helpful especially when the network involved is a WAN.

To use this technique, follow these steps:

1. Define at least five stored procedures, which I recommend that you include as part of a package: one each for SELECT, INSERT, UPDATE, DELETE, and LOCK. The query procedure should pass and return a REFCURSOR or a table of records. The other four procedures should pass and return a table of records. A single record would do the job, but to involve multiple records, you should use a table of records.

   For this example, the database package PKGDEPTEMP uses the DEPT and EMP tables and contains the five required procedures pquery, pinsert, pupdate, pdelete, and plock. The code for the package appears in Listing 2.1.

Tip

Data blocks can be based on a stored procedure returning a REFCURSOR, an Index-by table of records, a record type, and an object REF.

 

1. The form uses a block named STPROC created using the Data Block Wizard. The five pro cedures are specified as the values for the Query procedure, Insert procedure, Update procedure, Delete procedure, and Lock procedure names in the Data Block Wizard. One such screen, for the Query procedure, appears in Figure 2.1.

   Figure 2.1. The Data Block Wizard shows how to specify values for the Query procedure.

   

   The Available Columns box lists the result set columns. The argument names are carried over from the names of the procedure parameters. The extra parameter P_EMPNO is required for performing ad hoc queries and is explained at a later time.

2. Make the following changes to the property palette: Set the Query Data Source Procedure Name to QUERY_PROCEDURE, the query procedure name; set Query Data Source Columns to the result set columns, as shown in Figure 2.2. These columns also become the base table items in the block. Set Query Data Source Arguments to the Query procedure parameters, as shown in Figure 2.3.

   Figure 2.2. How to specify Query Data Source Procedure Name.

   

   Figure 2.3. Specifying Query Data Source Arguments.

   

   Similarly, in the Property Palette for the block, under the Advanced Database section, set the corresponding properties for Insert, Update, Delete, and Lock accordingly , as shown in Figure 2.4.

3. At the block level, Forms generates four triggers: INSERT-PROCEDURE, UPDATE-PROCEDURE, DELETE-PROCEDURE, and LOCK-PROCEDURE. These can be thought of as replacements for the ON-INSERT, ON-UPDATE, ON-DELETE, and ON-LOCK triggers for a base table block. The sample code for the INSERT-PROCEDURE appears in Figure 2.5.

Tip

Do not modify the code in the INSERT-PROCEDURE, UPDATE-PROCEDURE, DELETE- PROCEDURE, and LOCK-PROCEDURE triggers. Doing this will result in the corresponding operation (that is, Insert, Update, Delete, or Lock ) functioning incorrectly. This trigger is not regenerated every time the Form is compiled; therefore, if modified, the customized code is executed, instead of default Forms written code, which might yield wrong results.

 

Figure 2.4. Specifying advanced database properties.

Figure 2.5. INSERT-PROCEDURE written by Forms for a block based on a stored procedure.

Listing 2.1 The PKGDEPTEMP Package

CREATE OR REPLACE PACKAGE PkgDeptEmp AS



/* A REF cursor variable is used as an IN OIT parameter for the query

procedure, and recordtype variables are used as IN OUT variables

for the INSERT, UPDATE, DElETE, and LOCK procedures. These parameters

are IN OUT because they transfer data to and from the block

and the database. */



 TYPE Dept_Emp IS RECORD(

 empno NUMBER(4),

 ename VARCHAR2(10),

 job VARCHAR2(9),

 hiredate DATE,

 sal NUMBER(11,2),

 deptno NUMBER(2),

 dname VARCHAR2(14));

 TYPE dept_emp_ref IS REF CURSOR RETURN dept_emp;

 TYPE dept_emp_tab IS TABLE OF dept_emp INDEX BY BINARY_INTEGER;

 PROCEDURE query_procedure (resultset IN OUT dept_emp_ref,

 p_empno IN NUMBER);

 PROCEDURE lock_procedure (dmlset IN OUT dept_emp_tab);

 PROCEDURE insert_procedure(dmlset IN OUT dept_emp_tab);

 PROCEDURE update_procedure(dmlset IN OUT dept_emp_tab);

 PROCEDURE delete_procedure(dmlset IN OUT dept_emp_tab);

END PKGDEPTEMP;

/



CREATE OR REPLACE PACKAGE BODY PKGDEPTEMP AS

 PROCEDURE query_procedure(resultset IN OUT dept_emp_ref,

 p_empno IN NUMBER)

 IS

 BEGIN



/* The code below selects from the EMP and DEPT tables into a REF cursor and

outputs the result to the block. A REF cursor is more efficient than a PL/SQL

record for the query operation because there is no PL/SQL involved. The

coordination and synchronization between the input dmlset and the

population of the block with these records are done by Forms

automatically */



 OPEN resultset FOR

 SELECT e.empno, e.ename, e.job, e.hiredate,

 e.sal, e.deptno, d.dname

 FROM emp e, dept d

 WHERE e.deptno = d.deptno

 AND e.empno = NVL(p_empno, e.empno);

 END query_procedure;



 PROCEDURE lock_procedure(dmlset IN OUT dept_emp_tab) IS

 tempout NUMBER(4);

 BEGIN



/* The following locks each record in the input dmlset. The coordination

and synchronization between the input dmlset and the block's records that

have been marked for LOCKING are done by the LOCK-PROCEDURE trigger

written by Forms */



FOR i IN 1..dmlset.COUNT LOOP

 SELECT empno

 INTO tempout

 FROM emp

 WHERE empno=dmlset(i).empno

 FOR UPDATE;

 END LOOP;

 END lock_procedure;



 PROCEDURE insert_procedure (dmlset IN OUT dept_emp_tab) IS

 CURSOR c_dept(i BINARY_INTEGER) IS

 SELECT deptno

 FROM dept

 WHERE deptno=dmlset(i).deptno;

 tempout NUMBER(4);

 BEGIN



/* The following inserts into the DEPT table if not found already.

It also inserts into the EMP table. Each record in the input dmlset

is inserted. The coordination and synchronization between the input dmlset

and the block's records that have been marked for INSERT are done by

the INSERT-PROCEDURE trigger written by Forms */



FOR i IN 1 .. dmlset.COUNT LOOP

 OPEN c_dept(i);

 FETCH c_dept INTO tempout;

 IF c_dept%NOTFOUND THEN

 INSERT INTO dept(deptno,dname)

 VALUES (dmlset(i).deptno,dmlset(i).dname);

 END IF;

 CLOSE c_dept;

 INSERT INTO emp(empno,ename, job,hiredate, sal, deptno)

 VALUES(dmlset(i).empno,dmlset(i).ename, dmlset(i).job,

 dmlset(i).hiredate, dmlset(i).sal, dmlset(i).deptno);

 NULL;

END LOOP;

 END insert_procedure;



 PROCEDURE update_procedure(dmlset IN OUT dept_emp_tab) IS

 CURSOR c_dept(i BINARY_INTEGER) IS

 SELECT deptno

 FROM dept

 WHERE deptno=dmlset(i).deptno; tempout NUMBER(4);

 BEGIN



/* The following inserts into the DEPT table if not found already.

It also updates the EMP table. Each record in the input dmlset is updated.

The coordination and synchronization between the input dmlset and

the block's records that have been marked for UPDATE are done by

the UPDATE-PROCEDURE trigger written by Forms */



FOR i IN 1..dmlset.COUNT LOOP

 OPEN c_dept(i);

 FETCH c_dept INTO tempout;

 IF c_dept%NOTFOUND THEN

 INSERT INTO dept(deptno,dname)

 VALUES(dmlset(i).deptno,dmlset(i).dname);

 ELSE

 UPDATE dept

 SET dname = dmlset(i).dname

 WHERE deptno = dmlset(i).deptno;

 END IF;

 CLOSE c_dept;

 --

 UPDATE emp

 SET ename=dmlset(i).ename,

 job=dmlset(i).job,

 hiredate=dmlset(i).hiredate,

 sal =dmlset(i).sal,

 deptno=dmlset(i).deptno

 WWWHERE empno=dmlset(i).empno;

END LOOP;

 END update_procedure;



 PROCEDURE delete_procedure(dmlset IN OUT dept_emp_tab) IS

 BEGIN

/* The following deletes from the EMP table. Each record in the input dmlset

is deleted. The coordination and synchronization between the input dmlset

and the block's records that have been marked for DELETE are done by the

DELETE-PROCEDURE trigger written by Forms */



FOR i IN 1..dmlset.COUNT LOOP

 DELETE FROM emp WHERE empno=dmlset(i).empno;

END LOOP;

 END delete_procedure;



END PKGDEPTEMP;

Tips for Basing Blocks on Stored Procedures

Here are some additional tips that will help when you base blocks on stored procedures:

• There is no connection between a data block based on a stored procedure and the stored procedure itself, except for the following: The QUERY, INSERT, UPDATE, DELETE, and LOCK data source procedures specified are executed in response to the block QUERY, INSERT, UPDATE, and DELETE functions.
• As far as the query operation is concerned, all the triggers and system variables function in the same way as with a block based on a database table. Specifically, the PRE-QUERY, POST-QUERY, ON-SELECT, and ON-FETCH triggers fire by default (that is, without any extra code written), and the system variables, :SYSTEM.MODE, :SYSTEM.FORM_STATUS, : SYSTEM.BLOCK_STATUS, and :SYSTEM.RECORD_STATUS, return the same values as in the case of a base table block. Even ad hoc querying is possible, but with extra care taken. (Ad hoc querying is explained in the next section, "Specifying Ad Hoc Query Criteria.")
• Do not define ON-SELECT and ON-FETCH triggers for the block based on a procedure because it defies (and, in fact, replaces ) the default selection procedure of returning a result set from the Query Data Source procedure.
• The ON-INSERT, ON-UPDATE, ON-DELETE, and ON-LOCK triggers do fire. Do not define these triggers for a data block based on a stored procedure.

Specifying Ad Hoc Query Criteria in Case of Blocks Based on Stored Procedures

As mentioned earlier, no connection exists between the data block and the DML procedures except for receiving and sending the data. Therefore, any additional functions must be taken care of explicitly. I have discussed the techniques for performing INSERT, UPDATE, DELETE, and LOCK operations in blocks based on stored procedures. The SELECT operation is possible by default, that is, by Forms, without writing code. However, one function pertaining to the selection of records is the specifying of ad hoc query criteria at runtime. This is an often-required feature because it provides the flexibility of querying on user-defined criteria, thus eliminating the need to search the entire result set for a specific subset of data. This section highlights the technique for ad hoc querying in a block based on a stored procedure.

To do so, follow these steps:

1. Specify extra parameters, one each for the items that are Query Allowed, to the Query Data Source procedure. This can be done while defining the stored package or at a later stage.
2. Specify the value clause for each parameter as the item name or form parameter name that supplies the IN value to each query procedure parameter defined in step 1. Again, this can be done while creating the block or later on, using the Data Block Wizard.

Tip

Creating the extra parameter(s) and specifying the value do not guarantee the query to be ad hoc. To take care of the desired functionality, the corresponding logic has to be coded in the body of the Query procedure by adding additional WHERE conditions.

 

The package code in Listing 2.1 already includes the extra parameter P_EMPNO for the PKGDEPTEMP.QUERY_PROCEDURE. The Data Block Wizard with the value modification appears in Figure 2.6.

Figure 2.6. The Data Block Wizard shows the extra parameter P_EMPNO used for ad hoc querying.

Note that the value is specified as :STPROC.EMPNO (you must include the colon ), which is the item name in the STPROC block. This value provides querying on the STPROC.EMPNO item in Enter-Query mode. At runtime, the value of the user-entered EMPNO becomes the IN value of the P_EMPNO parameter. Completing these two steps will enable the user to query on a specific EMPNO.

Note that the technique described here works only for exact equality matches and without involving any other operators, such as > , < , and so on. For example, if the user enters > 1000 in the EMPNO item, an error occurs. Additional functionality for nonequality and LIKE matches should be coded explicitly in the Query procedure.