Declarative Favicon in 5.1

Some features you just tend to stumble upon in the builder somewhere.

Sure it's in the documentation, but not listed under new features.

Where to find declarative favicon setting in APEX 5.1

Patrick described how to do it in APEX 5.0;
Amanda for APEX 4.2;
Christian if you're still using APEX Listener;

Now we have an dedicated attribute. Neat.

Phased APEX migrations

While looking through the comments regarding 5.1 features it occured to me how many different ways the Oracle APEX team enable us to phase our applications into any new APEX version, thereby minimising risk and introducing new IDE features to developers earlier.

I've always thought APEX does a really good job of leaving our applications alone during upgrades. It normally depends on how much the boundaries have been pushed, and how much the theme has been modified, or templates added. In fact, most of the work is in upgrading the theme, which is why the Universal Theme came about.

So that that masterpiece may start the list:

1. Universal Theme
   Designed to remove need to migrate themes in future. Instead, theme templates re-subscribed on demand as long as you kept your theme locked to the repository.
2. Compatibility mode
   A relict from Oracle Forms, this feature allows you to prepare your app for behavioural changes between APEX versions. This great write up from Carsten describes how asynchronous developments in 5.1 may apply to your applications.
   Check this setting on any applications borne out of earlier APEX versions.
3. jQuery Migrate
   To keep libraries lean, older functions are removed over time. Older references can sustained until they're refactored, as described by Marko.
4. Utilities -> Upgrade Application
   APEX will leave deprecated features alone, until you're ready to upgrade each instance. The classic example is the old datepicker from 3.1. All your items called the horrible window until you replaced them all. This feature makes this process quicker and this older reference from Dimitri still applies. I may translate my mentioned of it in this presentation (as it applies to charts) to a blog post.
5. Multiple concurrent versions
   It's not here year, but 5.2 may see the introduction of the ability to run two different APEX versions on the same database instance.
6. apex.oracle.com
   This will always be a sandbox for the latest version or patch, so why not try your applications out in there? You can script up your tables, generate some data and import your app. Start preparing now and see how much your application won't break.
   Let's not forget apexea.oracle.com, when the latest major release is in early adopter mode.

If you can think of any others, let me know and I'll add them to the list.

Or do you have any stories of nasty surprises? I haven't heard of too many serious issues with APEX upgrades.

Edit:

• Upgrade your icons at any time

#letswreckthistogether

Upgrading the Universal Theme

Back when APEX 5.0 was released, these forward thinkers at the development team installed a feature to verify your Universal Theme against the one defined in the repository.

There hasn't really been anything to verify against, until now.

There are a number of changes to the Universal Theme, and one way to explore these changes is to check out what's reported under the Verify button in the Theme definition.

Though unfortunately on apex.oracle.com I'm still getting the same error
ORA-20001: Unable to subscribe to report template.
ORA-00040: active time limit exceeded - call aborted

Errors aside, this facility all planned, of course, to minimise the impact theme upgrades have caused in the history of APEX. And no doubt used internally while they were developing UT in the first place.

As Patrick Wolf points out in this forum post, some features aren't borne out of the IDE upgrade alone, but are found with the theme upgrade. This includes RTL functionality.

So some thoughts that come out of reviewing the comparison:

• A couple of new default styles, to go along with Slate. Nice.
• Two new region templates to explore - Content Block and Blank with Attributes (No Grid)
• Every other template has been touched in some way. It would be nice to see a diff of the updates.

If your Universal Theme is still locked, you just refresh your theme definition and your application should continue to work wonderfully.

Take care if you have any customisations or extensions, regression test as necessary. We've tried to minimise ours so it will be interesting to explore when 5.1 becomes generally available.

If you want to learn more about the features of the Universal Theme, check out the sample application, which also got a wicked upgrade, always available at
apex.oracle.com/ut
There are some awesome tools in the reference section alone.

APEX 5.2 early outlook

It seems at a recent conference a slide was put up that a few tweeters managed to snap a photo of:

