APEX: Why using HTML as part of SQL queries for Interactive Report can be dangerous?

Why using HTML as part of SQL queries for Interactive Report can be dangerous? Look at the screenshots! Careless use of HTML can cause problems that are not obvious at first glance:







Problem 1: From a business user’s point of view, the filtered lines do not contain the word “default” (this word is contained within an HTML tag)

Problem 2: When filtering by status, instead of user-friendly text, it receives an HTML expression that looks like an abracadabra to it.

')





Export also looks awful.

How best to fix everything?





First, pay attention to the separation of the business logic layer and the presentation tier layer. In the layer of business logic there should be nothing related to the display of information, especially HTML.



First, take a look at the original query in Interactive Report.







The “v_cost_report” view returns the status column as HTML. Using HTML in views is a bad practice, as it makes it difficult to further use views in other views and PL / SQL procedures, especially when trying to fetch or link to other tables using this column.

For correct division of logic into layers, it is necessary to put an expression that returns HTML to the Apex level.

Before CREATE OR REPLACE FORCE VIEW v_cost_report AS SELECT p.id, p.project, p.task_name, '<img class=”default_report_img” src=”/i/'|| decode(p.status_no,1,'wwv_help3', 2,'wwv_light', 3,'wwv_logout', 4,'wwv_notes', null)||'.gif”/>' status, p.assigned_to, p.status as status_desc, p.cost, p.BUDGET FROM eba_demo_ir_projects p;

After CREATE OR REPLACE FORCE VIEW v_cost_report AS SELECT p.id, p.project, p.task_name, p.status_no status, p.assigned_to, p.status as status_desc, p.cost, p.BUDGET FROM eba_demo_ir_projects p;

The apex belongs to the presentation tier, so there are no such strict restrictions at this level. HTML in SQL queries can be used to a limited extent; however, it is best to avoid directly using similar return HTML columns in Interactive Reports. These columns should be hidden, and the results returned by them should not be shown directly, but using the “HTML Expression” property in other columns.

This means that we need 2 columns instead of one, the first for HTML and the second for a text description.







In our example, the “Status” column returns a number (ID) that identifies the corresponding status. We use LOV (list of values) to show a text description instead of IDs that will be used for searching, filtering and exporting.

Another column, “STAT_IMAGE”, will be used for the image corresponding to each status.

We can simply use DECODE or CASE in SQL to select the desired image, but it is more correct to use a static list (Static LOV), especially with a large possible number of values. Using the convenient table list editor (Grid Edit), their creation and editing is greatly simplified.







Another advantage when using static lists is manifested in the possibility of using the function of subscribe (Subscribe), which is a variant of inheritance and allows you to easily synchronize changes between web applications.

In our example, the static list STATUS_IMAGES consists of 4 elements.

To use the list of values, we will add the function call APEX_ITEM.TEXT_FROM_LOV to the SQL query.







Now you need to set the necessary properties of the column “STAT_IMAGE”.

First you need to change the Display Type property of the column to Hidden. Thus, that column will not be displayed or exported. Search by value of this column is now also prohibited.







Now let's work on with the “Status” column.

This column should be displayed on the screen. To display a text description instead of the status code, change the Display Type of the column to Display as Text (based on LOV, escape special characters) .

The list of values ​​STATUS_DESCRIPTION contains the following statuses:







Now the column shows textual descriptions of statuses, but we need pictures instead. Let's use the great functionality of HTML Expression, which appeared in Apex 4.1. We can use the template #NAME_FOLUMN # to display the values ​​of both the current and other columns in the HTML expression.







Here in the example, we use HTML Expression to display the value of another column in the column, “STAT_IMAGE” . An additional title tag is needed to display a tooltip with a description of the status.

Pure HTML and # COLUMN # -syntax looks and reads much better than when used inside SQL, in addition, there is no need for additional quotation escaping.

HTML Expression is used only for text and HTML output to the browser. For searching, filtering in drop-down lists uses original column values .







Export is also performed correctly.







The result: the correct design of WEB-applications, the separation between the layers and the correct use of the functionality of Apex can improve the life of the developer and avoid many hidden potential errors when searching, exporting, filtering, etc. Do everything right at once, and it will be reckoned to you ...