APEX Blueprint, the 2024 edition

During my preparation for the amazing APEX Alpe Adria conference in Maribor I have realized that I no longer consider some of my previous recommendations valid, hence there is a time for an update. You can download my presentation at AAA24 review article.

Here are the original Blueprint articles published 01/2022: Part 1 about the obvious things + backend and Part 2 focused on APEX. I will repeat some thoughts from these two previous articles and put all things here, so you can refer just to this one new article. I will also add few new thoughts. And it will be longer than usual (around 10 pages actually).

Clean code

Clean code is a code that can be understood correctly with minimal effort in minimal time. It is not some utopia and you don't have to go to any extremes. In fact you can do just minimal changes in your applications for a huge improvements.

How you done things might be obvious to you now, but not the future you, not to mention your colleagues. So if you want to avoid being asked the WTF question or if you want to leave behind something you can be proud of, think about how to make your code clean and readable.

Motto: If you are writing it twice, you are doing it wrong.

It is easy to create a mess. Even the official materials out there are encouraging you to quickly write a query on page and to use other shortcuts, which looks great to citizen developers and are tolerable on tiny projects, but they are not that great when you want to build something sustainable or at scale. There are usually multiple paths to achieve same goal (especially in APEX) and it takes some time to catch good practices and to know when to use which approach. You should experiment and seek better ways how to do things.

MVC - the model view controller design pattern

Image from Ashok Gupta article.

The MVC design pattern is the magical recipe for clean code in APEX. It promotes separation in between business logic and design/vizualization. I will simplify this a bit so you can imagine it:

Model = tables & views - the model is represented by your tables and views and it holds end expose your data
View = APEX pages, JS, CSS - the view is the APEX layer, the pages with its components, styles and also the scripts, but without any BE business logic
Controller = packages, triggers, jobs - the controller holds your business logic, so that is represented by your packages, triggers and jobs

The message is simple. You should not have logic on your pages! Your logic should be in packages and views, not hardcoded on your regions.

Why?

What do you get from inlined code? You have most likely experienced this. A full bucket of surprises when user visits the page on the production and it crashes. You also get more code to maintain, because when you copy paste something, you wont deduplicate it. So if you copy paste a query, you will not put it in a view, hence you now have it twice. When was the last time when your manager gave you some time for a refactoring? I don't remember.

Let me tell you few reasons...

Separation of duties (responsibilities/roles)

First, there is the separation of duties/responsibilities/roles which opens you to bring the experts or specialists to your project.

Opens you to bring experts/specialists on board

You can ask any DBA or a tuning expert to look at your slow query, but it would be much harder for them to fix it on the APEX page. And to tune or debug PL/SQL code scattered all over the APEX application it will be even more difficult.

Catch bugs early, build more reliable apps

If you have everything in database you will catch bugs early because of the compilation warnings and errors. When you change something, you will immediately see invalidated objects.

Painful to change, debug and test

If you have queries buried somewhere on the pages, how can you know that something broke?

If you have query on page and you have to change it, many times it is not effective/possible to change it on the page. You have to copy paste it to your SQL Developer, change it there and then copy paste it back. And most likely repeat this cycle multiple times. That is not very efficient... And the more complex query you have, the more time you waste.

Readable diffs

Who reads the APEX exports? There is so much clutter there that sometimes it is very difficult to understand the change. Many times people give up and just commit whatever is there.

Easier code maintenance

With separated logic, you will get more readable diffs and your code will be cleaner and easier to maintain.

Faster searching

It will be also faster to search for things and to do the code reviews.

Reusable code

You can create reusable code only in database, not on pages. There is much higher chance you will see bugs and repeating code in database objects.

Easier impact analysis

It is easier to do the impact analysis because of the database dependencies. If you change columns in table or a view, if you change a package or anything, how can you know that your pages still works? Will you go and test all your pages manually for all test scenarios?

Easier unit tests

It is also easier and possible to do the unit tests on views and packages, and I mean the real unit tests. The ones which you can run regressively to make sure you didn't broke any core functionality.

Exceptions trapped on backend

You can (and should) trap the exceptions on the backend. It is much easier to debug them there than in APEX.

Reduced technical debt

Less code copy pasted, less time wasted. Many of the points above leads to reduced technical debt, which should be your goal even if your managers don't care.

I believe you should push yourself and try to deliver the best solution possible in the time given, learn in your free time, explore different paths, challenge your client request if needed. That is the way how you can grow, how you can be proud of your work and also be confident you can provide the best service to your clients.

Easier deployments?

Lets be honest. No. It will be harder to move your changes and that is the biggest reason why people are not doing applications this way.

The main reason why companies love APEX is the speed how quickly can you build things. Duck tape here, duck tape there, move on. So if you don't have a tool to help you with deployments, it will be certainly harder for you. Now exporting the page wont be enough. You would have to export also the underlying objects.

But...

But it is completely different story if you are using a tool like APEX Deployment Tool. With tool like ADT you can deploy backend and frontend independently and without any effort. So there is zero downside for the MVC approach.

Proposed standards – APEX

