Monday, August 18, 2008

Performance Guideline-Use a PL/SQL variable instead of a bind variable

Use a PL/SQL variable instead of a bind variable. Referencing a bind variable that represents a forms item (such as :DEPT.DEPTNO) requires forms to find the item in its internal list. Therefore, each time your code uses that same bind variable, the lookup has to occur again. If you have code that uses the same bind variable more than once in the PL/SQL block, create a variable instead. For example, the following code stores the value of :DEPT.DEPTNO in a variable and references that variable instead of repeating a lookup to find the value.
For Example,
DECLARE
v_deptno NUMBER := ept.deptno;
BEGIN
message('The department # is ' v_deptno);
IF v_deptno < 0
THEN
message('Incorrect department number');
...;
END IF;
--
:control.department_num := v_deptno;
END;

No comments:

Post a Comment