Thursday, 13 October 2016

Connect2016 - Australian Conference Series

It's less than a month away from the Australian conference series and I'm probably about 2 thirds ready for my sessions.

Australia? Yup, the Perth gig will be on 7-8 Nov and the fun continues in Melbourne on 10-11 Nov 2016.

Check out, then think about that junket. Jokes aside, events like these are the best place to chat to people about Oracle technology, with plenty of sessions to allow you to stay sane while considering what challenges others are facing. I know times are hard but the price is reasonable, and there is more to just the program.

Sessions I've got my eye on include:

"Evolving APEX Development Practices" - Mark Lancaster, title alone grabs me.

"User Experience (UX): Building Usable Applications – Why and How" - Basheer Khan, as an APEX developer I'm finding UX very interesting, when I can indulge.

"APEX, RESTful Services and STRIPE" - Lino Schildenfeld, I have plenty to learn about web services.
Lino is also doing "APEX Plugins - World of possibilities", and I'm keen to hear his perspective on this.

"Transition to Cloud - Should we or shouldn't we?"" - Howard Ong, I have a feeling I should listen to this.

"Next Generation Databases" - Guy Harrison, I know this one will be infotainment goodness.

And Connor McDonald will treat us with some 12.2 goodness, and my director has one on BI Visualisations, lessons direct from a recent project.

I have the following sessions:
"Mastering Dynamic Actions" - ready!
"APEX 5.1 Charts with OracleJET" - researched... mostly.

In Perth I'll be joining other Sage colleagues called "Our Kitchen Rules" and I'll have 10 minutes to cover a topic of my choosing - ready!

I have another couple of ideas ready for next year, too.

I'll be on both legs so it will be particularly awesome to see everyone on the east coast.

Tuesday, 11 October 2016

OTN Appreciation Day : APEX Dynamic Actions

I'm going to take advantage of the fact I live in a city so remote to many others in this amazing community, and schedule this post for my local 8am time. It's seems my schedule didn't work. Not the first fail from blogger... This might get me as one of the first posts in what's hopefully an interesting day amongst Oracle bloggers.

Tim Hall, a great producer of resources for Oracle technologists, suggested bloggers new and old post about their favourite feature, in appreciation for the professional network that is OTN.

I nominated APEX Dynamic Actions for sake of brevity in the subject line, and while they are pretty awesome as a whole, it does encompasses a range of sub-component features.

So I want to be more specific than that, I nominate the jQuery Selector attribute.

One of my favourite features
I could have selected one of a bunch of SQL functions (listagg, nvl2) or some PL/SQL features (%TYPE), or maybe something rarer like Oracle Text, but this one is a little more important to me.

For me it's a bridge between the Oracle world and the native web page we are generating.

With this attribute specifically, we can nominate, with precision, a component on the page and attach some event handler. We can make the web page communicate with the database.

Understanding how this feature operates I found like a gateway to building more interactive applications.

I looked back and it started with a post like this in 2012:
And took me so far as to write a book combining jQuery with APEX:
(apparently chapter 9 has finally been corrected, more detail later)

Learning about this feature and further steering my career in an interesting direction required a community that blogged, participated in forums, tweeted, slacked, attended user group events, presented, networked, authored, and so on.

Giving also helps you learn. I do a little bit every now and then on the forums, and now I'm deemed a "legend". I read, watch interactions from interesting questions, and occasionally contribute. It helps in both directions, it's awesome, and it adds up. Same goes for presenting. A topic choice for next year is helping me learn what I need to keep my skills up to date.


Wednesday, 5 October 2016

Synchronise Sequence value with 12c Identity Column

My journey into 12c continues with the use of identity columns, this time regarding data that had been imported from another database, but sequences haven't been updated.

Ensuring the next number returned from a sequence matches the current value from the table appears to be a common problem, my thoughts are described here. The biggest trouble is linking up the sequence to the column so we could automate the process.


We can now execute an ALTER statement to reset/align the sequence to a value appropriate to the column
alter table my_table modify (id generated as identity START WITH LIMIT VALUE);

The next insert will be problem free, and we don't need to do anything else.

The Underlying Sequence

Identity columns use a sequence under the hood, as hinted in the statement diagrams for the create table syntax.
I love these diagrams

I thought perhaps I could use my old technique on these sequences, but I forgot where to look for the name of the sequence associated with the identity column.

As usual Tim Hall pointed me in the right direction
select table_name, column_name, generation_type, sequence_name
from all_tab_identity_cols
where table_name = 'MY_TABLE'