keep everything you can in database, not in APEX
keep pages/components behaviour consistent to not confuse the users
convince your team mates to use the same (or at least compatible) standards
ask someone for a code review, you might be surprised, but it is enlightening for both sides
use version control for your APEX apps and database objects

APEX workspace

use dedicated database schema for each app if possible, having just one workspace might be fine (and more developer friendly if you don't have SSO for APEX Builder)
set you environmental/color banner, it is a small thing, but the impact is huge, it might prevent you to run something on a wrong environment; also set same colors in SQL Developer (on connection properties)
leverage the Master application, that can hugely improve your applications by reducing common code and unifying UI and UX
use workspace files for everything you copy in between your apps
personalize your APEX Builder

add missing columns for pages (auth scheme, build option, comments...)
remove columns you don't use
use page group in a Format - Control Break so you can see related pages
save it as a primary report so you don't have to set it up again

upgrade your APEX version as often as possible, you can trust new releases

APEX application

go through all options in Shared components and try to understand them
use Friendly Url to have app/page alias in url instead of app/page number; you can later change app/page numbers without users noticing it
use Substitutions for strings you would normally hardcode (date/number formats, classes, icons...), check escaping options
enable Deep Linking so users can create bookmarks
don't use APEX_COLLECTION for everything, many times you can do just fine with regular table/view
don't skip the the error handling section, that is a great way how to catch errors in APEX
regularly check Advisor and Embedded Code report (in Utilities) to reveal issues and review hardcoded things

APEX pages

always use page alias, page group and set auth scheme on each page
consider using page numbers >= 100 and < 1000, keep gaps in between page numbers, so you can later add related pages close together
create LOVs in shared components for every LOV you want to use, also create a view for each LOV (and any other shared component)
use Authorization Schemes based on package, not on app items
don't abuse the page zero, don't put everything there
use views instead of tables to show data to user, more on that later
use packages for processes, more on that later
when using page wizards keep that in mind that it might set page/components properties in a different way when you start with a blank page

APEX components and naming conventions

be consistent in naming items, regions, processes, dynamic actions, static_id attributes...

name these uppercased with "_" as a word separator
a good name is very important and with proper prefix can be better than any documentation
use meaningful names, skip useless prefixes/postfixes
turn off the items encryption unless it is really sensitive item

use prefixes when naming processes

INIT_ prefix for setting up items (pre-rendering)
SET_ prefix for computations (pre-rendering)
RUN_/CALL_ prefix for actions (pre-rendering)
SAVE_ prefix for submit processes (processing)
AJAX_/CALL_ prefix for AJAX callbacks (processing)
GET_/CHECK_ prefix for validations (processing)

use prefixes for static_id on page components

REGION_ for regions
BUTTON_ prefix for buttons
add other things you use, your JS will be more readable

for page items keep the P#_NAME format

use matching column name whenever possible
item placement is also important; I like using "Blank with attributes" for my regions (and I actually set this as a default region type) and "Hero" regions above every form/report region for related items not to mix them with form items

for global/app items use G_NAME format
for global page use P0_NAME format, use items on page zero if you need to access them in JavaScript, otherwise use application items

JavaScript & CSS

JavaScript and CSS should be in your application or workspace files, not on the page, nor on the global page. That is valid also for the dynamic actions. You should store the content in JS file if possible. It might be acceptable to do some specific JS/CSS tweaks on the page if it is small and used just on that page/component, but keep in mind that this is how the mess starts.

There is an excellent article from Vincent about clean code for JavaScript, read that for more details.

Proposed standards – database

Magic of views and magical views

Don't be afraid of views, views are awesome. They provide a level of abstraction and security for both rows and columns. They shield your application from data model changes and through them you can easily limit rows and columns exposed on page. Through views you can create reusable and testable code. Views are easier to change (and test) than a query at APEX and your diff will be readable. Views also support dependencies and compile time warnings (vs run time errors). Views can contain business logic and you can contain concatenation chaos there.

Some people are against using APEX items in views. I love it. It keeps my APEX regions clean from WHERE conditions, I just use the reference to the object and keep everything there with all those benefits of compile warnings, dependencies, easier and traceable changes... And you can test your views if you set and get items from PL/SQL.

avoid queries on page, use views instead
learn how to leverage page items inside of the view; you can create dynamic views with page items (and call it from different pages), you can also leverage SQL Macros, polymorphic table functions or event the old-school pipelined rows
if you change a view, don't forget to synchronize columns on page
always use table/view aliases in queries, even if you have just one table; later someone else will come and add a JOIN and you will be wondering from which table it is; the shorter alias the better, but it has to have meaning

Packaged treasure

We all love packages. Packages offer much more than standalone procedures/functions. The main advantage is that they help you to contain the procedures and functions at one place, so you can see related things together. They allows you to reuse constants, exceptions, records... They provide better performance and overloading is also cool. Take advantage of that and use them in APEX.

create a dedicated package for each APEX page or at least a page group; this way it would be clear where your code is hidden and where it belongs
use object references instead of hardcoded queries (region source = table/view)
avoid anonymous blocks, use procedures and functions instead and keep procedures and functions short, like below 100 lines if possible
use procedures (in a package) for handling user actions (forms/grids, AJAX calls, other processes), use Invoke API if possible
use functions (in a package) for expressions, conditions and validations, also consider using page items and calculate expressions/conditions there; for attributes and formats (dates, numbers, currencies) use app items, substitution strings or translations
don't create nested and private functions/procedures; keep in mind that if you create nested or private modules you can't test them, you can't reuse them and you can't see them in dependencies; checkout the ACCESSIBLE BY clause
consider utPLSQL for critical parts of your app; without the unit tests at least for your core functionality you will have no idea if you broke something
use views in packages and ROWTYPEs to make them shorter and cleaner; with this you can also avoid explicit cursors
use %TYPE and %ROWTYPE as much as possible, don't hardcode types; explore SUBTYPEs
use Logger (or any alternative like APEX_DEBUG) in procedures, but skip it from functions called in SQL (for performance reasons), mark those with PRAGMA UDF
write useful comments above every block of code if possible/needed and helpful; skip silly comments with signatures, versions and dates, you have Git blame for that
don't hardcode things, you can put hardcoded string and magic numbers into package variables/constants, comment might also help...
don't repeat the procedure/function name after END, it is redundant and if you keep your procedures small it is just distracting
proper code structure and formatting dramatically improves code readability; don't underestimate this, do a proper indentation and keep at least few empty lines in between each function/procedure
refactor your code as soon as possible because often there is no next phase for refactoring

Triggering nightmare

We all faced some headaches when using triggers. The best is to avoid them, but if you can't then I would recommend to create just one compound trigger on each table instead of these oldschool triggers. I typically name them as TABLE_NAME__ or TABLE_NAME_TRG and merge/consolidate all previous triggers into this one compound trigger. Than there are no questions about which trigger fired and in which order, no questions about in which trigger you have the problem. You can also solve the mutation table errors there. And if you decide to add something later, you are just changing existing trigger instead of creating a new one. Of course the point is that you also move your logic to a real package and you just call it from the trigger. Think of it as your small mini package. Would you rathe have 4 packages on your table or just one?

Data model

spend more time on doing data model right, that is a great investment; poor design will hunt you down eventually
use data modeler to visualize your model, do it for each application module
be consistent in naming and data types, don't forget to name constraints
pick good (and intuitive) naming conventions, more on that below
use primary and foreign keys (should be obvious, but it is not); no foreign keys on audit columns

Naming conventions

This is a controversial topic for sure, feel free to adjust this to your beliefs. I know these type of naming conventions: non existing, non intuitive, overcomplicated, or schizophrenic. Naming conventions must be simple and intuitive so developers will remember them. If they have to search, you failed.

I prefer naming tables with natural names and in plural form (for historic reasons, to avoid keyword conflicts and it is more natural to me + I can use the singular for on a package), but if you use app/module prefix, then singular form is ok; consistency is the key
it is fine to use _V postfix on views, I would strongly encourage you to not use a prefix
for views used on APEX pages I would recommend adding P#_ (after app prefix) representing the page where the view is used
I think it is a good idea to use short app prefix (3 characters) on each database object; when someone add things to your schema, you can keep eye on your objects
when you have a lot of tables it is a certainly good to add a module prefix (after app prefix) to keep them logically grouped
I certainly don't recommend using any prefixes on table columns, it redundant, it does not make sense to me, it is a waste of time - you just write more, read more, need to maintain more and every table name change became a nightmare
don't forget to name your constraints, when you get an error you can identify problem just from the constraint name, these days I prefer TABLE_NAME_[PK|UQ] and TABLE_NAME_COLUMN_NAME[FK|NN] format (yes, you can name NOT NULL constraints too)
use same logic for indexes (TABLE_NAME_IX#), I like to use constraint names for indexes created on/for constraints
for sequences I recommend COLUMN_NAME (typically "PROJECT_ID", "CUSTOMER_ID"), but it is not bad to give them a postfix _SEQ or similar; alternatively you can use TABLE_NAME_SEQ instead, but I find the column variant more readable
for package names I would recommend to use module prefix (after app prefix) to group functions related together; for APEX packages I like to use ???_P# packages (where ??? is your application module and P# is a related page or page group); when you do this for views too, it is very obvious how it is all linked together
use meaningful names and don't use silly prefixes or postfixes (PROC_, FUNC_, _PKG); does your prexif/postfix add any value and is it intuitive to others?
use simple prefixes for arguments, like IN_ and OUT_ prefixes, although P_ is fine too
prefix local variables with V_ or L_, global variables with G_, constants with C_, use whatever make sense for collections, consistently
write keywords and Oracle packages as uppercased, everything yours is lowercased

Other good practices

proper indentation is a must, that will save you a lot of time; remember, you (and your colleagues) will spend much more time reading the code than you spend writing it, so invest some time to make your code readable
if you can't format your code yourself, use autoformat
always try to create clean and readable code
keep existing code better than you found it (if you have time to break it)

In photography you learn the rules first and then you break them. That is a great advice. So if you have a good reason why to break something, go ahead.

Snippets and Thoughts of One Oracle Developer

Search This Blog