Define Custom Data Filter Using Pre-Query Trigger In Oracle Forms

发布时间:2017-2-27 17:22:45 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"Define Custom Data Filter Using Pre-Query Trigger In Oracle Forms ",主要涉及到Define Custom Data Filter Using Pre-Query Trigger In Oracle Forms 方面的内容,对于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 
 
image
 
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.

上一篇:转:深入理解javascript原型和闭包系列
下一篇:.NET 基础 一步步 一幕幕[数组、集合、异常捕获]

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。