Monday 21 June 2010

Better practices within APEX Forms

When developing in Apex, sometimes it's good to check out the little tips, suggestions and reference information that pops up below
It's just a shame some of the examples the present show some better practices, even in Apex 4.0.
SELECT MAX(empno) + 1 ?

How many years have we been telling people to never use that in their code?

And if you select "Existing sequence" you get an ugly looking page process with
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 
  :P1_EMP_NO := get_pk; 
end; 

As a better alternative, select "Custom PL/SQL function", or if you want to obfuscate it within a trigger you could do it as follows:

CREATE OR REPLACE TRIGGER emp_bi
BEFORE INSERT ON sage.emp
FOR EACH ROW
BEGIN
  IF :NEW.emp_no IS NULL THEN     
    SELECT emp_seq.NEXTVAL 
    INTO :NEW.emp_no 
    FROM dual;
  END IF;
END;
/

With the key line here being 5 - only source from the sequence if the emp_no is not already supplied. If your table is also being populated from another source, it would be more efficient to use
INSERT INTO emp (emp_no, ...) VALUES (emp_seq.NEXTVAL, ...);

I think within the context of most applications in Application Express, where your users are performing data entry at a page level, I don't think it matters if the sequence is populate like this in a page process or trigger.

Also not that in Oracle 11g, in lieu of the implicit cursor you can use
:NEW.emp_no := emp_seq.NEXTVAL
However, last time I ran a trace on this, it was the same as selecting from dual - handy, not a performance improvement.

Any other preferred methods?

Update March 2016
Sven Weller provides the perfect APEX 'trigger'

2 comments:

Patrick Wolf said...

Hi,

you are right. I have filed a bug to update the examples and the generated code.

Thanks for the hint
Patrick

Stew said...

I always create my own trigger just like your example. Then I don't have to worry about where the INSERTs are coming from. My only problem comes when I copy records from the Test db to Production, but forget to update the Production sequence! Yikes! :-(

Excellent tip.