17 April 2015

APEX 5: forgot the images?

On my play environment I usually use Oracle APEX with the Embedded PL/SQL Gateway, just because it's so easy.
When a new version of APEX is released, just like everybody else, I upgrade my play environment.
After the apexins.sql script is run, I always want to start playing with it immediately. Usually it is at this point where I just see a blank page... scratching my head wondering why it is not running,... having to go back to the documentation to realise I forgot the last step - configure the EPG...
Now with APEX5 an alert is shown when you forget the last step:

Immediately you know what to do... :)
Another one of those little enhancements that makes APEX5 simply awesome.

08 April 2015

Speed Up Development with Logger: Generate a Template

Instrumentation of PL/SQL code is essential. My favourite tool to instrument PL/SQL is Logger.
Martin Giffy D'Souza wrote a few blogs on how to speed up development with Logger and I want to chime in with my own productivity booster.
What I have written is a PL/SQL package that will generate a Procedure or Function body with all the calls to Logger. This includes all the arguments, or at least the IN and IN/OUT arguments.
When an exception occurs, it is quite handy to have all the arguments at your disposal to make it easier to debug your code. But to include the arguments manually when you are writing code is quite a lot of work.
You can download (or fork or whatever) the package on GitHub.

How does it work?

Install the package, which contains a single procedure called Template.

   procedure template (p_procedure  in varchar2
                      ,p_standalone in boolean := false
The Template procedure has two arguments; one for the complete (pacakged) procedure name, and one to indicate if it is standalone (not pacakged) or not.

When you have written the procedure (or function) signature, something like

create or replace package demo_pkg is
   procedure test (p_arg1 in     varchar2
                  ,p_arg2 in out varchar2
                  ,p_arg3 out    varchar2

end demo_pkg;
... and when it compiles successfully, then you can generate the stored procedure body using my generator_pkg.
The package uses DBMS_OUTPUT to show the template, so you should set the serveroutput on in SQL*Plus.
set serveroutput on format wrapped

   generator_pkg.template ('demo_pkg.test');
This will generate the following:
      l_scope  constant varchar2(61) := g_package||'test';
      l_params logger.tab_param;
      logger.append_param (p_params => l_params, p_name => 'p_arg1', p_val => p_arg1);
      logger.append_param (p_params => l_params, p_name => 'p_arg2', p_val => p_arg2);
      logger.log_information (p_text => 'Start'
        ,p_scope => l_scope
        ,p_params => l_params
      [==> Actual Program goes here ==]
      logger.log_information (p_text => 'End'
        ,p_scope => l_scope
   end test;
Because it is a packaged procedure the l_scope variable contains a reference to g_package.
Each of my packages contains a Global Constant called g_package which is defined as
   g_package constant varchar2(31) := $$plsql_unit || '.';

Now that the template is generated, simply copy-and-paste it in your editor and start to write the actual program where it says

   [==> Actual Program goes here ==]
And that's it, now you have a starting point for development including the references to Logger.

The blogs that Martin wrote:

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.