Oracle 12c: Temporal Validity, multiple on one table - Part Deux

One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!

In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that blog by following this link.

As you can read in that blog it is possible to create multiple Validity Periods on one table, but only via an ALTER TABLE statement.

That kept me wondering whether what I was doing was supported and what would be the proper way to go and create multiple Validity Periods for a single table.

Because I was still at Oracle Open World when I wrote that blog, it was easy for me to go over to the Demo-Grounds and get my answers.. However this was not as easy as it sounds.

After having finally found the correct station the gentleman I spoke to couldn't give me a satisfying answer, which made me doubt if I had indeed found the correct station. Maybe I didn't find the correct station, or maybe it was the language barriere that I couldn't explain what I wanted to know. Or maybe he just didn't know the answer... anyway, he was very kind and showed me some other things.

By chance I ran into Bryn Llewellyn, Distinguished Product Manager for PL/SQL, and talked to him about my quest to find an answer (among other things; like why I didn't join him for the Bike trip he organized right before OOW #BikeB4OOW).

Bryn suggested to contact Kevin Jernigan, who is a Senior Director Product Management for a number of products including Temporal Validity.
So, after getting back from Oracle Open World I contacted Kevin and he was very helpful.

Turns out there is no supporting syntax to define multiple Validity Periods for a single statement with the CREATE TABLE syntax.
This is stated in the CREATE TABLE section of the documentation:

You can specify at most one valid time dimension when you create a table. You can subsequently add additional valid time dimensions to a table with the add_period_clause of ALTER TABLE.

The limitation is with the CREATE TABLE syntax, not with the Temporal Validity implementation.

One final thing I asked Kevin: How about that DBMS_METADATA behaviour that I encountered in my blog?

Re: the problem with DBMS_METADA, I will check with development to see if this is a known issue (and maybe already fixed in a test environment?), and if not, we will file a bug to have it fixed.

Good to know that this issue is (going to be) addressed and that I found my answer about Temporal Validity.

Documentation Links

Create Table documentation Temporal Validity

DBMS_REDACT and complete ROW update

My session on "Oracle 12c for Developers" is done. Afterwards someone asked the question:

What happens when you use DBMS_REDACT with a complete row update?

My guess was that it would place the redacted data in the column, but I haven't tried it, so here goes:

create table emp
as
select ename
      ,to_char (abs (dbms_random.random)) credit_card
  from scott.emp
;


begin
   dbms_redact.add_policy
     (object_schema  => 'A'
     ,object_name    => 'EMP'
     ,policy_name    => 'Hide Creditcard'
     ,expression     => '1=1'
     ,column_name    => 'CREDIT_CARD'  
     ,function_type  => dbms_redact.regexp
     ,regexp_pattern => dbms_redact.re_pattern_any_digit
     ,regexp_replace_string => 'X'
     );
end;
/

First to create a test table (of course called EMP) in schema "A". Next to place a DBMS_REDACT policy on it which replaces the credit_card information with X for each digit.
Just to verify that it works:

SQL> select *
  2    from a.emp
  3  /

ENAME      CREDIT_CARD
---------- --------------------
SMITH      XXXXXXXXXX
ALLEN      XXXXXXXXX
WARD       XXXXXXXXXX
JONES      XXXXXXXXX
MARTIN     XXXXXXXXX
BLAKE      XXXXXXXXXX
CLARK      XXXXXXXXXX
SCOTT      XXXXXXXXX
KING       XXXXXXXXXX
TURNER     XXXXXXXXXX
ADAMS      XXXXXXXX
JAMES      XXXXXXXXX
FORD       XXXXXXXXX
MILLER     XXXXXXXXX

14 rows selected.

Now for the row update without actually changing any data.

SQL> declare
  2 l_emp a.emp%rowtype;
  3  begin
  4 select ename, credit_card
  5   into l_emp.ename, l_emp.credit_card
  6   from a.emp
  7   where ename ='SMITH';
  8 update a.emp
  9    set row = l_emp
 10 where ename = 'SMITH';
 11  end;

PL/SQL procedure successfully completed.

The code above will update the information for SMITH without altering the data.
And now to unveil the actual data which is in the table, first remove the redaction

SQL> begin
  2    dbms_redact.drop_policy (object_schema => 'A'
  3          ,object_name => 'EMP'
  4          ,policy_name => 'Hide Creditcard');
  5  end;
  6 /

PL/SQL procedure successfully completed.

and inspect the data:

SQL> select ename
  2        ,credit_card
  3    from a.emp
  4  /

ENAME      CREDIT_CARD
---------- --------------------
SMITH      XXXXXXXXXX
ALLEN      146486740
WARD       1079838967
JONES      204239028
MARTIN     784659193
BLAKE      2086063983
CLARK      1949626638
SCOTT      736597519
KING       1541186772
TURNER     1456281762
ADAMS      61726886
JAMES      729938493
FORD       238314859
MILLER     714890479

14 rows selected.

And there you have it.. looks like we lost some information along the way.

"Busy Button" with APEX5, jQuery and Font Awesome

Both jQuery and Font Awesome are standard included with APEX5 (still in early adopter). With a little bit of jQuery you can create an animated button that reflects that it is doing something in the background.
For this example I created a button "Text + Icon button". Simply drag and drop this in the Page Designer.
The Icon CSS Class: "fa-play-circle-o"
and the action: "Defined by Dynamic Action"
For the Dynamic Action: it should respond to the button click (of course)
Choose: Execute Javascript and enter:

$(this.triggeringElement).prop('disabled', true)
                         .children('span')
                         .toggleClass('fa-repeat')
                         .toggleClass('fa-spin')
                         .toggleClass('fa-play-circle-o')
                         ;

Add another TRUE action to the Dynamic Action, for my example I'll use "Execute Javascript" and enter:

alert ('You clicked the button, now it looks busy');

The third TRUE action for the Dynamic Action, also "Execute Javascript" will reset the button to the way it was:

$(this.triggeringElement).prop('disabled', false)
                         .children('span')
                         .toggleClass('fa-repeat')
                         .toggleClass('fa-spin')
                         .toggleClass('fa-play-circle-o')
                         ;

See an example here: Early Adopter APEX; login with "demo" and password "demo".

Dynamic Action in Report - APEX5 version with Font Awesome

Almost two years ago, I wrote a little blog on how to trigger a Dynamic Action from a report. You can find that blog right here.
Things have changed with APEX5 (which is currently still in "early adopter 2") which allow you to do this in a more clean way (or at least I think so). No need to create a "fake link" so the user know that the icon is clickable. No need to upload your own images, use the already shipped Font Awesome library.

Based on that old blogpost I reused the same table structure and the same PL/SQL procedure. The only change I made was to the query on which the report is based:

select id tsk_id
      ,case ind_complete
       when 'Y' then 'up'
       when 'N' then 'down'
       end ind_complete
      ,what
      ,complete_before
  from tasks

Instead of "ok" and "nok" for the "IND_COMPLETE" column, I am using "up" and "down". These names will be used to get the correct Font Awesome icon. If you want to use different icons, check the Font Awesome Cheatsheet, version 4.0.3 (the version currently used by the early adopter.

Instead of having to create a Link Column, linking to a dummy page like Page 0, the IND_COMPLETE column can stay a "Plain Text" column.
Adjust the HTML expression for that column to:

<span class="t-Icon fa-thumbs-o-#IND_COMPLETE# setComplete" id="#TSK_ID#" style="cursor: pointer;" ></span>

The class added to the IND_COMPLETE column will contain the reference to the Font Awesome icons you want to show. The class "setComplete" is there to have the Dynamic Action fire when the column is clicked. The id reference is there, so the Dynamic Action will know which ID to update in the table. Finally styling the cursor so the user will know that the icon is clickable.

Next the Dynamic Action. The Dynamic Action is basically the same as in the original version:

1. Set the value of the clicked element in a hidden item
2. Execute the stored Procedure
3. Refresh the report

As far as the last point goes, there was a comment in the original blog which suggests using the "Submit Page" action because the pagination will return to the first set. I found a plugin to do a refresh which remembers the pagination, unfortunately it doesn't play well with APEX5.

And just for fun, add some CSS styling at page level:

.fa-thumbs-o-up{
  color: #2580D4;
}

.fa-thumbs-o-down {
  color:red;
  font-size: 20px;
}

That will really make the Font Awesome icons stand out :)

I put a small demo on the Early Adoptor site.

Oracle 12c: Temporal Validity, multiple on one table

During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:

Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid.

The question was: "Is it possible to have multiple temporal valid periods for a table?"
According to the documentation definition above it should be possible to create multiple temporal valid periods. Let's try this.

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  ,period for b_valid (b_start, b_end)
  9 );