------------- ----------- --------------- -------------
MY_TABLE      ID          BY DEFAULT      ISEQ$$_430382

I was reminded shortly after that you can also see the data default referenced within SQL Developer

SQL Developer Table properties

However it turns out if you try to apply alter sequence to those generated by the system from the table DDL, you get the following error.
ORA-32793: cannot alter a system-generated sequence
Fancy that.

So to increment the sequence beyond the most recent ID, I could make a bunch of requests to the next value of the sequence.
  l_val pls_integer;
  for i in 1..240 -- use the difference between .nextval and max(id)
     l_val := ISEQ$$_430382.nextval; -- change to sequence returned from all_tab_identity_cols
 end loop;
This might be considered un-elegant, a dirty way to fix the problem. We're on 12c, surely there's a better way.

12c solution

The sequence creation is built into the DDL, so why not maintenance? Check out the help for the ALTER TABLE command.
START WITH LIMIT VALUE, which is specific to identity_options, can only be used with ALTER TABLE MODIFY. If you specify START WITH LIMIT VALUE, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator's high water mark. The next value returned by the sequence generator will be the high water mark + INCREMENT BY integer for increasing sequences, or the high water mark - INCREMENT BY integer for decreasing sequences.
Now illustrate this in action.
drop table seq_reset_test;
create table seq_reset_test -- basic table with my suggested identity column options
  (id number generated by default on null as identity
  ,CONSTRAINT seq_reset_test_pk PRIMARY KEY (id)
  ,label varchar2(20)

Table SEQ_RESET_TEST created.

-- Will be given first ID of 1
insert into seq_reset_test (label) values ('Initial');

1 row inserted.

-- Simulate update of db without synchronising sequence
update seq_reset_test set id = 1000 where id = 1;

1 row updated.

-- ID too high for sequence
select * from seq_reset_test;

        ID LABEL              
---------- --------------------
      1000 Initial             

-- Find sequence name from all_tab_identity_cols
select ISEQ$$_404558.currval from dual;


-- This will use ID 2, risking PK violation
insert into seq_reset_test (label) values ('Second');

1 row inserted.

-- Magic alter statement
alter table seq_reset_test modify (id generated by default on null as identity start with limit value);

Table SEQ_RESET_TEST altered.

-- This will use the updated sequence, avoiding max(id) value
insert into seq_reset_test (label) values ('Third');

1 row inserted.

-- Proof
select * from seq_reset_test;

        ID LABEL              
---------- --------------------
      1000 Initial             
         2 Second              
      1001 Third               

select ISEQ$$_404558.currval from dual;



So it seems we can throw away that old reset_seq.sql file?
Particularly if the replacement is now an ALTER TABLE command that doesn't need to know about any values.

alter table my_table modify (id generated /*by default on null*/ as identity START WITH LIMIT VALUE);

In comments the optional definition settings I find most useful, which would have been defined within the table DDL.

If Oracle can manage by IDs with sequences and do all the grunt work for me, go right ahead.

Monday, 3 October 2016

Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.

Well, it's one thing to say 'create all new tables like this', but what about our existing structure? What process should we follow to decommission these triggers?

If you take the weekly DB Design quiz at the PL/SQL Challenge website, you may recognise this example as a quiz from Sept 2016.

Existing Framework

Consider a table with a structure similar to the one I described in a post of mine from 2010
drop table orbiters;
drop sequence orbiter_seq;

create sequence orbiter_seq;

create table orbiters
 (id  number not null
 ,CONSTRAINT orbiter_pk PRIMARY KEY (id)
 ,position    number not null
 ,planet varchar2(15) not null
 ,name   varchar2(30) not null
 ,distance  number not null
 ,created_date date not null
 ,created_by varchar2(50) not null

create or replace TRIGGER orbiters_biur BEFORE INSERT OR UPDATE ON orbiters FOR EACH ROW DECLARE
  IF INSERTING THEN            := COALESCE(           , orbiter_seq.NEXTVAL);
    :NEW.created_date  := COALESCE(:NEW.created_date , SYSDATE);
    :NEW.created_by    := COALESCE(:NEW.created_by   , apex_application.g_user, USER);

insert into orbiters (position,planet,name,distance) values (3,'Earth', 'Luna', 384) ;
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'SpaceX', 400/1000);
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'ISS', 400/1000);

select id, name from orbiters;

1   Luna
2   SpaceX
3   ISS
It's not rocket surgery...

Audit columns