Beyond 5.2 + #orclapex #safeharbour pic.twitter.com/XKMbmzQw4N

— Simon Greenwood (@APEXORADEV) December 7, 2016

It describes some goals for APEX 5.2 and beyond, of course coming with the usual safe harbor statement - we're still waiting for 5.1!

I do enjoy these snippets into the future, having talked about various APEX SOD in the past. Though I realise I'm doing this not having heard the talk that came with the slide, so I could be interpreting things wrong.

This was the best image I saw, so for those who struggle to read it (emphasis theirs):

1. Tighter integration / native integration with RESTful web services and ORDS
2. Improved integration with Developer Cloud Service (hudson, git)
3. Enhanced self service cloud with documented RESTful APIs
4. Proper APEX app diff, full app change history capture
5. Friendly URLs
6. Automated APEX app testing
7. Greater internal use of JSON expanding on interactive grids and page designer
8. Allow page designer to set IR and IG report settings at design time
9. Multiple concurrent versions, for example 5.1 and 5.2 - goal is to ease upgrade
10. Documented APIs to dynamically generate APEX application components

#1 is no surprise, I'd be shocked if this didn't continue to happen, as with the cloud stuff.
#4 will excite the bean counters, though I'd be happy with just a more accurate change history.
#5 A zillion ways to do this, but something within the builder would be cool. Particularly since I've attempted none of them.
#6 well, this would be impressive.
#8 excites me, particular in a world where half the time I don't include the search bar, so modifying report settings becomes a pain.
#9 surprises me. I'm not sure how they'll solve this, but quite an engineering effort when upgrades are fairly seemless, the UT has the Verify option that we'll finally be able to play with, and we also have jQuery legacy options in the UI details. But I guess it does reduce the need for another server during regression testing...
#10 still not sure I get why people want to do this, but why not I guess.

Noticely absent: any mention of OracleJET. I thought they may take fuller advantage of such a rich product.

To me this list is another sign of APEX's maturity. 5.0 came with a lot of stuff to excite developers, and 5.1 ties a tidy knot in the bow (and introduces OracleJET...)
This list for 5.2 is mostly infrastructure stuff, right?

Time will tell.

In Memory Session State - Use Case

Many moons ago Anton Nielson posted about the different kinds of session state in Oracle APEX.

Different kinds?!

I remember session state was hard enough when learning, particularly coming from Oracle Forms. But how is there more than one, you may ask?

98% of the time we just need to worry about the persisted session state that we all know and love. The one we see when clicking "Session" in the developer toolbar. Essentially, this is just a normal table with a session ID, item name and value.

In Memory Session State (IMMS) is the 2% that comes and bites us occasionally. Recently I had a problem the scenario described by Anton as "2A Item Rendering". His description is a little tricky, but I think this visualisation of a simpler example will help.

IMSS Demonstration

Some page facts:

• 'before region' has item P45_BEFORE, defaulted to A
• Region 1 has condition P45_BEFORE = A 
• Region 2 (after Region 1, not visible) has condition P45_AFTER = B 
• 'after region' has item P45_AFTER, defaulted to B
• The items have no source, and are just defaulted to those values shown.
• Session state (persisted) remains empty before/after. During is another story. 

In this case, while it renders Region 1, IMSS will actually report a value of A for P45_BEFORE while rendering Region 1, hence it's displayed. Since P45_AFTER is rendered after Region 2, the value is null during the evaluation of the condition.

Therefore, the results differ by moving the location of the items relative to the component with the condition.

Unfortunately I couldn't find any support/explanation for this behaviour in the debug log, even at LEVEL9.

In my actual case, I had an item that stopped a report from being rendered until the user selected criteria and clicked submit. I did this by defining a hidden P1_SUBMITTED, defaulted to Y, and used that as the condition in the report.

This usually worked fine since the item is usually with other criteria in the Right Side region, but a new page had this item at the top of the page. So P1_SUBMITTED was treated as having a value Y while rendering my report, just like in the example above with P45_BEFORE.

