17 September 2014

Multirecord Master-Detail Report

A common requirement is to show a Master-Detail Report where both the Master as well as the Detail Report show multiple records. When you click on one of the Master records, the connected detail records are shown as well.
This is very easy to accomplish with a Hidden item and a Dynamic Action.
First the Master Report, for this example I am using the DEPT table:
select d.deptno
  from dept d
The detail report will consists of the employees which belong to the DEPT record which is clicked by the user.
select * from emp
 where deptno = :P9_DEPTNO
You will notice that there is a reference to P9_DEPTNO in the query, that will be the hidden item.
So there are two reports and a hidden item on the page.
The user will need to click on something, so the DEPTNO column from the first report will act as a link. Navigate to the column and fill in the section labelled "Column Link".
For the Link attributes fill in the following information:
onclick="return false;" class="show-employees" id="#DEPTNO#"
Now all components are in place, time to create the Dynamic Action.
The Dynamic Action will respond to a click on the master report(DEPT). The link column has a class attribute of "show-employees" which act as the jQuery Selector. Fill in the details when the Dynamic Action needs to fire as follows:
What does the Dynamic Action need to do? First it needs to set the value of the clicked DEPTNO in the hidden item. This can be done with a little bit of javascript:
apex.item( "P9_DEPTNO" ).setValue( this.triggeringElement.id );
The second part of the Dynamic Action is to refresh the Employees report. Add a TRUE action to the Dynamic Action where you specify "Refresh Region" and choose the details report (Employees).
You can find the demo right here.

29 August 2014

5 minutes: Grant Role to Package

The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. Five minutes is not a whole lot to tell the story of PL/SQL security enhancements in 12c.

This blogpost will cover my five minutes of fame.

The thing that I will cover is Code Based Access Control (or actually only a small example where you grant privileges to a package).

1. Setting the stage

For this example I will use Schema A which will contain two tables (T1 and T2) and a package (PKG).
The structure of the tables is irrelevant, and so is the actual implementation of the package.

2. The Package's Objective

The actual implementation of the package is not really relevant, what is important to know is that it would query table T1 and would manipulate some data of table T2. However it should work on schema A's table T1 and on the calling schema's table T2.
The reason that schema A has a table T2 is that it would be impossible to compile the package if table T2 was missing. Table T2 only acts as a template table in schema A.
The calling schema should have a table named T2 in order for the package to be able to work.

note: the calling schema is the schema which would call the package that schema A has defined.

The package (located in schema A) should therefor be compiled with Invoker Rights (authid current_user) as opposed to Definer Rights (authid definer).
The reference to table T1 should be fully qualified (a.t1) and the reference to table T2 should not be fully qualified (t2).
Because of the Invoker Rights specification on the package PKG the table T2 of the calling schema is used. And because of the full qualification of table T1 (a.T1) the correct table is referenced (the one in schema A).
For a calling schema to be able to use this package (PKG) - let's assume the calling schema is called B - EXECUTE privileges need to be granted to schema B

grant execute on pkg to b;

3. The Old way (< 2013) and the flaw

Before the release of Oracle 12c, only granting execute privileges on package PKG to schema B would not be sufficient.
When invoking the package:

there would be an exception raised because of the reference to table T1 which resides in schema A.
ORA-00942: table or view does not exist
What you needed to do was to grant privileges on schema A's table T1 as well.
grant select on t1 to b;
Now all works fine. The package can be executed from schema B, and they lived happily ever after.
So, what is the security issue here?
Not only can the package be executed from schema B, the table T1 (in schema A) can also be queried directly without using the package. What if there are columns in table T1 that contain sensitive information?

4. Fixing the flaw

The solution is to grant privileges to the package and not to the user.
First of all: revoke the select privileges on schema A's T1 from schema B

revoke select on t1 from b
Next create a role and grant the select privileges on schema A's table T1 to that role. What you couldn't do before was grant a role to a package, but now you can.
create role only_this_role;

grant select on a.t1 to only_this_role;