,period for b_valid (b_start, b_end)
            *
ERROR at line 8:
ORA-55603: invalid flashback archive or valid time period command

That didn't work.
But we're not done yet. Let's try something a little different:

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  );

Table created.

SQL> alter table t
  2 add period for b_valid (b_start, b_end);

Table altered.

That worked. There are now two valid period defined on table T. Maybe I got the syntax wrong, let's take a look at the Metadata and find out where I went wrong.

SQL> select dbms_metadata.get_ddl
  2     ('TABLE'
  3     ,'T')
  4    from dual;
ERROR:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1



no rows selected

Oh oh.. I think I found a little bug here.
I wonder what will happen if we add data and run some queries.

SQL> insert into t values
  2  (1, trunc (sysdate) -10, trunc (sysdate), trunc (sysdate), trunc (sysdate) +10) ;

1 row created.

SQL> insert into t values
  2  (2, trunc (sysdate), trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate))
  3  ;

1 row created.

SQL> insert into t values
  2  (3, trunc (sysdate) -10, trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate) +10)
  3  ;

1 row created.
 
SQL> commit;

Commit complete.

SQL> select *
  2    from t;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

3 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

It all seems to work fine.
So yes, you can add multiple temporal validity periods to a table but only with an alter table statement.

Links

