PL/SQL in Forms 4.5 is limited to version 1.x and poses certain limitations to application development. Forms 5.x has lifted these limitations by supporting PL/SQL2.x, which has significant advantages in the programming constructs that can be used. Also, from Forms 5.x onwards, some of the DBMS packages supplied by Oracle can be directly called from Forms. Regarding the former, the use of PL/SQL tables, especially a PL/SQL table of records, eliminates the need for temporary blocks. As an example of the latter, PL/SQL 1.x prohibits the direct referencing of package variables in Forms 4.x. I will discuss the limitations of PL/SQL in Forms 4.x and also discuss their work-around.
PL/SQL in Forms 4.5 has two limitations:
Both these limitations can be solved by placing PL/SQL 2.x structures inside a package as public variables and by having stored (not public packaged) functions return their values. This is because Forms looks for the validity of the package specification and not the body.
The package PkgDeptEmp_fromclause, used for performing DML in a block based on a FROM CLAUSE query (described in the "Basing a Block on a FROM CLAUSE Query" section in Chapter 2, "Advanced GUI Development: Developing Beyond GUI" ), contains procedures that return a public record type variable and cannot be directly called from Forms 4.5 triggers or program units. The package can be modified as follows using the technique I've described.
Follow these steps:
The following code eliminates the drawback of directly referencing the packaged variable pkgdeptemp_fromclause.dept_emp by wrapping around the code contained in the ON_INSERT, ON-UPDATE, and ON-LOCK triggers in the form of stored procedures p_on_insert, p_on_update, and p_on_lock. The record type fields are passed as individual parameters to these procedures. The direct reference to the dept_emp packaged variable is made in these wrapper procedures. The code is as follows:
CREATE OR REPLACE PROCEDURE p_on_insert (p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_hiredate DATE, p_sal NUMBER, p_deptno NUMBER, p_dname VARCHAR2, retcd OUT NUMBER, errm OUT VARCHAR2) IS /* The following line shows how the packaged public variable dept_emp can be directly referenced in the wrapper procedure instead of directly in Forms 4.x */ dept_emp_rec pkgdeptemp_fromclause.dept_emp; BEGIN /* The individual parameters are assigned to their counterparts in the local record type variable dept_emp_rec declared to be of type dept_emp, the one defined in the package */ dept_emp_rec.empno := p_empno; dept_emp_rec.ename := p_ename; dept_emp_rec.job := p_job; dept_emp_rec.hiredate := p_hiredate; dept_emp_rec.sal := p_sal; dept_emp_rec.deptno := p_deptno; dept_emp_rec.dname := p_dname; Pkgdeptemp_fromclause.insert_procedure(dept_emp_rec, retcd, errm); END p_on_insert; / ON-INSERT trigger DECLARE Retcd NUMBER; Errm VARCHAR2(100); BEGIN /* The ON-INSERT trigger directly calls this wrapper procedure and compiles successfully */ P_on_insert(:fromclause_blk.empno, :fromclause_blk.ename, :fromclause_blk.job, :fromclause_blk.hiredate, :fromclause_blk.sal, :fromclause_blk.deptno, :fromclause_blk.dname, retcd, errm); IF (retcd <> pkgdeptemp_fromclause.get_success) THEN MESSAGE(errm); RAISE FORM_TRIGGER_FAILURE; END IF; END;
The code for the procedures p_on_update and p_on_lock and the corresponding ON-UPDATE and ON-LOCK triggers is similar to the code for the p_on_insert wrapper procedure and the preceding ON-INSERT trigger.
CREATE OR REPLACE PROCEDURE p_on_update (p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_hiredate DATE, p_sal NUMBER, p_deptno NUMBER, p_dname VARCHAR2, retcd OUT NUMBER, errm OUT VARCHAR2) IS dept_emp_rec pkgdeptemp_fromclause.dept_emp; BEGIN dept_emp_rec.empno := p_empno; dept_emp_rec.ename := p_ename; dept_emp_rec.job := p_job; dept_emp_rec.hiredate := p_hiredate; dept_emp_rec.sal := p_sal; dept_emp_rec.deptno := p_deptno; dept_emp_rec.dname := p_dname; Pkgdeptemp_fromclause.update_procedure(dept_emp_rec, retcd, errm); END p_on_update; / ON-UPDATE trigger DECLARE Retcd NUMBER; Errm VARCHAR2(100); BEGIN P_on_update(:fromclause_blk.empno, :fromclause_blk.ename, :fromclause_blk.job, :fromclause_blk.hiredate, :fromclause_blk.sal, :fromclause_blk.deptno, :fromclause_blk.dname, retcd, errm); IF (retcd <> pkgdeptemp_fromclause.get_success) THEN MESSAGE(errm); RAISE FORM_TRIGGER_FAILURE; END IF; END; ON-DELETE trigger BEGIN Pkgdeptemp_fromclause.delete_procecdure(:fromclause_blk.empno); END; CREATE OR REPLACE PROCEDURE p_on_lock (p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_hiredate DATE, p_sal NUMBER, p_deptno NUMBER, p_dname VARCHAR2, retcd OUT NUMBER, errm OUT VARCHAR2) IS dept_emp_rec pkgdeptemp_fromclause.dept_emp; retcd number; errm varchar2(100); BEGIN dept_emp_rec.empno := p_empno; dept_emp_rec.ename := p_ename; dept_emp_rec.job := p_job; dept_emp_rec.hiredate := p_hiredate; dept_emp_rec.sal := p_sal; dept_emp_rec.deptno := p_deptno; dept_emp_rec.dname := p_dname; Pkgdeptemp_fromclause.lock_procedure(dept_emp_rec, retcd, errm); END p_on_lock; ON-LOCK_trigger DECLARE Retcd NUMBER; Errm VARCHAR2(100); BEGIN P_on_lock(:fromclause_blk.empno, :fromclause_blk.ename, :fromclause_blk.job, :fromclause_blk.hiredate, :fromclause_blk.sal, :fromclause_blk.deptno, :fromclause_blk.dname, retcd, errm); IF (retcd <> pkgdeptemp_fromclause.get_success) THEN MESSAGE(errm); RAISE FORM_TRIGGER_FAILURE; END IF; END;
GUI Development
Advanced GUI Development: Developing Beyond GUI
Multi-form Applications
Advanced Forms Programming
Error-Message Handling
Object-oriented Methods in Forms
Intelligence in Forms
Additional Interesting Techniques
Working with Trees
Oracle 8 and 8i Features in Forms Developer