Basing a Block on a Stored Procedure Involving Object Tables

The concept of basing a block on a stored procedure is introduced in Chapter 2, "Advanced GUI Development: Developing Beyond GUI." The same method is illustrated here with reference to object tables.

A block has to be based on a stored procedure when it is necessary to provide user -defined logic for replacing the default functionality of SELECT, LOCK, INSERT, UPDATE, and DELETE. This is required in the following circumstances:

• A block needs to be based on multiple tables involving complex application logic tying them. In this case, a view or a FROM clause query cannot be used because of the complexity of the underlying logic involved.
• There is DML to be performed on the server-side, using 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. Similar to the method of basing a block on a stored procedure involving non-object relational tables, this method also involves getting and returning result sets of data in the form of Index-by table of records.

The basic building blocks and the steps involved are as follows :

1. Create an object type DEPT_EMP with the following structure:

   CREATE OR REPLACE TYPE dept_emp AS OBJECT (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, sal NUMBER(11,2), deptno NUMBER(2), dname VARCHAR2(14));

2. Create an object table DEPTEMP_TAB of type DEPT_EMP :

   CREATE TABLE deptemp_tab OF dept_emp;

3. Create an object relational table dept_emp_addr with two REF columns deptemp of type dept_emp and addr of type add_type :

   CREATE TABLE dept_emp_addr (deptemp REF dept_emp, addr REF add_type);

4. Define five separate procedures, included as part of a package called PKGDEPTEMPOBJ: one each for SELECT, INSERT, UPDATE, DELETE, and LOCK. All the procedures pass and return a table of RECORDS whose structure is the same as that of the object type dept_emp.

Tip

Data blocks can be based on a stored procedure returning a ref cursor, an index-by table, records, or a record type.

1. Create a block named STPROCOBJ that has these procedures specified as the values for Query Procedure, Insert procedure, Update Procedure, Delete Procedure and Lock Procedure Names in the Data Block Wizard for the block. The items in this block correspond to the columns in the object type dept_emp.
2. Make the following changes to the property palette. Set the Query Data Source procedure Name to PKGDEPTEMPOBJ.QUERY_PROCEDURE ; set Query Data Source Columns to Result Set columns EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO, DNAME ; and set Query Data Source Arguments to RESULTSET and P_EMPNO.

   Similarly, under Advanced Database, set the corresponding properties for Insert, Update, Delete, and Lock accordingly .

   DML is performed internally on the object/object-relational tables DEPTEMP_TAB and DEPT_EMP_ADDR.

3. Forms generates four triggers called INSERT-PROCEDURE, UPDATE-PROCEDURE, DELETE-PROCEDURE, and LOCK-PROCEDURE at the block level. These can be thought of as replacements for ON-INSERT, ON-UPDATE, ON-DELETE, and ON-LOCK triggers for a base table block.

The code for the package PKGDEPTEMPOBJ can be found in Listing 10.1.

Listing 10.1 Package with procedures for Query, Lock, Insert, Update and Delete operations involving Object Tables.