Definition Temporal Validity

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
      ,d.dname
      ,d.loc
  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.

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:

begin
   a.pkg;
end;

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.

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;

ENAME      FORMATTED
---------- -------------------------
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  /
 
 ENAME       FORMATTED
---------- -----------
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.

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
      ,ename
      ,job
  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.

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;'/

Links

Documentation on APEX_ESCAPE

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.

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.

ODTUG Kscope 2014: Monday

Monday morning started with the opening keynote. At the same time the Dutch soccer team played Chile (which the Dutch won with 2-0). A small Dutch delegation went to watch the game, all dressed in orange, I was one of them.
During the keynote the location of Kscope 2015 was announced: Hollywood, Florida.
Lunch was special, there were lunch and learn. There were "reserved" tables were you could sit on and talk tech with ACE Directors. We had some nice discussions at the table that I sat on, including the soccer game.

After lunch I went to see a session by Kris Rice on the Oracle Rest Data Services (formely known as the APEX Listener) Best Practices and Features.
The first (and the last) thing he pointed out that should must change the following settings:

1. Configure database connection pool
2. set max size
3. set initial size
4. set timeouts

Kris also spend some time on ICAP which stands for Internet Content Adaptation Protocol. This will scan all file uploads for viruses before it reaches the database. They needed that for the Oracle Cloud services.
In the APEX Listener you could use PL/SQL for URL validation, nowadays you can also use Javascript for these validations. Even though the demo didn't go as smoothly as Kris would have wanted to, the point was clear. There is no database hit, it is handled in the ORDS.
The Rest filtering option looks really interesting. With these rules you can add filtering options before you do down to the database. Definitely something I need to look into more closely, it sounds very interesting.
There is also a way to log all PL/SQL call and the bind variables, there is a sample_capture.sql (somewhere). It needs to have a certain signature which is specified in that sample script.
For version 3.0 there are a lot more features coming, like "Simplified Installation", "Client REST Filtering", "Bulk CSV Loading over REST", "Database 12.1.0.2 JSON Collections", a "New Plugin framework", and more...
The next session I went to was another one by Kris Rice, this time it was about "Creating RESTful APIs with Oracle Application Express Listener".
There are over 40 internal group at Oracle defining the REST standard. This means that if you know and understand how to interact with REST and ORDS, you also know how to interact with REST and Fusion.
After an introduction about what REST Data Services is all about, Kris continued with lots of demos including "SQL Injection as a Service".
In ORDS version 3.0 you can "REST enable" the table with a single click from SQLDev - very cool.