There are plenty of options about in regard to auditing your columns and tracking history, but for the pattern you're likely to see regularly, there is no need for a trigger.
alter table orbiters modify created_date default sysdate ;
alter table orbiters modify  created_by default coalesce(
Sven's coalesce to resolve the username when the environment may or may not be APEX is neat.

Default using sequence

The sequence itself has no performance benefit using an assignment vs selecting from dual. Instead, it needs to either be in the insert statement, or as a default value as 12c (finally) allows.
alter table orbiters modify id default orbiter_seq.NEXTVAL;
drop trigger orbiters_biur;
If you get ORA-02262, check your sequence exists and you've typed it properly.

Identity columns

Identity columns are ideal for new tables, but existing tables would require more work.

Identity columns actually use a sequence under the hood, so while I thought I was on the right track by using this syntax:
alter table orbiters modify id GENERATED BY DEFAULT ON NULL AS IDENTITY start with 10;
It returns the following error.
ORA-30673: column to be modified is not an identity column
A few bloggers mention this fact, someone posted the question on Reddit of all places.

But I think the value equation of creating a new column and shifting data, foreign key references etc is outweighed by the fact that performance is better regardless of using identity vs sequence. Tim demonstrates this here. I discuss resetting sequences in identity columns here.

Definitely remove the trigger

Here is the best part:
drop trigger orbiters_biur;


To decommission triggers and replace them with new 12c features, use these steps:

alter table orbiters modify id default orbiter_seq.NEXTVAL;
alter table orbiters modify created_date default sysdate;
alter table orbiters modify created_by default user;
drop trigger orbiters_biur;

Happy #db12c!

Wednesday, 21 September 2016

Show report tooltip as notification

Last week I described a method to make the tooltip on information more accessible to the end user.

Here is how you could make the same information available to touchscreen users.

1) First step, as before, is to define the HTML Expression of the column to include the title tag.
In this case I also stored my row identifier as an extra data- attribute.
<span title="#RECENT_NOTE#" data-key="#ROW_KEY#">#MY_COLUMN#</span>

2a) Create a dynamic action on click of the column cell.
jQuery Selector:

2b) Set the value of a hidden page item.
This could be done a few ways, but here is how you can do it with Set Value, based on a JavaScript expression.
Set Value action example

What's pertinent is this.triggeringElement, which identifies the element being clicked. In this case it's the table cell, so .children() identifies the span from the HTML expression; .data() grabs the extra attribute; and .attr() grabs the title tooltip.
$(this.triggeringElement).children('span').data('key') + ': ' + $(this.triggeringElement).attr('title')

This expression extracts the a relevant key value (to identify the record) and concatenates it with the tooltip.

My P50_LAST_NOTE is hidden and unprotected.

2c) A Notification plugin could be used to display the value set in the hidden item, as a second action in the dynamic action. Other notification plugins are also available.
This 'gritter' style notification from APEX uses the hash syntax to get the client value,
As opposed to the value from session state while the page was rendered.
This information is documented in the relevant attribute help.

Now when you tap (or click) on the cell, the tooltip is displayed in the notification.

Wednesday, 14 September 2016

Extend column tooltip to table cell

Quite sometime ago while still on 3.x I described a simple way to add tooltips in a report using standard HTML in a HTML Expression.
<span title="#RECENT_NOTE#">#MY_COLUMN#</span>

However, it will only appear when hovering over the span content, not anywhere within the table cell.

To do so we can add a dynamic action to execute some JavaScript after refresh of the relevant region.
$('td[headers=my_column] span').each( // for every data cell in the column
  function() { 
     // copy the title attribute to the parent cell
$(this).parent().attr('title',  $(this).attr('title'));
This JavaScript copies the title attribute from the inner span to the surrounding td tag, so you will see the tooltip when the cursor is anywhere within the cell. I find this relevant when there is a bit of spacing/padding in the report.

In this follow-up post I describe how you could display this tooltip on tap of the cell, useful when used within touch devices.

Friday, 9 September 2016

APEX Survey Results: Workspace Activity Log

This question in my 2015 survey relates to built-in instrumentation.

Q7. Do you utilise apex_workspace_activity_log (for monitoring/reporting application usage)?

Yes (90)  47%
No (102) 53%

This log table reports details of all page rendering and AJAX process calls. I really like this information, though I would like to be able to add to the output things such as
sys_context('userenv', 'server_host')

In addition to the Monitoring pages you'll find in the APEX administration section, I've built a few pages that report on this information in my own particular way. This allows me to monitor behaviour and identify bottlenecks. More information to come on this since it's fuelling a presentation I'm writing on charting in APEX 5.1, so stay tuned.