Happy APEXing!

See this post for another example.

APEX Component Export

A common question when it comes to migrating APEX applications is "can I just export a page".

There are more technical posts on the topic of exporting APEX applications than this one out there, eg:
Håvard Kristiansen
Christoph Ruepprich
Alan Warren
John Scott
I just wanted to clarify the concept, perhaps for those learning the tool.

So the answer? Sure you can, but what about other components related to that page? A breadcrumb; menu list entry; LOV? What about application items? Are you sure you've got everything?

So with this in mind, when you start the process export an APEX application you have a side-option to do a Component Export, which might look like this:

From here we can not just nominate the page, but identify other components to add to the export. The exported SQL is just a subset of the entire application. (I wonder when this will become JSON?)

However, the only time I consider doing this is for a reporting application where I know the only components in a new report is the page itself and a breadcrumb. The menu is dynamic so the existence of the page is enough.

Otherwise, perhaps for an operational style application, I think the risk of missing changes it too high not to do a full export.

Build options can also help you find your bundle, but even then diligence needs to be high.

Answer with SQL: How many weekdays a year?

I'm a big fan of generating data with dual, using a perk of the connect by syntax.

I think Tom Kyte was the originator of this technique. It's not necessarily the fastest method, but it's super convenient - no table required.

Today I wanted to know how many weekdays a year, so I defined 365 rows on the fly using sysdate to turn these into each day of the year. Then I ran a simple select over this to aggregate my result

with years as (
   select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy
   from dual
   connect by level <= 365
)
select count(*)
      ,sum(case when dy in ('sat','sun') then 1 end) weekends
from years

You can see this demonstrated at livesql.oracle.com
https://livesql.oracle.com/apex/livesql/file/content_D6ZWKMK4O2IVMWGOAXIDEP61M.html
Never heard of it? I recommend you have a play. I've only dabbled myself, but if you don't have an environment to experiment with, this is free!

Ordering & Formatting Date Columns in APEX

Quite often I'll find I'd like to display a date with the time in a column within an APEX report, but you end up wondering how to control the wrap.

This is one problem that probably has a half dozen solutions, but I think this is the cleanest. And blogging about it helps me remember next time.

Depending on your screen size, you might be faced with something that takes up too much space per row.

not a good wrap

One solution is to turn that column into a formatted character string
,to_char(created_date,'DD-MON-YYYY"<br>"HH24:MI:SS') created_date
Note the HTML break tag surrounded by double quotes, this will put the time on the next line.

Combine this with styling on the column to stop wrapping.
Column setting CSS Style: white-space:nowrap
And you'll get a neater output.

If column is varchar, won't order nice

But there's a problem. And it's a deal breaker if you want to allow the user to order the column.
Your output will be ordered Apr, Aug, Dec, Feb, Jan... sense a trend? Alphabetical, not chronological.
Applying formatting within SQL is something you generally want to avoid.

Instead, just use the funky date format mask and apply it in a declarative manner within the column attributes, along with the CSS Style. This will allow the report to honour the appropriate ordering on the date columns, whatever format mask you require.

Declarative column attributes, using DD-MON-YYYY "<br>"HH24:MI:SS

Either way you need to set "Escape special characters" to No. I think your individual date values will be safe from cross site scripting.

These settings, along with the other Column Formatting options are used frequently in my applications, particularly HTML Expression. Just remember, if you use style frequently, define a class in your page/application/theme CSS definition and use that instead.
.date_fmt {white-space:nowrap;}

#letswreckthistogether

AUSOUG Connect 2016 Presentations

Another conference series over and many new lessons are still churning around somewhere in the back of my brain.

Our Australian conferences still seem a little subdued compared to years gone by, but the thicker community bonds held strong while the economy decides what to do.

The other problem we have is getting all those people out there we know are using Oracle technology out from their cubicles and at these fun events!

My Presentations

