A dozen things to do with Oracle APEX

For this year's Australian Oracle User Group conference I decided to consolidate some of my favourite things to do with 3 of my favourite APEX features.

I considered this a bit of a 'lazy' decision, as it didn't really require any new research - but I think sometimes that can produce some good content, since I'm consolidating years of experience into a session.

It did give me a little leverage for one of my Kscope19 abstract submissions - another concept I've been working on for some time now regarding the management of multiple applications acting as one.

Some of my favourite features? I covered Dynamic Actions, of course, plus some use cases for Build Options, and the REQUEST value. Why? Well, check it out.



One thing I learned from this session is to concentrate on the group in the room. I was asked to record my session, which I was happy to do, but I wasn't mic'd up, so I had to spend my time behind the podium so the laptop mic would record my voice. I prefer to move about & show my enthusiasm, but a colleague said I ended up spending too much time looking at my laptop instead of the people in the room.
And in the end, the recording didn't even work!
Connor suggested a better idea would probably be a phone/camera on a tripod. Lesson learned.

I aim to create a few blog posts out of this session, so some of the ideas can be consumed as such, so stay tuned.

Adjusting Interactive Reports - learning from mistakes

Sometimes I come across code where it's obvious someone has taught themselves APEX, then worked through a problem to come up with a solution - but they went a long way.

Today's situation required data in an interactive report to not wrap the output. I thought it'd be worth sharing because a few lessons might be demonstrated along the way for newer developers.

Here is the long way:

1) Add this to the Page Load property
$('td').attr("style","white-space:nowrap");
This will work to a point, until the user start filtering the IR. And it affects all td page elements, not just those in the report.

So now the developer (name withheld to protect the innocent) does the following:
2) Create a hidden item P1_IR_ID

3) Create on new instance computation to fetch the allocated report ID

SELECT interactive_report_id
FROM apex_application_page_ir
WHERE application_id = :APP_ID
AND   page_id        = :APP_PAGE_ID

The computation point will rarely fire because this is executed typically upon landing on the login page - at least the first APEX page visited before logon.

4) Create a dynamic action to reapply the original jQuery.
Event: After refresh
Selection type: DOM Object
DOM Object: &P1_IR_ID.

With a JavaScript action has per the page load. The dynamic action scope was Dynamic to ensure it was reapplied after any partial page refresh (PPR), but I think this would be superfluous to an "after refesh" event.

I admire the ingenuity here to identifying the report. This would have been easier solved with a static region ID being allocated to the region, eg: p1_report, then using jQuery selector #p1_report.

Here's the easy way.

Define the following CSS, either inline or within your supporting .css file.

.ir_nowrap td {
  white-space:nowrap
}

Add "ir_no_wrap" to the region's CSS classes.

Done.

However, my theme 25 IR template did require an additional adjustment, because I found the original template had two class attributes, and the second one was ignored and my class wasn't being used.

I changed the first line from this"
<section class="uIRRegion" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# class="#REGION_CSS_CLASSES#">
to this:
<section class="uIRRegion #REGION_CSS_CLASSES#" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>

If you like this sort of example to learn from, then I bet you'll enjoy Peter's (coming) APEX Worst Practices presentation.

APEX Best Practices Survey

Hi All,

I was inspired by Peter Raganitsch to use the Survey Builder packaged application to create a survey that will help with my "Evidence Based APEX" presentation.

I would appreciate 5-10 minutes of your time if you could fill out my survey:

https://apex.oracle.com/pls/apex/f?p=70347:Q::AAC3

How-to Geek

You'll find a small set of questions over five major topics.

1. Instrumentation (debugging)
2. Performance
3. Security
4. PL/SQL
5. JavaScript

In time I'll discuss the results, I look forward to reading your responses.

Edit
Check out this label for detail on the responses
http://www.grassroots-oracle.com/search/label/2015%20Survey

Scott

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

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'

Steven Feuerstein Perth Review - Session 2 - Writing Maintainable Code

Steven's second session remained light on for technical details, which was great for a Friday morning. He dealt with a topic that can be applied to any language and one that every developer faces on a day to day basis - the balancing act between deadlines and writing maintainable code.

As I write this I remember the old adage from Microsoft Secrets, and probably numerous software engineering books - every step a bug makes towards production, the exponentially more expensive it will be to fix.

Steven mentioned a few basic mantras and elaborated on each. I think this was well received by the audience and it always makes a difference to hear the argument from authority.
Without elaborating too much, some I managed to note down include:

