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:
declare
  function get_pk return varchar2
  is
  begin
    for c1 in (select EMP_SEQ.nextval next_val
               from dual)
    loop
        return c1.next_val;
    end loop;
  end;
begin
  :P6_EMPNO := get_pk;
end;
Now it's somewhat simpler:
begin
    if :P6_EMPNO is null then
        select "EMP_SEQ".nextval
          into :P6_EMPNO
          from dual;
    end if;
end;
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

2 comments:

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

Hi

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.