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:

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:

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

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:

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.

Related

Категории