I did 2 and a half this year, and I have plenty more ideas for next year. Some of which got cut out of my 5.1 Charts session, which really didn't end up what I intended it to be. In part because APEX 5.1 is still in early adopter, though I hear that there will be no EA3 and it will head straight into apex.oracle.com as UAT. Just not sure when...

Mastering Dynamic Actions - demonstrating 3 useful patterns I use daily. Certainly my favourite topic at the moment.
https://prezi.com/lko3dxsbzr3i/mastering-dynamic-actions/

APEX 5.1 Charts with OracleJET - more to come on this topic, and I think I'm going to find myself digging into JET over the summer.

https://prezi.com/misgwk4trqhc/apex-51-charts-with-oraclejet/

The Perfect Trigger - this is the 'half', well, only 10 minutes. I shared a slot with Penny & Ray who talked about some of their favourite things.
https://prezi.com/g0cew4clllzk/the-perfect-trigger/
Bonus slide at the end for the Melbourne crowd where I just mentioned some sites people might like but may have missed.

Conference notes

Here are some thoughts from my scratchy notes, included here to help reiterate my learning, and some of you might find something interesting.

Basheer Khan described "Extensibility" as a required feature of a good UX. APEX has that nailed, affirming some of what I was going to say about plugins in my OracleJET session.

Basheer' design philosphy for mobile was: glance, scan, commit. Which translated into workflows of only 3 levels deep. Glancing at choices, scanning results, commiting to action. I think we do a decent job of that already, and so does the documentation. I always feel only 3 clicks away from the information I need.

The final thing from Basheer I have to look up is: UX RDK.

This is not me. I'd never play for Melbourne...

Mark Lancaster gave me something that may help the penny drop with an issue regarding tabular forms, not that I use them often. I'm certainly looking forward to learning all about Interactive Grids.

Connor smashed out some good feels about 12.2 where it seems some good features that weren't quite finished get the treatment they need. Well, almost.

LISTAGG now has features to elegantly handle problems the concatenated string becomes large, but there is still no 'distinct' option.

Validate_conversion sounds like a very useful datatype validation device, but it might has well behave like LNNVL.
Column level collation sounded interesting, but I didn't make enough notes. Connor speaks pretty fast. Case insensitive columns will solve a few issues, but come with caveats.

I love the look of the deprecated pragma. PL/SQL warnings in general I would like to revisit.

External table alterations on the fly complete the picture for the true flexibility of external tables. And doing things on the fly seems to be a bit of a trend with 12.2

JSON generation is a biggy, not just features for reading. It's a shame the ability to read and write came out at the same time, but again, it completes the picture in regard to the JSON lifecycle.

So many other goodies to come in 12.2, including approx_count_distinct() which works in a very interesting manner to help make histogram analysis quicker on large data.

Analytical Views seem like a massive feature attempting to solve the problems with rollup and grouping, but I wonder if it will be a another spruce goose.

Melbourne was also a blast. Trent Schafer showed me how much I really don't know about APEX administration, and Ubuntu for that matter. I've really stalled in my use, and speculating whether I made the right decision. Let's see how I go with my exploration of Atom.

Lino's session on Stripe was a good demonstration on how web services make APEX applications become easily extensible. It also gave me another presentation idea.

And all this is a drop in the ocean to what I hear is coming out of some European conferences right now. Information overload. I feel like those people who know an awful lot about one thing and do it really well... an 'expert', right? One who knows a lot about a little.

Scott.

APEX 5.1 Chart Column Mapping

If you've played with the D3 chart plugins you can find in the Sample Charts Packaged Application, you may have noticed the ability to nominate a column from your query as the series name.

APEX 5.1 Column Mapping

APEX 5.1 provides this built into the framework. Combined with some rather granular attribute control at series and axis levels, I think it will be harder to find tweaks that haven't been made declarative.

And if you do, there is a section in the chart attributes for JavaScript code. This also means if you do need to start hacking your chart, you're just adding to the existing attributes.