grant only_this_role to package a.pkg;
And that's it.
The execute privileges on the package still exists of course otherwise schema B wouldn't be able to execute the package.
Table T1 (in schema A) can not be called directly by schema B any longer.
Now only the package can make calls to table T1.
This adheres to the concept of least privileges.

27 August 2014

Order, Order.. Sorting Happens Last

While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.

When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that sorting really happens as the final step.

Oracle 12c made some enhancements in the Subquery Factoring clause. Specifically now it is possible to create Functions and Procedures in the WITH clause.
Something like the following:

SQL> with
  2     function formatname (p_name in varchar2)
  3        return varchar2
  4     is
  5     begin
  6        return initcap (p_name);
  7     end formatname;
  8  select ename
  9        ,formatname(ename)  formatted
 10    from emp;

---------- -------------------------
SMITH      Smith
ALLEN      Allen
WARD       Ward
JONES      Jones
MARTIN     Martin
After the WITH keyword (first line) a simple function is defined (lines 2 - 7). This function is used in the SELECT part of the query (line 9).

When you want to include a named query in the WITH clause, it has to be after the Procedures and Functions that you have defined.
In the following query, I included a named query called ordered_emps which is a resultset of the EMP table sorted by ENAME.
SQL> set serveroutput on
SQL> with
  2    procedure show (p_what in varchar2)
  3    is
  4    begin
  5      dbms_output.put_line ('input is: '||p_what);
  6    end show;
  7    function formatname (p_name in varchar2)
  8      return varchar2
  9    is
 10    begin
 11       show (p_name);
 12      return initcap (p_name);
 13    end formatname;
 14  ordered_emps as
 15    (select ename from emp order by ename asc)
 16  select ename
 17        ,formatname(ename) formatted
 18    from ordered_emps
 19  /
---------- -----------
ADAMS      Adams
ALLEN      Allen
BLAKE      Blake
CLARK      Clark
FORD       Ford
JAMES      James
JONES      Jones
KING       King
MARTIN     Martin
MILLER     Miller
SCOTT      Scott
SMITH      Smith
TURNER     Turner
WARD       Ward

14 rows selected.

input is: SMITH
input is: ALLEN
input is: WARD
input is: JONES
input is: MARTIN
input is: BLAKE
input is: CLARK
input is: SCOTT
input is: KING
input is: TURNER
input is: ADAMS
input is: JAMES
input is: FORD
input is: MILLER

After the WITH keyword, a procedure (SHOW) is defined which acts as a wrapper for DBMS_OUTPUT.PUT_LINE (lines 2 - 6). Next a function (FORMATNAME) is defined which calls the SHOW procedure with the given input and formats the name to INITCAP (lines 7-13). Next a named query is defined ORDERED_EMPS (lines 14 - 15), and finally the "real" query is specified calling the FORMATNAME function.

Because SERVEROUTPUT is turned on, you can see the results as produced by the query as well as the calls to the SHOW procedure.
The resultset by the query is sorted based on the ENAME as we expect, the calls to the function (and procedure) happen as the data is fetched from the table in no particular order which can be seen in the output from DBMS_OUTPUT.

28 July 2014

APEX 5: Using Font Awesome Icons in Report

APEX 5 is currently in Early Adaptor 2, so the exact implementation of this blogpost might change when APEX 5 goes GA.
Font Awesome is standard included with APEX 5 and you can use the icons on buttons, there is a special property for that.
I was playing around the other day and I wanted to include some Font Awesome icons in my report.
First create a report (classic or interactive, whatever you want) and use the following query:
select empno
      ,case mod (rownum, 2)
       when 0 then 'male'
       when 1 then 'female'
       end gender
  from emp
Because the EMP table doesn't have a gender column, I decided to create one using a CASE statement. Some are "male" and some are "female".
Navigate to the GENDER column.
With the GENDER column highlighted, move your attention to the right side of the Page Designer and focus on the Properties panel.
In the section labelled "Column Formatting", enter the following for "HTML Expression":
<span class="t-Icon fa-#GENDER#"></span>
The names of the font awesome icons always start with "fa-", so this is prefixed to the content of the GENDER column. The result of a "female"-rows will be
<span class="t-Icon fa-female"></span>
For "male"-rows it will be
<span class="t-Icon fa-male"></span>