The final (regular) session of the day was by John Scott on "Testing APEX: removing the Boring from Testing".
He started of by comparing testing to Marmite, you either love it or you hate it.
The most fundamental of tests is Unit Test, followed by Integration Tests. Above that are Functional Tests and finally the Acceptance Tests.
John covered NodeJs, PhantomJs, CasperJs, SlimerJs and TrifleJs to help with testing. He did the demos with CasperJs.
He made a good point, and I am really interested in trying out CasperJs to do some testing.

ODTUG KScope14 Sunday APEX Symposium

The ODTUG Kscope conference always starts with a full day symposium. There are several specialized tracks going on, and I attended the APEX track (mainly).
The room was packed, I would estimate around a 175 delegates.

Joel Kallman started the day with an overview of the history of APEX, including a video of Steve Balmer being very enthousiastic about APEX. He also told a bit about the background and how some features evolved throughout the years, like Themes and lessons learned with the packaged applications.
Joel showed a couple of videos demonstating the Page Designer vs. the current wizard driven style of development. Loved the new video with the song "Everything is Awesome" to show of the productivity-boost by using the Page Designer in APEX 5.0.
Deployment also gets easier with APEX 5. Now you can associate the database objects with your application. The DDL is then generated, so you don't have to go to external tools to get the DDL.
Joel also shared a story on the usage of APEX as mission critical application. There was a large customer who experienced a production outage on a mission critical application after an upgrade. Turned out to be a database bug (outline),but it shows that APEX is being used by large organizations and in mission critical applications.
One of the last things Joel shared was that APEX will get more marketing in the future to get more companies using APEX.
Next up Patrick Wolf about the Page Designer. The demo's that Patrick did really showed how the Page Designer improves developer productivity.

After lunch Shakeeb Rahman gave some insight in the process that was taken by the redesign of APEX 5. He also spend a good amount on the Universal Theme, especially on Theme Styles and Template Options.
Without touching any HTML create a new look for your applications by using Template Options, very flexibel. Just by changing some options, you can change your applications dramatically
Font Awesome is included standard, 400 icons to choose from which can be easily incorporated. For now there is no quick-pick available, but a future release might include that as well.
All the templates and regions will be shown in a sample application,like the eight permutations of a list template. Unfortunately this sample application is not yet available.
With Theme Styles it simply a flip of a switch to change the color scheme of your application. Currently Universal Theme 42 comes with Blue (Default) and Red.
One more thing: Universal Theme Roller, and again the song "Everything is Awesome" is played.
The Universal Theme Roller works (more or less) like the jQuery Themeroller, but now on your own application. Of course Shakeeb picked Orange for the demo. The changes made in the Universal Theme Roller popup where shown live in the application (using Less) and the setting stored in the browser.
The CSS can be saved to a file and included in your application. Hopefully there will be a possibility to save the CSS directly in your application.
The Universal Theme Roller is not available in the EA2, but they are working hard on it to include this.
Jason Straub did a presentation on APEX and the Multitenant Architecture of the 12c Oracle Database. This session was at the same time that the USA played Portugal in the world cup, still the room was quite busy.
The session started with an overview of the Multitenant Architecture, followed by all the different options that you have were to install APEX. There are a lot of different scenarios with copying and moving PDBs between different Oracle 12c databases. Thankfully Oracle provides a lot of scripts which help you with all of these scenario's.
And that wraps up the first day of ODTUG KScope14.

Finland: OUGF Harmony 2014

Last week was the annual conference for the Oracle User Group Finland. This time the conference was at the Finnish Nature Center called Haltia. Needless to say that the location was beautiful.
Even though the conference started on Thursday, most speakers flew in on Wednesday. Heli, the president of the OUGF and dear friend, acted as a tourguide and showed Helsinki. The tour ended with a dinner at a Viking restaurant where the food was very good. We got to try some tar ice-cream,.. Tar, yes tar - as in the stuff that you find in cigarettes. It tasted like it should be hot, black and oily, instead it was white and cold. Not my thing.
The next morning busses were taking all the delegates from Helsinki to Haltia, about a 30 minute drive.
After breakfast and the opening keynotes, there was a choice of 6 tracks.
The one that stood out for me was done by Brendan Tierney on Sentiment Analysis. Certainly worth it to investigate a little more.

