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.- On page create HTML region
- 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_FieldNameExample:
P1_EMPNO P1_ENAME P1_SAL P1_DEPTNO
- 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_FROMwhere "P" is constant and "108" is PageId, "DATE_BIRTH" is field in table and "_from" and "_to" determine date range.
P108_DATE_BIRTH_TO
- 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_TYPEExample:
P1_SEARCH_TYPE
- 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 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') andwhat 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%") typeThis 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)
- 1-means search in all fields with
- 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')
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.
No comments:
Post a Comment