Client-Side Filters, LOVs, and Pop-Lists
Introduction
This paper will demonstrate some tips and techniques for using client-side filters, LOV’s and pop-lists in useful ways.
Client-Side Filters
· Have you ever had to add a correlated sub-query to the WHERE clause of an Oracle Forms block? Did you find that this query that returns less items, came back from the database much, much slower, even when tuned?
· Did you ever want to restrict the results of a query to only the top 10 results?
· Did you ever try to use a function in a where clause, only to discover that it changed the package state and was not allowed?
· Did you ever want to restrict the data returned in a Forms query by an organization that the user has access to, or some other security requirement?
These are a few of the situations that demand client-side filtering of query results.
This section discusses a simple, undocumented technique to filter the results of an Oracle Forms query without any loss in database performance.
The secret is to use a base-table pop-list item to force filter the data.
In Oracle Forms when a record is queried from the database, if the resulting field value in a pop-list item is not satisfied by either the values in a static list or the values in a populated query, the record is not displayed and no error is returned
Figure 1: Filter on DEPT.LOC
This feature can be used to your advantage by setting up a
pop-list that is limited to the results you desire to be returned. For example in Scott/Tiger's DEPT table, if
the Item LOC is set to a pop-list
with only "CHICAGO" as an option, as chosen through a filter pop-list
that offers all acceptable values for LOC in the table DEPT, the results might
be as shown in Figure 1. Notice the only
acceptable value for Location has been set to "
Setting up the acceptable values in a pop-list is simply accomplished by creating a record group on a query and basing the item LOC's pop-list on the record group. The following code was included in the WHEN_LIST_CHANGED trigger on the item Location Filter. As a precaution, keep in mind that a pop-list can not be repopulated when in use. Therefore, I have performed a CLEAR_BLOCK prior to setting up the pop-list.
/* STUFF.LOCATION - WHEN-LIST-CHANGED */
go_block('filter_dept');
clear_block;
p_setup_dept_loc(:stuff.location, 'filter_dept.loc');
execute_query;
/* Procedure P_SETUP_DEPT_LOC */
PROCEDURE P_SETUP_DEPT_LOC (i_loc varchar2, i_item varchar2) IS
v_sql varchar2(2000);
BEGIN
v_sql := 'select '''||i_loc||''' cd, '''||i_loc||''' txt '
||' from dual';
p_setup_rg('RG_LOC',v_sql);
populate_list(i_item, 'RG_LOC');
END;
/* Procedure P_SETUP_RG */
PROCEDURE P_SETUP_RG (i_rgname in varchar2, i_sql in varchar2) IS
/* Create a Record Group Based on a SQL Statement */
v_cnt integer;
v_status integer;
v_rowcount integer;
v_rg_id RecordGroup;
v_gc_id GroupColumn;
v_gc_id2 GroupColumn;
BEGIN
if i_sql is null then
null;
t_error.p_error(1005,-1,i_rgname);
else
v_rg_id := Find_Group(i_rgname ); /* Delete Old Record Group */
IF NOT Id_Null(v_rg_id) THEN Delete_Group(v_rg_id ); END IF;
v_rg_id := Find_Group(i_rgname ); /* Initialize RG with SQL */
IF Id_Null(v_rg_id) THEN
v_rg_id := Create_Group_From_Query(i_rgname, i_sql);
v_status := Populate_Group(v_rg_id );
ELSE raise form_trigger_failure;
END IF;
end if;
END P_SETUP_RG;
As an additional example of using a pop-list to filter data, I suggest displaying the Top "N" values in a query. For this example I used the display of the Top "N' salaries in the Scott/Tiger EMP table.
To accomplish this feat, we need to define a base table column on the block as a pop-list. The pseudo-column ROWNUM can be used for this purpose. As in the previous example, the pop-list can be populated using a query. For this example I've used the following query:
v_sql := 'select to_char(rownum) id, to_char(rownum) txt '
||' from all_objects where rownum <= '
||to_char(nvl(i_num,10));
The results are shown in Figure 2. Here again the valid values for the Top# pop-list is used as a limit. Figure 1.2 shows that only values of 1 through 5 have been setup in this example of the Top 5 Salaries in the EMP table.
Figure 2: Top “N” Salaries in DEPT.SAL
In this example I've demonstrated placing the pop-list filter on a Synchronized Item rather than the base table column. Here again, the limited values in the pop-list also force the display of only values that satisfy the desired filter. The use of a Synchronized Item offers the opportunity to display the base column as required but still retains the advantage of a Client-Side filter. Additionally, the pop-list need not even be displayed. However, in order for it to operate properly, the filter pop-list must be on a canvas, but the visible property can be set to “NO.”
As an aside, for this demonstration to return the Top "N' values, it is important to understand how to use ROWNUM properly. To accomplish our goal we need to pre-sort the results before allowing Oracle to assign the ROWNUM psudo-column value. We do this by basing the block on a Query rather than the base table. The query is as follows:
(select empno, ename, sal, (sal*(-1)) sort_sal
from EMP
group by (sal*(-1)), ename, empno, sal)
Note the use of the group by to sort salaries descending. Also, be sure not to include ROWNUM in the query or the resulting values will be assigned before the sort instead of afterwards. Finally, to make the block fully functional, the DML Target Name is set to the original base table EMP, and the Item ROWNUM has the Query Only property set to YES.
A final example of pop-list filters demonstrates the use of a security-based function. The objective of this query is to return only those Employees in "My" Department or below. The screenshots in Figure.3 show the results of the query when logged in as SCOTT and as MARTIN.
Figure 3: My Employees – Security Filter
Here again the Query Data Source is based on a query as follows:
(select emp.*, dkf_dept.f_my_dept_yn(deptno) my_dept_yn
from EMP)
In this example the query includes a function that returns either Y or N depending on whether the provided DEPTNO is equal to or below the USER's Department. To filter the record with a function value of Y, all that needs to be done to setup the pop-list is to build an Element List with Y as the only acceptable value. Here again this field can be hidden by setting the visible property and the Query Only to “NO” to allow data entry. To complete the example, the following is the code for the function used in the in-line view.
package body DKF_DEPT is
function f_my_dept_yn (i_deptno number) return varchar2 is
/* function returns 'Y' if dept is mine or below */
/* my DEPT */
cursor c_my_dept is
select deptno
from emp
where ename = user;
/* DEPT tree from ic_deptno down */
cursor c_dept_tree (ic_deptno number) is
select deptno
from dept
start with deptno = ic_deptno
connect by prior deptno = deptno_rfk;
v_my_deptno number; /* my deptno */
o_yn char(1); /* output Y or N */
begin
o_yn := 'N'; /* initialize to N */
open c_my_dept; /* get my deptno */
fetch c_my_dept into v_my_deptno;
close c_my_dept;
/*
for r_dept in c_dept_tree(v_my_deptno) loop
if r_dept.deptno = i_deptno then
o_yn := 'Y'; /* Dept matches passed in value */
exit;
end if;
end loop;
return o_yn; /* return result */
end f_my_dept_yn;
END DKF_DEPT;
Drill-Down List of Values
I have built many "drill-down" type LOV's (List Of Values) using this technique and it is very effective for the end-users. It’s very easy to add additional options to your record group using UNION ALL.
For a drill-down LOV, you'll need to include selection options that allow you to go back to a prior LOV set or select an alternative Search Criteria. Here is an example of a drill down LOV with a Recursive Foreign Key Drill Down on ORG:
1.
A Control Block DRILL is used to receive and send data
to the LOV Fields: Key, Action,
2. The Record Group (RFK_RG) has three actions, each represented by a SELECT statement as a component of the UNION ALL SQL SELECT statement:
select -3 action, org_id id,
org_name display, org_name descr,
par_org_id rfk_id
from organization
where nvl(par_org_id,-1) = nvl(:drill.key, -1)
union all
select -2 action, par_org_id id,
'<Back>' display,
'<Back>' descr, par_org_id rfk_id
from organization
where org_id = :drill.key
and :drill.key is not null
union all
select 0 action, org_id id,
'['||org_name||']' display,
org_name descr, par_org_id rfk_id
from organization
where org_id = :drill.key
order by 1 desc, 3
3. Data is returned from the LOV as shown in Table 1.
|
Column Names |
Return Item |
Display Width |
Column Title |
|
ACTION |
DRILL.ACTION |
0 |
Action |
|
ID |
DRILL.ID |
0 |
Primary Key |
|
DISPLAY |
|
200 |
Description |
|
DESCR |
DRILL.DESCR |
0 |
Real Description |
|
RFK_ID |
DRILL.RFK_ID |
0 |
Parent Key |
Table 1. LOV return data
4. The Procedure that processes the LOV is as follows:
PROCEDURE LISTVAL_ORG
(i_id_fld varchar2 -- ID Field,
i_descr_fld varchar2 -- Description Field
) IS
v_status boolean;
v_lov varchar2(32) := 'rfk_lov';
v_lov_title varchar2(200) := 'Organization';
BEGIN
set_lov_property(v_lov, title,
v_lov_title||' - Drill Down List');
:drill.key := null;
loop
v_status := show_lov (v_lov);
if not v_status then
exit;
else
if :drill.action = -2 then
-- Back
:drill.key := :drill.id;
elsif :drill.action = 0 then
-- Return Values
copy(:drill.descr,i_descr_fld);
copy(to_char(:drill.id),i_id_fld);
exit;
elsif :drill.action = -3 then
-- Drill Down
:drill.key := :drill.id;
else exit;
-- Unknown Action
end if;
end if;
end loop;
END;
5. The result of this LOV is shown in Figure 4. The first time through only ORGs with no parents will be displayed (Top of the Tree) (Figure 4).
Figure 4 - Top Level LOV
6. Double-Clicking on an ORG will display the Parent as a selectable Value + the “Back” option + All the Children, shown in Figure 5.
Double-Clicking on an ORG will display the Parent as a selectable Value + the “Back” Option + All the Children.
· 1. Double-Clicking on the Parent will return that value to the Form.
· 2. Double-Clicking on <Back> will show the prior LOV.
· 3. Double-Clicking on a Child will display its children; etc.
Figure 5 - Second Level LOV with all three Actions
This same approach can be used to call other LOVs, perform other tasks (i.e., add a new reference value) or change the where clause.
About the Author
I. Michael Snyder is a Principal and the Director of Product
Development at Dulcian, Inc. His 20
years of systems development experience includes Oracle Designer, Oracle Forms,
Reports, Oraterm, Web Server, PL/SQL and Database Administration Michael taught Database Concepts at the