Oracle Forms is has it default records filter, which we can use through Enter Query mode to specify some search Criter IA or to filter records before display, but can also create your own filter, which can is more user friendly and easy To use. In this example I has created a form based on SCOTT's EMP table, below is the screen shot of this form and I am SH Aring also the form source code FMB file with scott.emp table script which can is downloaded with the following link prequ ery_filter.zip Created The Drop downs and one text item to specify search criteria and populating thes E drop downs on When-new-form-instance trigger, the following are the code written in it: DECLARE Rg_na me VARCHAR2 (+): = ' dyngrp '; rg_id Recordgroup; errcode number; BEGIN * * * * Sure group doesn ' t already exist */ rg_id: = Find_grOUP (Rg_name); /* * * IF It exists then delete it first and then re-create it. * * IF not Id_null (rg_id) then delete_group (rg_id) ; END IF; rg_id: = create_group_from_query ( & nbsp; 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 and then re-create it. * * IF not Id_null (rg_id) then Delete_GROUP (rg_id); END IF; rg_id: = create_group_from_query ( & nbsp; 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 in scott_emp block to modify the "Where Clause" of this block at run time and following Is the code: DECLAREVwhere varchar2 (+): = ' empno is not NULL '; Begin--Build WHERE clauseIf:fltjob is isn't null then Vwhere: = Vwhere | | ' And job =: Fltjob ';End If;If:fltdept is isn't 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 are the code written in when-button-pressed trigger : Go_block (' scott_emp '); Execute_query;Note:run The SQL script first to create the table with 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