And that's it.
The report will look like the screenshot below. As you can see there are icons for the males and the females in the EMP table.

01 July 2014

APEX_ESCAPE, a new (and better) way of HTF.ESCAPE_SC

Last week, at the yearly ODTUG Kscope Conference, I did my presentation "Getting Started with APEX Plugin Development". After the session Patrick Wolf, Principal Member of Technical Staff for APEX, pointed out an improvement that could be made.
In the presentation I point out the need to escape the input that you get from a user of the plugin in order to protect the plugin from unwanted use, like SQL Injection, Cross Site Scripting and the like.
In the example plugin that is created in the presentation, I use HTP.ESCAPE_SC to escape the special characters (hence the name _SC). There is a newer and better method to escape the special characters.
By default the extended level of escaping is enabled, but this can be overridden (for whatever reason).
To illustrate both the extended and the basic level of escaping, the examples below set the level explicitly.
SQL> begin
  2     apex_escape.set_html_escaping_mode (p_mode => 'E');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sys.htf.escape_sc ('hello &"<>''/') htf
  2       , apex_escape.html ('hello &"<>''/') escape
  3    from dual
  4  /

HTF                            ESCAPE
------------------------------ ----------------------------------------
hello &amp;&quot;&lt;&gt;'/    hello &amp;&quot;&lt;&gt;&#x27;&#x2F;
With the extended level of escaping, the forward slash and the single quote are escaped as well.
When you set the escaping level to Basic (example below), you will get the same results as if you were using HTF.ESCAPE_SC.
SQL> begin
  2     apex_escape.set_html_escaping_mode (p_mode => 'B');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sys.htf.escape_sc ('hello &"<>''/') htf
  2       , apex_escape.html ('hello &"<>''/') escape
  3    from dual
  4  ;

HTF                            ESCAPE
------------------------------ ----------------------------------------
hello &amp;&quot;&lt;&gt;'/    hello &amp;&quot;&lt;&gt;'/


Documentation on APEX_ESCAPE

26 June 2014

ODTUG Kscope 2014: Wednesday

The morning started nice, ODTUG organized breakfast with country themes. There were tables for Australia, Canada and The Netherlands. If you are Dutch you might have expected to have hagelslag or cheese, this was not the case. It was a nice and hearty American style breakfast, eggs, bacon, potatoes.
The first session of the day that I went to was by Nathan Catlow on Oracle APEX Security, an interesting topic.
Nathan pointed out that by far the most common security has to do with Cross Site Scripting (XSS). This can lead to data protection issues, account compromise and attack of other applications.
Regarding injection attacks, Nathan pointed out that substitution variables (&P...) in comments are also prone for Injection attacks.
Very good advise to upgrade to at least APEX 4.2.1. There are vulnerabilities in the APEX framework which are fixed in this release.
The next session was another one on APEX Security by Tim Austwick, this time with a focus on SQL Injection.
Lots of practical information regarding SQL Injections. After listening to this, it makes you wonder how secure applications are. On the other hand it is good to known that I implement loads of their advise already. :)
"Pins Polygons and Perspectives: Visualizing Geographic Data in APEX" by Christoph Ruepprich was next.
One of the mapping apis that I never heard of was LeafletJs. Looks really nice, yet another thing to put on my to-do list.
After lunch I attended Jonathan Lewis' session on the Cost Based Optimizer for Developers. The session was very well attended and the content was superb.
According to Jonathan Oracle must obey your index hints, but only if you get it absolutely correct. If you tell the wrong path, you left out information (hint missing) or if you tell Oracle to do something "illegal" than Oracle will not follow your hints.
John Scott did a presentation about NodeJs. You can expect to see a lot of demos when John does a presentation and this time was no exception. Besides the installation of NodeJs, he also installed node-oracle for the connection to the database.
Demos included Grunt, Mail-listener2, Officegen and pdfkit. In one word: awesome.
The last session of the day: Dimitri Gielis on his way of developing APEX applications. A very useful tip that Dimitri shared was to create a template application so you can have a nice starting point for the application. You define the basic building blocks (like global page, lists, administration page, include font-awesome) and export the application. Then go to the internal workspace and add this application as template application.