• Construct and enforce coding standards - even to the extent of when to utilise SQL. Steven pointed us to a set of standards he follows and has put out for public comment.
• Personally, I'm in favour of using hungarian notation, including the datatype in the prefix of a variable, eg: ld_dob DATE;
• - Utilise your development tool's template facilities. Whether it be Toad, SQL Developer, Textpad... they have have facilities to generate say a package with all the stubs, exception handlers, comment headers etc you need to avoid the White Canvas of Death, as he calls a blank .sql file. This helps with the enforcement of coding standards.
• Encapsulate "stuff" to make it more readable
• define a package for constants, and don't hardcode ANY value. If there is a slim chance in 100 years it could change, it should be defined somewhere. This also goes for formulas such as a full name evaluation from numerous fields.
• Use pragma exceptions, so we see pkg_exceptions.e_collection_empty instead of -22166
• Use PL/SQL APIs to each table (elaborated later in this post)
• If you ever say Never or Always, it's a bug. Tom Kyte always says ;-)
• Boolean functions read best when they return a boolean expression such as
  RETURN (this='TEST');
as opposed to
  

  if this = 'TEST' then
  return true;
  else
  return false;
  end if;

• Functions in general should work like a funnel. This way you can avoid ORA-06503 - Function returned without a value. PL/SQL warnings can help with this. Steven's words with this comment obviously help describe what a funnel looking function looks like!
• Always (is this a bug?) return a subtype - never a specific datatype, ie: VARCHAR2(100)
• Visit Refactoring.com
• Use instrumentation/assertions. (Hint - I'll be elaborating on this later in the year)
• And the fastest way to get the job done is to take your time. Don't rush. Mistakes are made when people rush, and experts from any industry will tell you that if you take your time, the job will get done quicker with fewer mistakes. The best analogy I heard on this was (I think) from a lecturer at uni -
  "Two carpenters were asked to make the same cabinet. The first carpenter, and old man, spent the first 2 hours thinking - studying the plans, laying out his tools.
  The second carpenter, an apprentice, (put his cowboy hat on) and got cracking straight away - sawing wood, drilling holes, occasionally swearing and looking for his hammer. He looked over at the old man thinking (chuckling), crikey, I'm halfway through this cabinet already and he's still thinking about the job!
  The old man then starts making the cabinet. All the relevant bits of timber were within arms reach as he built the cabinet, every tool never more than a step away. Actual construction of his cabinet took 30 minutes. Meanwhile, four hours in, the apprentice is still putting 'finishing touches' on his cabinet (working out how to put the doors on straight).
  The old man beamed at his work. Every join flush, every screw lined up. When the apprentice finally finished his, he figured he'd do better next time. Practice makes perfect."
  

As for coding practices, I had a flick through the book Steven was giving away on the day, and it looks well worth the read. Perhaps a book like that should be a standard text in a university curriculum?

Now to my first major criticism for the day. The concept of PL/SQL APIs to every table. I noticed Tim Hall also describes this sort of utopian development environment where all DML (including SELECT) for a table is done via PL/SQL APIs and any results returned via associative arrays. As I understand it, utopia is like the speed of light, or absolute zero - theoretically you can never reach it. And I think the reason in this particular case is the balance required between a tuned application and an abstracted/encapsulated one. To his credit, Steven himself said his focus is on PL/SQL performance and integrity, he's never been a SQL tuning fanatic.

Steven also mentioned Tom Kyte's mantra of "if it can be done solely in SQL, then do it in SQL". If there is no need to introduce PL/SQL for a solution, then don't.
E-Business Suite has been utilising table APIs for years, and quite successfully. It creates a well placed barrier and internalises all the business logic, turning say the insertion of a new HR employee into a procedure call whose black box touches a dozen tables to manage the setup. It also allows customisations to be neater and separate from the main install.

Software development theory aside, Steven did demonstrate a very nifty facility in his Toad suite to automatically generate all the typical APIs that may be required for a set of tables. Very tidy indeed.

There are many ideals in this session that need to be reiterated to every developer, and every newbie (I feel I should stipulate I think this is not a derogatory term, we should all remember we were all there once) should also cover it thoroughly. 4/5 for this session. Points lost because I think Hungarian notation should be in the major standard ;-), and further elaboration should be made on the utopian ideal of PL/SQL APIs - although time is always pressing for such a theological debate.