OracleJET Custom JavaScript

Compare this with the XML API for AnyChart 6.x where once you start using XML, say goodbye to most of your declarative attribute settings.

The ability to nominate a series column is optional, bringing more flexibilty to the SQL that no longer needs to conform to positional columns. I'm not sure yet if this will impact how often I use pivot/unpivot, but I'm sure it will help.

This post was brought to you by the result of last minute presentation screenshot prep and a prompt from this tweet, so thank Peter.

Build Options on Report Columns

Let's say you're experimenting with a report (IR or classic) column and you would only like it to appear in you development environment.

What options do you have for hiding that column in other environments?

1. Don't migrate the change - use your source control processes to only move it when it's ready. This isn't always practical, nor the intent, particularly since you can programatically set the status.
   
   
2. Add server condition on database name - we can use an expression such as
   sys_context('userenv','db_name') = 'dev'This isn't bad, I have a common function I call for this sort of thing. But it's quite granular.
   
   
3. Build Options - under-utilised silent catalyst that can help us turn a group features off/on at the flick of a switch.

In Oracle APEX 5.1, Build Options are an attribute of each column, in addition to just about every other component in the builder.

APEX 5.1 Column Attributes

Prior to APEX 5.1, column definitions were one of few components that don't have a declarative option. However, we still have the magic of dictionary views.

Pre 5.1 Build Option on Column

So we can query the application's build options and determine if the relevant record is switched on.

select null
from apex_application_build_options
where build_option_name like 'My build option'
and build_option_status = 'Include'
and application_id = :APP_ID

Again, you could put this in a re-usable function/view, not that you would need it often.

Neat.

APEX Survey Results: Instrumentation Thoughts

This question in my 2015 survey is related to question 7 regarding instrumentation.

Q8: Do you have any thoughts you'd like to share on instrumentation?

I left this open for people to add a free-text response. Here are some worth highlighting, though it was hard to choose since so many people had something to add.

a) How would it help me?

Have a read of this post to get an idea of how the debug output can help solve problems.

b) It's not optional

I agree, it should be baked into your code.

c) An application is not just building, but also managing after deployment. To reduce the cost of the second fase (sic) instrumentation is a must

Another perspective on what instrumentation is all about.

d) Nobody instruments enough ....

Well, I think you could probably have too many logs.

e) Not enough planning goes into instrumentation in general.

Planning is an interesting point. While we could plaster our code with debug logs, this isn't necessarily going to help debugging your code, particularly when you need to further sort the wheat from the chaff.

Once you've decided on a framework, start thinking about standard patterns such as noting start/end of procedures; incoming/outgoing parameters; the varied level of detail you may need; how you can turn it on/off; how errors are highlighted and treated.

Any other factors you think are worth mentioning in regard to planning?

f) Please make Debugging more smoother and documented. An idea can be to create a packaged application to showcase debugging.

Something for the APEX development team to consider.

g) Instrumentation should point out the exact place where error is occured (sic). It should not be overhead for the APEX application and there should be a switch to turn it on/off.

Back in APEX 3.x, debugging was displayed where relevant, within the rendered page itself. It looked awkward and was hard to decipher. Any code is overhead, but the risk/benefit equation outweighs the cost. APEX debugging can be turned on/off, and some logging libraries provide options for granular logging.

h) ... Also debugging apex_collections is somehow hard today ...

Constructs relating specifically to a particular session (such as collections) can be tricky to detail, but with careful planning you should be OK. It may be useful to output a collection count at key locations. You could have a little library that looped and logged key columns from the collection. And the Session link in the Developer Toolbar allows you to see collection contents naturally.

i) I wish I'd known about or used some of the instrumentation methods above long ago when I built my first Apex apps.

I think that comment right there should be a warning to all those who haven't started yet.

j) The CREATE ANY CONTEXT privilege requirement in Logger is a pain.

Sounds worth airing, but I'm pretty sure the benefits outweigh whatever pain that might be.

