The Work-around for PL/SQL 2.x Limitations in Forms 4.5

The Work around for PL SQL 2 x Limitations in Forms 4 5

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:

  • PL/SQL 2.x features such as PL/SQL tables cannot be used in Forms 4.x. The record types %ROWTYPE and %TYPE can be used.
  • Public package variables cannot be directly referenced in Forms 4.x

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:

  1. Replace the code for ON-INSERT, ON-UPDATE, and ON-LOCK triggers with stored procedures, named p_on_insert, p_on_update, and p_on_lock, taking the individual record fields as parameters. These procedures also have two more OUT parameters having RETCD and ERRM to return. RETCD returns the success or failure of the procedure, which is on success and the corresponding SQLCODE on failure. ERRM is the SQLERRM exception error message corresponding to the SQLCODE returned. It is NULL in case of a success.
  2. Call the created wrapped procedures with the respective block item values, in the ON-INSERT, ON-UPDATE, and ON-LOCK triggers.
  3. There is no need for a p_on_delete procedure because the original delete procedure takes EMPNO as an argument that is not referenced by a packaged public variable.

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



Oracle Developer Forms Techniques
Oracle Developer Forms Techniques
ISBN: 0672318466
EAN: 2147483647
Year: 2005
Pages: 115

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net