Tonight there is the traditional party, this time it will be at the Seattle EMP (experience music project). Just realized that there is an Oracle link there... (emp as the table in the Scott demo schema - just the geek in me I guess). ODTUG has a reputation to uphold regarding the parties, so I expect a lot from it.

25 June 2014

ODTUG Kscope 2014: Tuesday

Mark Drake, the product manager for XMLDB and the new JSON features in the database, started Tuesday with a session on flexible storage.
After a short history of the XMLDB ("more than just a LOB Store"), he went to the heart of the presentation: flexible storage.
In the upcoming release of the database there will also be JSON functionality built in. The JSON functionality won't have a separate datatype. By not introducing a separate JSON datatype the implementation of replication and high availability won't be as hard to implement as with the XMLType. On the other hand, it is harder for the API to figure out which datatype it should project to.
There are several ways to implement flexibel storage, like:
  1. flex-fields
  2. document persistence
  3. name value pairs
With flex-fields you have no way of knowing the content of the data by looking at the datamodel. By using document persistence you would implement this with content stored as XML or JSON.
Some problems can be solved when you represent name value pairs as XML. This solution was described in detail, including indexing strategies.
Right before lunch I did my session "Getting started with APEX Plugin Development". There were about 45 people in the room and I think the session went allright. After the session I had lunch with Patrick Wolf and we were going over some of the different aspects of the plugins and he gave me a few good pointers. More stuff to play with and figure out how they work.
OTN (Oracle Technology Network) sponsored a lunch and learn session. In a packed room there were some very good questions and answers.
Dietmar Aust covered some "small" features of APEX 5. This time the "Page Designer" was not the main focus of the presentation, which was a big part in other sessions on APEX 5. Instead Dietmar discussed the change in the export functionality, the different ways modal pages can be created and session joining, just to name a few.
Not new in APEX 5, but might be useful: APEX_MAIL.GET_IMAGES_URL and APEX_MAIL.GET_INSTANCE_URL. Go find more about this in the documentation.
Instead of using the v-function, use the context functions like SYS_CONTEXT ('APEX$SESSION', 'app_user') to get information about the APEX context.
There is also a extended method to escape substitution variables, e.g. &P16_EMP_NO!JS. He promised that he will blog about it.
There is a new APEX_ZIP package which is based on the AS_ZIP package, created by my former colleague Anton Scheffer. There will also be an APEX_JSON package with lots of functionality.
Finally there is an alias APP_PAGE_ALIAS, long overdue.
Next up Peter Raganitsch on things you can learn from the Packaged Applications which are created by Oracle. A lot of very nice plugins are hidden inside these packaged applications.
Peter also demonstrated a method of installing packaged applications using the command line as opposed to using the wizards. Unfortunately using the command line doesn't install the supporting objects, so the application doesn't work. This still needs to be done using the regular method in the builder. So how did he got it to work? He imported the APEX Builder (which is just an APEX application after all) and found the wizard which install the supporting objects.... just don't do it, there are some undocumented API's being used.
As there are a lot of plugins (around 50) in the packaged applications, Peter also demonstrated a way to export these by utilising the java command line tools (APEXExport and APEXExportSplitter) to export and split the applications.
The final session of the day that I attended was by David Mann: "Time for some New Graphs: Incorporating Time and Animation Elements in your Web App Visualizations".
David showed different types of visualisation, some very funky ones, some very useful. He showed some demos using D3. Personally I had never heard of D3, so definitely something to play with in the near future (at least I hope near future).
At night the Oracle ACE program organized an ACE Dinner. It was at the Pike Brewery, needless to say there was some beer involved with a very nice meal.