In the evening there was a BBQ with plenty of salmon, reindeer burgers and strawberry cake. There was also a band playing made up of Finnish Oracle employees.

Most delegates were bussed back to Helsinki, only a few "braves" stayed to go camping in the local forrest. The strange thing is that it didn't really get dark, just dusky. The second picture of the camping was taken around midnight.
Even though they warned about the Finnish mosquitos ahead of time, obviously I didn't take enough precautions. I got bitten quite a few times. All part of the experience. The camping was great - thanks Heli, Ann and Olli for taking care of us.

The second day of the conference I had both my sessions. The first one (Use Cases for Virtual Columns) was right after Graham Woods keynote. He was running a bit late and I had the same room, so I started late.. Anyhoo, attendance was good and I thought the session went alright.
My other session (SQL Model Clause: a Gentle Introduction) was the last session of the day, right before Heli's closing keynote.
It is always amazing to listen to Heli, she speaks Finnish and English and switches all the time.
On Saturday, before we left for the airport, I did some sightseeing with Kent Graziano and Roel Hartman. I am glad that I was part of this great conference. Hopefully next year I will be able to fit OUGF Harmony into my calendar and so should you.

"I am Finnish and this is the end" - Heli

 

Current_Schema and the Data Dictionary

Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.

The regular install script for Logger looks something like the following (parts removed and table names are changed):

set verify off serveroutput on

begin
   execute immediate 'create table new_table (x int)';
end;
/

prompt ****
prompt *** Query user_tab_columns
prompt ****
declare
    l_nullable varchar2(32767);
begin
   select nullable
    into l_nullable
    from user_tab_columns
   where table_name = 'NEW_TABLE'
     and column_name = 'X'
   ;
   dbms_output.put_line('Found the NEW_TABLE, do some stuff to it');
end;
/

The first lines make sure that the user is not prompted for input and to see output from DBMS_OUTPUT.
The "Create table" is done using dynamic SQL after which some more action is taken. To determine which action needs to be taken is handled in the PL/SQL block - now simply replace by some DBMS_OUTPUT. In this PL/SQL block the datadictionary view USER_TAB_COLUMNS is queried.
This install script is usually run in the schema where the objects need to be installed.

This time, however, the install script needed to be adjusted for the DBA to run the script. As the DBA didh't want to log into different schema's during the complete install, the files needed to include the following at the top of the scripts:

alter session set current_schema = &1;

What this does is best explained in the Oracle documentation:

This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.

The install script was now called (by the DBA) as follows:

@install.sql SCOTT

Adding the ALTER SESSION command at the top of the install script produced this output

Session altered.


PL/SQL procedure successfully completed.

****
*** Query user_tab_columns
****
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

I would have expected that changing the CURRENT_SCHEMA would also query the USER_TAB_COLUMNS related to the schema that we changed into. This is not the case.

New install script

The install script needed to be adjusted to query the DBA_TAB_COLUMNS instead of the USER_TAB_COLUMNS:

set verify off serveroutput on

alter session set current_schema = &1;

begin
   execute immediate 'create table new_table (x int)';
end;
/

prompt ****
prompt *** Query dba_tab_columns
prompt ****
declare
    l_nullable varchar2(32767);
begin
   select nullable
    into l_nullable
    from dba_tab_columns
   where owner = upper ('&1')
     and table_name = 'NEW_TABLE'
     and rownum = 1;
   dbms_output.put_line('Found the NEW_TABLE, do some stuff to it');
end;
/

Now the install script works as desired:

****
*** Query dba_tab_columns
****
Found the NEW_TABLE

PL/SQL procedure successfully completed.

Difference between SESSION_USER and CURRENT_SCHEMA

SQL> col session_user format a35
SQL> col current_schema format a35
SQL> select sys_context ('userenv', 'session_user') session_user
  2        ,sys_context ('userenv', 'current_schema') current_schema
  3    from dual
  4  /

SESSION_USER                        CURRENT_SCHEMA
----------------------------------- -----------------------------------
SYS                                 SCOTT

Not so strange

Knowing what you know after reading the above, the following is not so strange anymore:

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 STR                                                VARCHAR2(25)


SQL> select column_name
  2        ,data_type
  3    from user_tab_columns
  4   where table_name = 'T'
  5  /

