首页 > 代码库 > Define Custom Data Filter Using Pre-Query Trigger In Oracle Forms
Define Custom Data Filter Using Pre-Query Trigger In Oracle Forms
Oracle Forms is having its default records filter, which we can use through Enter Query mode to specify some search criteria or to filter records before display, but you can also create your own filter, which can be more user friendly and easy to use.
In this example I have created a form based on SCOTT‘s Emp table, below is the screen shot of this form and I am sharing also the form source code FMB file with Scott.Emp table script which can be downloaded with the following link Prequery_Filter.Zip
Created two drop downs and one text item to specify search criteria and populating these drop downs on When-New-Form-Instance trigger, the following is the code written in it:
DECLARE
rg_name VARCHAR2 (40) := ‘DYNGRP‘;
rg_id RecordGroup;
errcode NUMBER;
BEGIN
/*
** Make sure group doesn‘t already exist
*/
rg_id := FIND_GROUP (rg_name);
/*
** If it exists then delete it first then re-create it.
*/
IF NOT ID_NULL (rg_id)
THEN
DELETE_GROUP (rg_id);
END IF;
rg_id :=
CREATE_GROUP_FROM_QUERY (
rg_name,
‘select DISTINCT job, job job1 from scott_emp order by 1‘);
/*
** Populate the record group
*/
errcode := POPULATE_GROUP (rg_id);
CLEAR_LIST (‘FLTJOB‘);
POPULATE_LIST (‘FLTJOB‘, ‘DYNGRP‘);
------- populate for department
rg_id := FIND_GROUP (rg_name);
/*
** If it exists then delete it first then re-create it.
*/
IF NOT ID_NULL (rg_id)
THEN
DELETE_GROUP (rg_id);
END IF;
rg_id :=
CREATE_GROUP_FROM_QUERY (
rg_name,
‘select DISTINCT TO_CHAR(deptno), TO_CHAR(deptno) deptno1 from scott_emp order by 1‘);
/*
** Populate the record group
*/
errcode := POPULATE_GROUP (rg_id);
CLEAR_LIST (‘FLTDEPT‘);
POPULATE_LIST (‘FLTDEPT‘, ‘DYNGRP‘);
GO_BLOCK (‘SCOTT_EMP‘);
EXECUTE_QUERY;
END;
Then written the Pre-Query on Scott_Emp block to modify the "Where Clause" of that block at run time and following is the code:
DECLARE
VWHERE varchar2(1000) := ‘empno is not null ‘;
begin
-- build where clause
if :fltjob is not null then
vwhere := vwhere || ‘and job = :fltjob ‘;
end if;
if :fltdept is not null then
vwhere := vwhere || ‘and deptno = :fltdept ‘;
end if;
if nvl(:fltsal,0) > 0 then
vwhere := vwhere || ‘and sal >= :fltsal ‘;
end if;
set_block_property(‘scott_emp‘, default_where, vwhere);
end;
Created a Push Button to execute query in Scott_Emp block and following is the code written in When-Button-Pressed trigger:
go_block(‘scott_emp‘);
execute_query;
Note: Run the SQL script first to create the table in your current schema before running the form which I provided in source code Prequery_Filter.zip.
Define Custom Data Filter Using Pre-Query Trigger In Oracle Forms
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。