03 March 2015

APEX: Active Tabs Based on Page Groups

Recently someone asked me: "How did you do that? When I include an APEX page in a Page Group, the correct tab is automatically highlighted"
When I setup an application, I usually use Dimitri Gielis' method, so instead of using "real tabs", I use a List and display that list as Tabs.
For each of the "Tabs", I also create Page Groups, just to keep things organized.
Each of the List Entries will have a PL/SQL Expression for "List Entry Current for Pages Type" based on the Page Group that the "Tab" should be active.
The function queries the APEX Repository, and more specifically APEX_APPLICATION_PAGES.
function page_in_group(
         p_app_id     in number ,
         p_page_id    in number ,
         p_page_group in varchar2 )
   return boolean
   l_retval boolean;
   l_dummy  number;
       select 1
         into l_dummy
         from apex_application_pages
        where application_id = p_app_id
          and page_id            = p_page_id
          and page_group         = p_page_group;
       l_retval := l_dummy = 1;
      when no_data_found then
         l_retval := false;
   return l_retval;
end page_in_group;
Placing the Page in the correct Page Group will now "automatically" highlight the correct "tab".

17 October 2014

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

28 September 2014

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
select ename
      ,to_char (abs (dbms_random.random)) credit_card
  from scott.emp

     (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'

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  /

---------- --------------------

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  /

---------- --------------------
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.

23 September 2014

"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)

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)

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

22 September 2014

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
  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:

  color: #2580D4;

.fa-thumbs-o-down {
  font-size: 20px;
That will really make the Font Awesome icons stand out :)

I put a small demo on the Early Adoptor site.

18 September 2014

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

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

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

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

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

---------- --------- --------- --------- ---------
  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.


Definition Temporal Validity

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.