k) asdfasdfasfd

It seems even cats have opinions on the topic.

l) do it! use whatever tool fits your needs, but use something!

I'll finish with that one.

Does anyone recognise their comment? I didn't want to name anyone without asking.

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 www.ausoug.org.au/connect2016, 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.

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.
https://oracle-base.com/blog/2016/09/28/otn-appreciation-day

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:
http://www.grassroots-oracle.com/2012/09/using-jquery-selector-in-apex.html
And took me so far as to write a book combining jQuery with APEX:
http://goo.gl/ZzQ0RP
(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.

#ThanksOTN

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.

TL;DR

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'

TABLE_NAME    COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME
------------- ----------- --------------- -------------
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.

declare
  l_val pls_integer;
begin
  for i in 1..240 -- use the difference between .nextval and max(id)
  loop
     l_val := ISEQ$$_430382.nextval; -- change to sequence returned from all_tab_identity_cols
 end loop;
end;
/

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;

   CURRVAL
----------
         1

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

   CURRVAL
----------
      1001

Conclusion

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.

Related Posts

12 Column Upgrades
Decommissioning Triggers in 12c

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
BEGIN
  IF INSERTING THEN
    :NEW.id            := COALESCE(:NEW.id           , orbiter_seq.NEXTVAL);
    :NEW.created_date  := COALESCE(:NEW.created_date , SYSDATE);
    :NEW.created_by    := COALESCE(:NEW.created_by   , apex_application.g_user, USER);
  END IF;
END;
/

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;

ID  NAME
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(
          sys_context('APEX$SESSION','app_user')
         ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
         ,sys_context('userenv','session_user')
         );

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;

Conclusion

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 sys_context('APEX$SESSION','app_user');
drop trigger orbiters_biur;

Happy #db12c!

Related Posts

12 Column Upgrades
Synchronise Identity Column

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:
td[headers="my_column"]

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,
#P50_LAST_NOTE#
As opposed to the value from session state while the page was rendered.
&P50_LAST_NOTE.
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.

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.

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.

Users still double click buttons

Despite web pages having been around for quite some time, and users have been given years of affordance, people still double click on links & buttons in web pages.

This can cause a few issues in an APEX environment as the underlying process may get executed twice, and today I came up with what I thought was a simple, declarative solution.

In my case I had a button that submitted a modal page. I added a dynamic action that simply hid the button on click. I just had to ensure the action didn't execute on page load, and the button still submitted the page.

I thought I'd look around to see what others have done to mitigate this issue, and it seems I was on par, but with less code:

Wonderfully verbose description of what I described above
Funky JavaScript solution from 4.x
JavaScript solution in 3.x
Similar JavaScript solution
Purely session state protection

The only thing that makes me uncomfortable about my suggestion is the button has two behaviours, so Roeland's description may be considered more elegant, ie - a submit and hide action.

APEX Survey Results: Instrumentation / Debugging

This question from my 2015 survey may pique curiosity among some APEX developers.

Q6: How do you instrument your code?

Hopefully those in the 22% who do not add instrumentation just didn't understand the question.

Instrumentation is another term for adding debugging information to your code. There is plenty of commentary on this topic and done right it can make tracking down problems a breeze.

It can be as simple as knowing when a procedure was called, and with what parameters. Take the following procedure definition with instrumentation (debug logs) added.

procedure xyz(p_in number) is
begin
  debug('start xyz');
  debug('p_in:'||p_in);

  -- do stuff

  debug('end xyz');
end;

 I think you're essentially coding blindfolded without it. You could only infer this procedure was executed because of whatever it does, and if something goes wrong how do you know what was passed in? Where did it get up to? Did it execute at all?

Imagine using the database without the ALL% dictionary views. They're essentially instrumentation to your database. Tom Kyte has some particularly good talks on the topic, and a few chapters in his various books.

It's very much a PL/SQL thing, but since APEX pages are generated via PL/SQL, there is a natural solution with the supplied apex_debug package. Any process can add information to the APEX debug log, which is only collected while debug is on.

There is no reason why you shouldn't instrument any PL/SQL, and have the granular control to turn on/off at will. Libraries are available to make this job easier.

You may have heard Logger talked about at conferences. It's a community developed library, well, mostly driven by some key players. It's had a few homes (remember google code?) but is now found on GitHub:
github.com/OraOpenSource/Logger

PL/SQL Logger can be found here, if you want to compare solutions or look for ideas, read other people's PL/SQL.
https://sourceforge.net/p/plsqllogger/wiki/Home/

Despite recommending it a few times, I've never actually used Logger on-site (sorry Martin). For quite some time I've had a basic infrastructure of my own that has worked fine, using the same premise. That being said, it's on my list to convert/upgrade when the timing is right. Partially because I've followed it's progress, know who wrote it, and inspected the code. I look forward to benefits some extra features will bring.

The next few questions also relate to instrumentation, so stay tuned.

How to debug stuff in Oracle APEX

Recently a fine young gentleman, who shall remain nameless (let's call him Jerry), asked for some assistance he was getting with an error in Oracle APEX.

He had done all the right things in regard to debugging the problem, but didn't know enough about APEX to know which settings to investigate.

I think developers new to APEX need the occasional post like this to give them an idea on how to start looking into a problem, so I hope you found this knowledge helpful in squishing bugs in future.

The problem.

An associate of Jerry's converted a classic report to an interative report. However, when using the search bar to add a filter, the error "missing expression" was shown instead.

We could be fairly certain it's the filter we added that caused the problem, but that's built by the APEX engine. What can we do but perhaps run the page in debug mode to look for clues.

developer toolbar

Jerry enabled debug mode in the developer bar, which refreshed the page, collecting information about the rendering processes within the page. Clicking on open debug, then drilling into the the recent debug entry, using ctrl-F to find the error and you'll see a result like this.

Click/tap to embiggen

The brackets are there, but there's nothing in the middle, hence "missing expression". Jerry told me about this and wondered how this could come to be?

The Hint

Jerry said they recently expanded the capacity to filter stuff on that report, for my benefit, he says... anyway, that's a lead we can follow through on.

It turns out each column in an Interactive Report is configurable at a fairly granular level, and it turns out all columns had the ability to filter disabled.

Isn't it so great we can manipulate 8 columns at once like this?

If a few columns were set as searchable in this fashion, the debug would report a statement that looked like this.

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
)  r
where ((instr(upper("ENAME"),upper(:APXWS_SEARCH_STRING_1)) > 0
    or instr(upper("SAL"),upper(:APXWS_SEARCH_STRING_1)) > 0
))
) r where rownum <= to_number(:APXWS_MAX_ROW_CNT)

