Export Oracle RAS (Real Application Security) 

I made a small APEX Application to export RAS-objects to SQL-File.
It is free and has MIT-License. You can download RAS Export from GitHUB. 

Any feedback is welcome.

I'm a Speaker!

See my presentation RAS in APEX 5 ist einfacher als Sie denken here.

Oracle Real Application Security (RAS) in APEX 5

      

       Eine gute Gelegenheit mit anderen APEX-Developer sich  auszutauschen.

Thanks Tom, Graham and Andrew for a wonderful, successful event!

Why using of HTML in SQL-Query Interactive Reports to show styled cells can be dangerous

Why using of HTML in SQL-Query Interactive Reports to show styled cells can be dangerous? 

Just look at these screenshots! Careless using of HTML can result in problems which are non-obvious at first blush. I just filter IR with wort "default" and choose status "yellow light" from drop-down select list on Status column .

With HTML in SQL-Query you become problems when using Interactive report out-of-the-box.
 
Fail 1: From business user’s point of view filtered rows don’t contain the word “default” (this word is situated inside HTML-tag)
Fail 2: Status = “abracadabra – HTML is unclear for common user”

While filtering by status user receives a statement looking like “abracadabra” instead of text comprehensible for him 

“Export” also looks terribly.

Are there any appropriate solution in this case? Yes, of course.
 
At first I have to pay attention to segregation of “business logic” tier from “presentation” tier. It should be nothing related to information displaying, especially HTML, in business logic tier.

How can we improve this report?

Let’s have a look at Interactive Report Source 

View “v_cost_report” returns status column as HTML. You should avoid of using HTML in views, because it significantly impedes using of this view in another views and PL/SQL procedures, especially when you need reference or filter against HTML- comprising column. Different HTML-producing functions such as apex_item.checkbox, apex_item.textarea etc. should be avoided in views too.

To improve tiers segregation we should remove HTML-producing expression from the view and add it to APEX.

Befor	After
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;	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;

As is well-known, APEX refers to presentation tier and there are no such strong limitations in it. HTML can be used in SQL-queries to a limited extent, but the best practice is to avoid immediate using of HTML-producing columns in Interactive Reports. Such columns should be hidden, and their results have to be shown indirect in other visible columns using “HTML Expression” property.

This implies we need two columns instead of one, the first one - for HTML and second one- for the text description.

 In our example “Status” column returns the number (ID) that identifies the certain status. We use LOV (List of Values) feature to show a text description, used for searching, filtering and exporting instead of ID showing.

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

We can simply use DECODE or CASE clause in SQL to choose a certain image, but using Static LOV is more correct way in this case, especially in event of great quantity of possible values (status-images). Using of convenient Grid Edit feature significantly simplifies items creating and editing.

Another advantage of LOVs in this case is the possibility of using Subscription-feature, which represents inheritance option and capable of easily synchronization of changes between the applications.

In our sample LOV with images has name STATUS_IMAGES and consists of 4 items.

To use LOV, we should add APEX_ITEM.TEXT_FROM_LOV function in our SQL-query. 

  

Now we should set the necessary properties of “STAT_IMAGE” report column.

At first we’ll change Display Type property of the column to Hidden. In such a way this column will not be displayed or exported. This operation will also exclude this column from search. 

Next we’ll adapt “Status” report column.

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

LOV STATUS_DESCRIPTION contains the following statuses description:

Now this column displays statuses text description, but we still need images displaying instead of it. To do this we’ll use the remarkable feature - HTML Expression, which appeared in APEX 4.1. We can use pattern #COLUMN # to show the values of current oder other oder both current and other columns and HTML tags in this columns.

Here in example we use property of HTML Expression to display in this column the value of another column, “STAT_IMAGE”. We need additional tag title to display a tooltip with status text description.

Pure HTML and #COLUMN# syntax looks match better and is easy to read in comparison to raw SQL-query and also in this case there is no need in additional escaping.

HTML Expression property is used only for browser displaying of text and HTML. For searching and sorting in drop-down lists the original values of this column are used.

Now export is also performed correctly.

Summary: correct WEB-applications design, segregation between tiers and proper using of APEX features such HTML Expression can improve the development and allow to avoid of many latent mistakes and troubles while using search, export, sorting etc.