Friday 18 November 2011

APEX matures => sequence usage

Of course many of the new features that come with an Oracle product are well documented. In fact, it's not a bad idea to read through the entire release notes for every APEX release because they also include things like changed behaviour.

Some changes in APEX 4.1 are that minor that you wouldn't even notice them until you fall over it.

Something I found recently was the code generated when choosing to source your primary key from a sequence when building a form.

Previously, the code was a little ugly:
  function get_pk return varchar2
    for c1 in (select EMP_SEQ.nextval next_val
               from dual)
        return c1.next_val;
    end loop;
  :P6_EMPNO := get_pk;
Now it's somewhat simpler:
    if :P6_EMPNO is null then
        select "EMP_SEQ".nextval
          into :P6_EMPNO
          from dual;
    end if;
What they haven't done, however, is clean up the custom function example - and this is a little more noticable.
I noted this in EA for 4.0 - I think they said they've logged the change, but it still hasn't come along. While it's not really promoting this sort of code, it doesn't really have a place anywhere except perhaps an article talking about how bad it is for concurrent user environments.

Perhaps sequence usage will change with 12c?

Idle musings... Scott


Anonymous said...

How does select "EMP_SEQ".nextval work with the double quotes? I get a ORA-02289: sequence does not exist

Scott Wesley said...


A quick google for oracle double quotes uncovered this relevant response.

Essentially, double quotes around identifiers will be case-sensitive.

select "emp_seq".nextval from dual;

will not work unless you created the sequence with
create sequence "emp_seq";

In general, best to avoid using double quotes unless absolutely necessary.