Note your own PL/SQL can also contribute to this debug log using the apex_debug package.

But, how?

Anyway, how could Jerry know to look there? Unfortunately the debug machine can't do all our work and tell us what to check. I guess knowing exactly where to look is when APEX developers get paid the foldy notes, in knowing what sort of settings to check out first.

Through experience I guess I had a hunch on where to look and how to get there. Adding to that, any errors you may receive with Interactive Report filters, try repeat the filter for just one column instead of 'row text contains'. I find issues are often isolated to one particular column.

I normally start with the related region if I'm not sure where to start, though these settings are mostly oriented to the region framework, regardless of the widget inside the region - report, chart, plugin etc.

People new to the Page Designer often forget the 'Attribute' node under the columns. This is where you find settings specific to the region type you've chosen.

IR search bar controls are fairly granular, and I remembered that IR columns also had some options, and seeing 'Filter' gave it away for me.

Will we always have a job?

Back to the concept that computers can't do it all. I think us software developers have got it good, because we're the ones who need to design the AI to solve problems without humans. We'll be one of the last jobs to go, right?

Application Item Prefix Usage

I was constructing a fresh app for a presentation idea and I came to think about the prefix people use for Application Items in Oracle APEX.

I've seen a few variations used, and upon asking the question on Twitter (as a poll limited to 4 options), some came back with a few other suggestions.