COLUMN_NAME                    DATA_TYPE
------------------------------ -------------------------
ID                             NUMBER

These scripts were run on the following version:

SQL> select *
  2    from v$version
  3  /

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Links

• Logger
• Oracle Documentation on CURRENT_SCHEMA
• Oracle Documentation on SYS_CONTEXT

Bookreview: Oracle APEX Cookbook - 2nd edition by Marcel van der Plas and Michel van Zoest

A few weeks ago Packt Publishing asked me if I would give a book review for the recently released Oracle APEX Cookbook, the second edition by Marcel van der Plas and Michel van Zoest. In exchange I would get the e-book for free, so this can be considered a sponsored blogpost. Even though it is sponsored I will give my honest opinion about it.

The book contains fifteen chapters starting with "Creating a Basic APEX Application" covering several topics such as "Themes and Templates", "APEX Plug-ins", "Using Web Services", '"HTML5 and CSS3" and ending with "Mobile".
Each of the chapters contain several recipe on how to implement a particular feature or functionality.

The way each recipe is setup is as follows: it starts with a short introduction "Getting ready", followed by "How to do it..." and lastly "How it works...". Most recipes also include "There's more..." which gives you additional information about the topic at hand.

Because the book is written in this modular method, it is easy to pick and choose the order in which you want to go through the book. You feel that you know how to "create a basic APEX application" but haven't created your own plug-in, then skip to the chapter on "APEX Plug-ins". It is not necessary to read the book in the order of the chapters, which I tend to do.

Conclusion

Usually I read books before I go to sleep, and yes most of the time the are technical books. This book was no exception. Even though I work with APEX every day, sometimes it was hard to follow along with the different recipes. In my opinion this is not a shortcoming of the book, but the way I tend to read books. This book would be more suited to be used following the recipes while working in APEX.

The recipes in the book follow a similar pattern as the official Oracle documentation. Each recipe starts with a section called "How to do it..." and provide a list of "do this", e.g. "1. Click on the Create Page button" or "6. Click on Create". This works out fine as long as the wizards don't change (which could happen with new releases of APEX). For the beginner this could be confusing if they work with a slightly different version of APEX. For the more experienced APEX developer this should be no problem, they would know the intended behaviour.

Where this book is ahead of the way the official Oracle documentation is set up, is the explanation after the part "How to do it..." called "How it works...". In this section the concepts are explained of what you just created (if you follow along which I obviously didn't).

At first when I started reading the book, I tend to start with the first chapter and work my way through to the end, I was under the impression that the intended audience was the beginning APEX developer. But as the book progressed, I ran into topics which I hardly ever (or never at all) have touched and picked up some good hints and tips. I'm sure that I will get the text handy when I need to, for instance, translate an application or work with websheets. The question then arises: if I haven't used these features before - would I need them in the future?

All in all, I like the book and would surely recommend it - for all APEX developers and especially for beginning APEX developers.

Thanks Packt for providing me a copy of the book and allowing me to review it.

Links

Packt Publishing: APEX Cookbook, 2nd edition

Triggering and Handling a Custom Event in APEX

On one of the pages there was a need to refresh a report region when a node in an APEX Tree was clicked. As far as I know there is no built-in method to create a Dynamic Action to a node in an APEX Tree, so this might call for a Custom Event.
If there is a built-in method, please leave a comment :)

Trigger Custom Event

To trigger a Custom Event, change the "link" column in the APEX Tree Query to something like the following:

select ...
      ,'javascript:apex.event.trigger(document, "nodeClicked", "'
        ||value_from_table||'"); void(0);' as link
from ...

This will include a little bit of javascript in the anchor when the APEX Tree is generated which triggers the "nodeClicked" event passing the "value_from_table" that you want to pass in.
The "value_from_table" will be available in the Custom Event as the Data object.

Handling the Custom Event

To handle the Custom Event, follow the "Create Dynamic Action" wizard and fill in the following:

• Event: Custom
• Custom Event: nodeClicked
• Selection Type: Dom Object
• Dom Object: document

When you want to get the "value_from_table", you will need to reference

this.data;

In my case I needed the "value_from_table" and use "Set Value" as a TRUE action, and refresh a report.

Links

APEX Javascript documentation