CREATE OR REPLACE PACKAGE PKGDEPTEMPOBJ AS /* Variables which are of type of an index-by table of records, are used as /IN OUT variables for the INSERT, UPDATE, DELETE and LOCK procedures. These /parameters are IN OUT as they transfer data to and from the block and the /database. */ TYPE dept_emp_rec IS RECORD (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, sal NUMBER(11,2), deptno NUMBER(4), dname VARCHAR2(14)); TYPE dept_emp_tab IS TABLE OF dept_emp_rec INDEX BY BINARY_INTEGER; PROCEDURE query_procedure(resultset IN OUT dept_emp_tab, 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 PKGDEPTEMPOBJ; / CREATE OR REPLACE PACKAGE BODY PKGDEPTEMPOBJ AS PROCEDURE query_procedure(resultset IN OUT dept_emp_tab, p_empno IN NUMBER) IS CURSOR c_dept_emp IS SELECT DEREF(a.deptemp) FROM dept_emp_addr a WHERE a.deptemp.empno = NVL(p_empno, a.deptemp.empno); i BINARY_INTEGER := 1; v_deptemp dept_emp; BEGIN /* The code below selects from the DEPT_EMP_ADDR tables into a cursor based / on dept_emp object type and outputs the result to the block. Note how the /DEREF operator is used to get the actual record rather than the OID. The /co-ordination and synchronization between the input resultset and the /population of the block with these records is done by Forms automatically */ OPEN c_dept_emp; LOOP FETCH c_dept_emp INTO v_deptemp; EXIT WHEN c_dept_emp%NOTFOUND; resultset(i).empno := v_deptemp.empno; resultset(i).ename := v_deptemp.ename; resultset(i).job := v_deptemp.job; resultset(i).hiredate := v_deptemp.hiredate; resultset(i).sal := v_deptemp.sal; resultset(i).deptno := v_deptemp.deptno; resultset(i).dname := v_deptemp.dname; i := i+1; END LOOP; EXCEPTION WHEN OTHERS THENRAISE_APPLICATION_ERROR(-20101, SQLERRM); END query_procedure; PROCEDURE lock_procedure(dmlset IN OUT dept_emp_tab) IS v_deptemp dept_emp; BEGIN /* The following locks each record in the input dmlset. The co-ordination and / synchronization between the input dmlset and the block's records that have /been marked for LOCKING is done by the LOCK-PROCEDURE trigger written by /Forms. Again note the use of the DEREF operator. */ FOR i IN 1..dmlset.COUNT LOOP SELECT DEREF(a.deptemp) INTO v_deptemp FROM dept_emp_addr a WHERE a.deptemp.empno = dmlset(i).empno FOR UPDATE; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20102, SQLERRM); END lock_procedure; PROCEDURE insert_procedure (dmlset IN OUT dept_emp_tab) IS CURSOR c_dept_emp(i BINARY_INTEGER) IS SELECT DEREF(a.deptemp) FROM dept_emp_addr a WHERE a.deptemp.empno=dmlset(i).empno; v_deptemp dept_emp; BEGIN /* The following inserts into the DEPTEMP_TAB table if already not found. It /also inserts into the DEPT_EMP_ADDR table. Note that the actual OID is /inserted into the DEPT_EMP_ADDR table for the deptemp REF column. Each record / in the input dmlset is inserted. The co-ordination and synchronization /between the input dmlset and the block's records that have been marked for /INSERT is done by the INSERT-PROCEDURE trigger written by Forms */ FOR i IN 1 .. dmlset.COUNT LOOP OPEN c_dept_emp(i); FETCH c_dept_emp INTO v_deptemp; IF c_dept_emp%NOTFOUND THEN INSERT INTO deptemp_tab VALUES (dmlset(i).empno,dmlset(i).ename, dmlset(i).job, dmlset(i).hiredate, dmlset(i).sal, dmlset(i).deptno,dmlset(i).dname); INSERT INTO dept_emp_addr(deptemp) SELECT REF(a) FROM deptemp_tab a WHERE a.empno = dmlset(i).empno; END IF; CLOSE c_dept_emp; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20103, SQLERRM); END insert_procedure; PROCEDURE update_procedure(dmlset IN OUT dept_emp_tab) IS CURSOR c_dept_emp(i BINARY_INTEGER) IS SELECT DEREF(a.deptemp) FROM dept_emp_addr a WHERE a.deptemp.empno = dmlset(i).empno; v_deptemp dept_emp; BEGIN /* The following inserts into the DEPTEMP_TAB table if already not found, else / it updates the same table. Each record in the input dmlset is updated. The /co-ordination and synchronization between the input dmlset and the block's /records that have been marked for UPDATE is done by the UPDATE-PROCEDURE /trigger written by Forms */ FOR i IN 1..dmlset.COUNT LOOP OPEN c_dept_emp(i); FETCH c_dept_emp INTO v_deptemp; IF c_dept_emp%NOTFOUND THEN INSERT INTO deptemp_tab VALUES (dmlset(i).empno,dmlset(i).ename, dmlset(i).job, dmlset(i).hiredate, dmlset(i).sal, dmlset(i).deptno,dmlset(i).dname); ELSE UPDATE deptemp_tab a SET ename = dmlset(i).ename, job=dmlset(i).job, hiredate=dmlset(i).hiredate, sal =dmlset(i).sal WHERE a.empno = dmlset(i).empno; END IF; CLOSE c_dept_emp; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20104, SQLERRM); END update_procedure; PROCEDURE delete_procedure(dmlset IN OUT dept_emp_tab) IS v_deptemp dept_emp; BEGIN /* The following deletes from the DEPT_EMP_ADDR table. Note how the DEREF /operator is used to find the matching record. Each record in the input dmlset / is deleted. The co-ordination and synchronization between the input dmlset /and the block's records that have been marked for DELETE is done by the /DELETE-PROCEDURE trigger written by Forms */ FOR i IN 1..dmlset.COUNT LOOP SELECT DEREF(a.deptemp) INTO v_deptemp FROM dept_emp_addr a WHERE a.deptemp.empno = dmlset(i).empno; DELETE FROM dept_emp_addr a WHERE DEREF(a.deptemp) = v_deptemp; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20105, SQLERRM); END delete_procedure; END PKGDEPTEMPOBJ;

Tip

OIDS are generated automatically when the INSERT_PROCEDURE is invoked at the server side.

Tip

It is not necessary to set the DML Returning Value because the procedures use DEREF to get the actual row values, and hence are not based on the OID.