Preference for application item prefix in #orclapex ?

— Scott (@swesley_perth) July 21, 2016

The final tally based on the replies (we'll see how pasting from Excel goes...)

F_	15		Inspired by APEX team, no doubt. +1 for me
APP_	10		Fair call, but could match built-in
G_	6		Global, clever. One person combines this with A_ for application scope.
{project}	5		To match the prefix on your tables, right?
AI_	3		Can't deny the provenance
{none}	3		This is dangerous, potential clash with built-ins
A_	1		Based on item scope.

I remember some chatter recently about a community sourced standards document for APEX being kicked off on StackOverflow. Perhaps mini surveys like this can help shape projects like that, or maybe it fits into the oraopensource activities.

Either way, this sort of discussion is surely healthy for the community, eking out any important considerations in sometimes seemingly trivial matters, or "bikeshedding" is a term I heard recently.

I can't say I've tried creating application items (or substitution strings for that matter) that are the same as a pre-defined substition string, but surely it would be detrimental.

You never know, even the Oracle APEX development team may decide a thing or two from community responses. I certainly know what my next poll topic will be, now the Fire on Page Load default No campaign is over ;p

ps - more 2015 survey results to come. The next question was on instrumentation, so a few juicy points to make there.

What should I know about SQL?

Chris Saxon from the Developer Advocates group asked the following question on Twitter.

What three things do you think every developer must know about #SQL ?

Reply and share yours!

— Chris Saxon (@chrisrsaxon) July 5, 2016

My immediate thought regarded features I'd be disappointed to live without. Looking at some other responses I realise that the answer matches Tom Kyte's creed: "it depends".

Here is a collection of responses that I think sums(sql) really well, syntax pun = intended.

What feature should I know, to be productive & efficient?
(me)
1. analytics
2. scalar subqueries
2. with clause

What fundamental principles should I understand?
(Stew Ashton)
1. DDL: data design is key to good SQL
2. ACID
3. Bind variables

What frequent mechanics/behaviour should I be consistently aware of?
(me)
1. null behaviour
2. date arithmetic
3. data type comparison issues, for lack of better desc

All the while remembering architecture is so important.
(Dani Schnider)
1. SQL is a set-based language
2. Clean data model is important
3. Constraints are not just for fun

and so is performance
(Draft A Center? Or Kyle Hailey posting from wrong account?)
1, Bitmap Indexes
2. Reading Explain Plans
3. SQL Tracing

Why there are often exceptions to rules:
(That Jeff Smith)
4. getting results back doesn't mean your code is right.
5. learning never stops

For the creative out there.
(David Fitzjarrell)
6. There is usually more than one way to write a query.
7. Sometimes you need to think 'different'

And there's always the "comedian".
(Connor McDonald)
select, from, where :-)

That's quite the collection!

APEX Survey Results: Editing Tools

Another preference question in my 2015 survey.

Q5: What editing tools do you use for PL/SQL and JavaScript

That Jeff Smith fellow should be pretty happy with the top result, but almost half of the "Other" responses said PL/SQL Developer. I used this many years ago while SQL Developer was still being born. Since SQL Developer was free and portable, it was an easy selection, though I only use if for queries, not PL/SQL development.

A number of editors were suggested in Other, including two takers for Notepad. All I can say is wow, and I hope for the sake of others that tab characters are not being saved in your files.

I've been happily using Textpad almost since I started coding. I'm interested in making the jump to a non OS specific editor like Sublime, or perhaps Atom, but I'm not there yet. Though after watching the Kscope16 deep dive / montage (as a livestream), I want to try soon!

Edit: January 2017

I spotted these results in the 2016 StackOverflow survey, a somewhat more diverse survey than mine.

And I've started to use Atom, but I might have to give Sublime a go for a while to compare.