Query by Form (QBF) for Apex

We are all aware of very nice feature in Oracle forms called "Query by Form" where user has ability to enter several patterns which automatically generate filter in data search. From a time when I was a programmer on Apex 2.2, one of mine primary problems was to solve search capability in a as far as possible easiest way. Problem is how to generate SQL query against entered values from user input for any form and any table. Second target was a request that implementation must be easy and compatible with any present and incoming Oracle version. This is how mine QBF for Apex was invented.
So similar feature was a nice to have in Apex. In Apex 3.x there is a component "Interactive Report" with large functionality and customization and very great query capabilities. For all of you that use older version of Apex or because of some other reasons do want to use "Interactive Report", I’ll show you mine solution which I have called Query By Form which was in the time when I created an enhanced version of QBF from Oracle web site.

Implementation

The main idea was to make a solution that can automatically and quickly be implemented on any Apex page.

1. On page create HTML region
2. In that region place "Edit Text” controls which will represent filter for query. Each "Edit Text" control must have name in UPPER CASE and named in a form:
   
   P+PageId_FieldName
   
   Example:
   
   P1_EMPNO P1_ENAME P1_SAL P1_DEPTNO
   
3. According that, QBF can search through page (defined with PageId) and look if there is a control with such a name. If exists, QBF knows that this control define value for searching that Field in Table/Viev and put in search part of resulting query. So names standard is need that function can search through the form and look for certain controls.
   For date fields range can be used. For such fields field items on WEB page name must have "_to" and "_from" suffixes. Order of WEB items is not important!
   Example:
   
   P108_DATE_BIRTH_FROM
   P108_DATE_BIRTH_TO
   
   where "P" is constant and "108" is PageId, "DATE_BIRTH" is field in table and "_from" and "_to" determine date range.
4. In HTML region, where previous controls are placed, one "Radiogroup" control must exist. It’s purpose is to define the way patterns will be used in query. Name (in UPPER CASE also) must be defined as
   
   P+PageId_SEARCH_TYPE
   
   Example:
   
   P1_SEARCH_TYPE
   
5. Place a report based as "SQL Query (PLSQL function body returning SQL Query)" (usually bellow the HTML region with search condition). In "region Source" place following code:
   
   declare
     l_sql varchar2 (32567 char);
   begin
     l_sql := qbf (
            ln_page_id=>:APP_PAGE_ID
           ,ls_table=>'EMP'
           ,ls_select=>'SELECT EMPNO   
                    ,ENAME
                    ,JOB
                    ,MGR
                    ,HIREDATE
                    ,SAL
                    ,COMM
                    ,DEPTNO'
           ,ln_search_type=>:P1_SEARCH_TYPE
           ,ls_date_format=>'DD-MON-RR');
     return l_sql;
   EXCEPTION
     WHEN OTHERS THEN
       -- some logging here?
       RAISE;
   end;
   

You can also try live example of QBF in action on Apex demo web page. The same application can be downloaded as an export Apex file.

How it works

Regardless function has 7 parameters only first two are required:

create or replace FUNCTION QBF (
        ln_page_id     NUMBER
      , ls_table    IN VARCHAR2
      , ls_select   IN VARCHAR2 DEFAULT NULL
      , ls_where    IN VARCHAR2 DEFAULT NULL
      , ls_order_by IN VARCHAR2 DEFAULT NULL
      , ln_search_type IN NUMBER DEFAULT 3
      , ls_date_format IN VARCHAR2 DEFAULT 'DD.MM.YYYY'
      , ls_scope_mode IN VARCHAR DEFAULT 'U'
);

• ln_page_id-Apex page number where report is placed
• ls_table-name of query table/view which is defined with ls_scope_mode parameter
• ls_select-which fields you want to show in report. If is null all fields from ls_table in definition order are shown
• ls_where-initial where part which is not compound automatically from the page but you want to have it in query. Example:
  
  where userid IN ('USER_A', 'USER_B') and
  
  what would at the end produce return WHERE part as:
  
  where userid IN ('USER_A', 'USERB') and P1_EMPNO like ‘9%’
  
  where P1_EMPNO like ‘9%’ part was generated by QBF from field from Apex page
• ls_order_by-field names which define order part of resulting SQL
• ls_date_format-date format in which query will be performed
• ln_search_type-how patterns are understood
  
  • 1-means search in all fields with
    
    UPPER("%search_value%") type
    
    This is default option
  • 2-Start with defined pattern
    
    UPPER("search_value%") type
    
  • 3-Original Oracle based search which uses SQL search characters ("%" and "_") and sample is as is (no upper values are used)
• ls_scope_mode-represent which dictionary view will be queried for finding source field names. Possible values are "U" (define USER_TAB_COLUMNS view–default) and "A" (define ALL_TAB_COLUMNS view)
  Code for that checking is very easy:
  
  FOR x IN (
       SELECT column_name, data_type FROM USER_TAB_COLUMNS WHERE table_name = UPPER(ls_table) AND ls_scope_mode = 'U'
     UNION
       SELECT column_name, data_type FROM ALL_TAB_COLUMNS WHERE table_name = UPPER(ls_table) AND ls_scope_mode = 'A') 
  

When user enter some search patterns and click on submit button, these values are passed to server where QBF function process them. QBF according previously explained names rules, search on whole Apex page for existence of such an field name. If it finds it, it place it in where condition according ln_search_type parameter.
After queering on all possible fields finally function adds (if is anything to add) all passed parameters that define on SQL sentence which come after "where" part.

Benefits

• Acclimatization especially generating on many forms. Less inconsistency on presentation part. Pages are very standardized and looks the same. Easy learning curve for end user.
• As you can see implementation is pretty straight forward and I was able to create more complex WEB pages (http://www.skdd.hr/portal/f?p=100:23 or http://www.skdd.hr/portal/f?p=100:41) in less then 5 minutes.
• If generated query is not OK (parsing is happened on client side only), in QBF function source code there is already implemented part which in debug mode prints some additional information which will help you to solve the problem, quicker. Look for V('DEBUG') = 'YES' part in QBF source.
  
  
• QBF method is bug free and with some extra coding you can prevent any SQL error.
  This can be done by adding IsSQLValid function which can be implemented in "Conditional Display" as main method to run Report (for true results) or show some message region (for false as a result).
  In this example this function doesn’t exists so function IsSQLValid will be explained in my next post IsSQLValid function-check your queries before you run them in a few days.
• Because report is based on data got from server side some additional security is implemented in the same way.

Deficits

• Only on standard column types (date, number/integer, varchar2 and char) are supported.
• Only "Edit Text" and Combox controls can be used as input values for query (no radiogroup or other type are not allowed in this version)
• No lookup values are possible to be used as source pattern
• Operator between all search patterns is AND.
• There is no "null" based operation possible from the user side-but can be added in WHERE part. is null or is not null are supported in combobox with -100 value which is treat as null.
• Required search condition (field that must have value to avoid full table scan) - easy to implement adding at the end of control name "XX" for required or "YY" for non required value. This is not not implemented!

Conclusion

Friend of mine, Denes Kubicek, has created on his Apex demo site his view of the same solution in a different approach.

I’m interested if anyone make any improvement in the future to notice me as well and share the Apex knowledge wider. I think QBF has potential for that.

Improvements

In the meantime I have added ls_date_format as parameter so now any date range search is 100% customizable according user date format and search condition (look at the picture)