How Oracle Follows Good Database Development Standards, NOT

Seems like they are trying to teach “How to write bad/worse code”.

>> To me, it’s one more confirmation that Oracle can do an excellent RDBMS, but when it comes to database applications development . . .

That is an utterly astonishing conclusion to reach from this *one* example. If this example had been written differently and correctly, would you have concluded that “Oracle” is superb at database application development?

Oracle Application Express itself is a “database application”, according to your definition. That seems to scale just fine to thousands and thousands of users. What does that prove about “Oracle”?

Joel,

Don’t get me wrong — I’m a big fan of Oracle database technology. Furthermore, I like Oracle APEX a lot and I think that Oracle, finally, came up with the next great development platform since old good Oracle Forms (it did have its own shortcomings but for its time it was great).

If you take pretty much *anything* from current Oracle Applications — you could see that it’s not the best example of database development and design and this is a flagship Oracle application I’d say.

To answer you question — this single example is not conclusive indeed but I also wouldn’t agree that APEX is purely a database application — rather development framework or environment but it’s an application itself on the other hand. However, I’ve got your attention now and this is what the purpose of this blog — not to insult APEX developers. Though, I suppose it could be a side-effect. :)

Now, I hope this example will be corrected soon.

Thanks for your feedback!

Alex

Alex,

So how would you recommend the query should be written?

“Making sure the database instance couldn’t potentially use an index in DEPARTMENT_ID column? ”
But it could use an index on nvl(DEPARTMENT_ID,’-1′)
And if you want to query on employees not in departments, that index may be quite appropriate.
Yes, it does focus on how you can develop an app, which isn’t necessarily how you SHOULD develop it. However when you get into ‘SHOULD’ you get into ‘WHY’ rather than just ‘HOW’ and your 2-Day guide becomes a 1-Week one.
If the user has to deal with anything more than trivial data volumes, they’ll need some understanding of SQL and how to structure data, indexes etc. But I don’t think this guide is the place to get into that.

Marley,

try using

where DEPARTMENT_ID = NVL(:P3_DEPARTMENT_ID, DEPARTMENT_ID)

this should be expanded into a concatenation consisting of two parts, each part driven by the fact whether :P3_DEPARTMENT_ID was submitted as null or not (i.e. only one of the part will be really executed)

Marley, Alex,

DEPARTMENT_ID = NVL(:P3_DEPARTMENT_ID, DEPARTMENT_ID) has slightly different meaning compare to nvl(DEPARTMENT_ID,’-1′) = nvl(:P3_DEPARTMENT_ID,’-1′). Unless, I’m missing something, when empty value provided as P3_DEPARTMENT_ID, the original version will return employees not in any department (i.e. with DEPARTMENT_ID NULL) while the modified version will return employees in any department except ones not in any department.

The best way would be to customize the where clause and use DEPARTMENT_ID = :P3_DEPARTMENT_ID for non-empty P3_DEPARTMENT_ID and DEPARTMENT_ID IS NULL for empty P3_DEPARTMENT_ID.

The alternative would be to use OR – DEPARTMENT_ID = :P3_DEPARTMENT_ID OR (:P3_DEPARTMENT_ID IS NULL AND DEPARTMENT_ID IS NULL). However, I would check if CBO is smart enough to execute it efficiently.

Gary,

I wouldn’t agree with you for several reasons. Some (I can say many since I’m a DBA ;) developers, unfortunately, are not keen on learning “WHY” when it comes to the databases and want to know only one universal way. Note, that documentation has no explanation why exactly NVL trick is needed so there is always a balance between assumed reader experience and depth of details.

Once developers learn this trick, they will use it without even thinking and produce sub-optimal SQL. On average, APEX developers I met are usually much more familiar with SQL and database development to say the least (compare to Java or .Net crowd) so it would be quite appropriate to give them WHY sometimes.

Finally, a beginners guide must be especially carefully crafted as it builds a foundation of the knowledge and that better be right.

Thanks for the feedback everyone.