APEX Blueprint, YMMV (part 1)

I

In APEX it is easy to create a mess. Little knowledge, a lot of damage. There are usually multiple paths to the same goal and it takes some time to catch good practices and know where to use what approach. Publishing any sort of guidelines is brave or foolish, but here are my recommendations.

I wish this document will help you to achieve inner peace and ease frustration of your colleagues when they will have to change your code. I don't expect you to agree with everything. Take it or leave it. If it doesn't suit you, find guidelines from someone else or create your own.

I decided to split it into two parts to keep them reasonable long and digestible:

1. Obvious things + Database rules (tips)
2. APEX rules (tips)

Welcome to the part one.

Obvious things (but sometimes hard to do)

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

• Keep whatever you can in database, not in APEX
• easier maintenance, support, debugging (changes)
• easier governance/control (diff)
• reusable code
• Don't hardcode it
• queries, PL/SQL blocks, styles, scripts; put queries into views, PL/SQL blocks into procedures/functions, styles into app.css file, scripts into app.js file
• expressions, conditions, attributes, date/number formats; consider using page items and calculate expressions/conditions there, for attributes and formats use app items or substitution strings
• avoid DA, avoid logic in DA, avoid DA misuse (do you really need DA to submit page?); store JavaScript in app.js file
• Use views for displaying data to user
• view provide level of abstraction, maintainability, security
• easier to change it in a view then in APEX region, readable diff
• reusable, testable, compile time warnings vs run time errors
• easy to track object dependencies and usage
• concatenation chaos can be contained here (or on virtual columns on table)
• Use packaged procedures for handling user inputs (forms/grids, AJAX calls)
• it might be ok to keep native DML process, but I suggest renaming them
• packages offer much more than standalone procedures/functions (global variables, constants, types, exceptions, init block, private functions, overloading, better performance...)
• see Coding practices below
• Use object references instead of queries (region source = table/view)
• cleaner page, easier to read and maintain
• easier impact analysis
• Keep pages/components/behaviour consistent to not confuse the user
• Convince colleagues to use the same (or at least compatible) standards
• Ask colleague (or friend) for a code/app review, it is enlightening for both sides

Database - data model

• Spend more time on doing data model right
• use data modeler to visualize your model
• Be consistent in naming and data types
• Pick good (and intuitive) naming conventions
• Name constraints too
• Use primary and foreign keys, except for the audit columns
• Store your model (and APEX app) in version control system
• export each object to its own file, use object type as a folder
• export app as zip, then unzip the app to a folder in Git*

Database - naming conventions

• I prefer naming tables and views with natural names and in plural form (for historic reasons, to avoid keyword conflicts and it is more natural for me), but if you use app/module prefix, then singular form is ok, just be consistent
• If you have schema just for 1 application, I would strongly restrain from using app prefix in table names, it is just a waste of time
• If you have a lot of tables (or multiple apps in same schema) it is a certainly good to use some sort of prefix to keep them grouped
• For views used on APEX pages I would recommend adding P#_ prefix representing the page where the view is used
• I certainly don't like using any prefixes on table columns, again it is a waste of time, you just write more, read more, need to maintain more and every table name change became a nightmare
• Name your constraints, when you get an error you can identify problem just from the constraint name, I prefer [PK|UQ]_TABLE and [FK|NN]_TABLE_COLUMN format (yes, you can name NOT NULL constraints too)
• Use same logic for indexes (IX_TABLE_*), I like to use constraint names for indexes created on/for constraints
• I name sequences as COLUMN_NAME, but it is not bad to give them a postfix _SEQ or similar, I prefer postfix because it is easier to read in this case; if you have many sequences it might be better if you use TABLE_NAME instead of the column
• For package names I would recommend using natural names for common/shared packages and module prefix to group functions related to that module; with multiple apps in 1 schema I would consider using app prefix (like A#_)

Database - coding practices

• Keep your procedures/functions short
• Use %TYPE and %ROWTYPE as much as possible
• Use meaningful names, don't use silly prefixes or postfixes (PROC_, FUNC_); although GET_ and SET_ might help
• 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_, consider C_ for constants
• Don't hardcode things, you can put hardcoded string and magic numbers into package variables/constants, comment might also help
• Write useful comment above every block of code if possible and helpful
• Don't use silly comments with signatures, versions and dates, you have Git* for that
• Write keywords and Oracle packages as uppercase, everything mine is lower cased
• Proper indentation can save you a lot of time; remember, you (and your colleagues) will spend much more time reading the code than writing it, so invest some time to make your code readable
• Keep existing code better than you found it (if you have time and confidence)
• Always try to create clean and readable code
• Always use table/view aliases; the shorter the better
• Don't repeat the procedure/function name after END, it is redundant and if you keep your procedures small it is distracting

Source code example (you can check my Git for more):

    FUNCTION get_page_root (
        in_page_id              navigation.page_id%TYPE     := NULL,
        in_app_id               navigation.app_id%TYPE      := NULL
    )
    RETURN navigation.page_id%TYPE
    AS
        out_id                  apex_application_pages.page_id%TYPE;
    BEGIN
        SELECT REGEXP_SUBSTR(MAX(SYS_CONNECT_BY_PATH(n.page_id, '/')), '[0-9]+$') INTO out_id
        FROM navigation n
        WHERE n.app_id          = COALESCE(in_app_id, app.get_app_id())
        CONNECT BY n.app_id     = PRIOR n.app_id
            AND n.page_id       = PRIOR n.parent_id
        START WITH n.page_id    = COALESCE(in_page_id, app.get_page_id());
        --
        RETURN out_id;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    END;

Bonus - how I check these things

It is easy to to set some rules, it is not always that easy to follow them. I created few pages in APEX which helps me to check things and also to gain some overview about the database scheme/model I am working with.

Tables overview (with some suggestions):

Table detail (with constraints, dependencies...):
I can for example rename or disable contraints here or add table column comments.

Views overview (with usage/dependencies):

Package detail (comments are retrieved from the package specification):

See you on the second part, more focused on APEX conventions (rules/tips).