Insum Solutions has been building APEX applications for a long time, and we keep on learning how to do it better. We also expect, with the growth of APEX generally and our merger with digital innovation consulting firm Talan specifically, that we will be building a whole lot more applications in the future. So to ensure we deliver high-quality apps consistently, we decided it was time to formalize the way we track projects from inception to completion.
So, of course, we built an APEX app for that. We call it InTrack. So to help build this internal application, we came up with a list of what we consider critical elements to a high-quality project, such as project methodology, coding and testing standards, and security, just to name a few. Of course, each client and project is different, so we do not expect or try to come up with a single “best” practice implementation.
We wanted to share all of these elements so that you, too, can level up your Oracle APEX development and make sure your applications meet the ever-changing needs of your or your clients’ organizations.
Please note that throughout this post, we’ve included some tools to consider. These are not exhaustive lists by any means but are simply the ones we’re more experienced with. Feel free to comment on tools you like we may have missed!
1. Project Methodology
A solid project methodology will help you complete your project on time and on budget. It also helps improve communication between team members and ensures everyone is aware of their roles and responsibilities. Additionally, using a project methodology can help reduce the risk of errors and increase the quality of your final product.
It provides a structure and framework for project planning, execution, and control.
Oracle APEX projects are very well suited to the Agile methodology for many reasons. Here are three:
- Oracle APEX is a low-code development platform enabling quick and easy changes.
- The use of user stories means requirements can be quickly gathered and implemented.
- The ability to deploy in short sprints allows rapid feedback and adaptation to changes.
These factors mean that the Agile methodology works extremely well with APEX projects, resulting in increased productivity and a more successful project.
Our project methodology at Insum relies very heavily on Agile, and it has served us well. We’ve even taken it as far as changing how we introduce ourselves. Despite our varying experience, roles and responsibilities, we’ll often introduce ourselves to new clients simply as “Agile Team Members.”
Project methodologies to consider
- Agile (scrum)
- Scaled Agile
- Waterfall
- Kanban
2. Technical Documentation
https://xkcd.com/1421/
“There’s no way I’ll ever forget why I’m writing this piece of code“ said every single developer of all time.
And once you’ve been trying to solve a problem for hours, you might really believe that.
But trust me, you’ll forget. And you might even find yourself years later looking at it wondering what goofball could have written something so convoluted before realizing, ooooops. That was you.
Technical documentation is essential in any software development project.
It provides a roadmap for developers, outlining how the system should work and what each component does. In addition, it can be used as a reference by users who need to understand how the system works or troubleshoot problems. Documentation also helps ensure that all stakeholders have a common understanding of the system, preventing costly misunderstandings down the road.
For all these reasons and more, ensuring you have a process or standard for documentation is a key to your project’s long-term success.
Check out our own Plamen Mushkov’s detailed post about Generating Documentation out of your PL/SQL packages. We especially like the JavaDoc + PL/SQL Markdown Doc.
And hey, those 20 items in your backlog? Leave them for now, and quickly go document that clever little code snippet you just created. You, and your future colleagues, will thank you for it.
A few documentation tips:
- Write when it’s still fresh in your mind. No time like the present!
- Document your code changes.
- Keep it simple. Not every line needs documentation.
- Create a quick start document for all key project assets: links to environments, code repos, JIRA boards, etc. This helps onboard new team members efficiently.
- Keep your documentation easy to find.
3. Source Control
I still remember years ago, at a Kscope event, one of the presenters asked the audience how many of us used the database as our code repository. I didn’t dare raise my hand, and as I looked around the room, neither did anybody else.
The speaker (affectionately) called many of us liars, and, at least speaking for myself, he was right. But I have learned the error of my ways.
Source control is a system that allows you to track changes to your code over time. This is important for several reasons:
- It enables you to go back in time and see how your code has changed.
- You’ll be able to track who made which changes, and when.
- Merging multiple developers’ work becomes possible.
If you’re using the database as your source control, you’re definitely doing it wrong. And don’t worry, I won’t make you raise your hand.
Some source control tools to consider (not an exhaustive list by any means)
A few source control tips
- Commit your code often so that your repository stays up to date.
- Use meaningful commit comments so that you, and others, understand why you made your changes.
- Don’t forget to merge your work with your colleagues. This will help avoid conflicts down the line.
4. DevOps and CI/CD
DevOps is a set of practices to build, test, and release your code in small, frequent steps. One of the core practices of DevOps is Continuous Integration, which has developers committing to a shared repo frequently, often daily (see Source Control tools above).
At Insum, we’ve developed a methodology for Oracle APEX deployments using a starter project on Github.
Many steps in our Oracle APEX deployments are automated, making them highly efficient. As a result, it improves the speed and stability of our deployments.
We recently shared the starter project template on APEX Instant Tips. Watch the replay below!
DevOps tools to consider
5. SQL and PL/SQL Standards
Although APEX is a low-code development platform, we rarely get away with any projects that don’t involve at least some code.
Many, in fact, involve quite a LOT of code, and chances are, yours will too.
SQL and PL/SQL code is fundamentally some of the most important code that Insum writes for its customers and partners. The difference between SQL and PL/SQL that performs well and that SQL and PL/SQL that doesn’t can be the difference between a successful system (our customers and partners) and a huge disappointment.
We were lucky enough to welcome Steven Feuerstein to Insum last year, and he’s helping us all level up our code.
But whether you like your code lowercase or uppercase or prefer your table names singular or plural, or have particular preferences about code formatting, it’s so important to have standards that are documented and implemented throughout your codebase.
Our Insum SQL and PL/SQL coding standards are adapted from Trivadis.
And whatever your standards are, just make sure they are well communicated to your entire development team.
Watch Steven review Anton's Code
6. SQL and PL/SQL Testing
When planning your project, make sure you include unit and regression testing of your SQL and PL/SQL code. Build this into your project timeline, and set client expectations around it.
Here are some options to consider for your SQL and PL/SQL Testing:
- utPLSQL is the best and most popular open source framework for testing backend Oracle Database code.
- SQL Developer unit testing. It’s not as powerful as utPLSQL, but it is integrated into the IDE.
- Ad-hoc testing, meaning that you don’t have any test scripts that can be run as needed. Instead, you run scripts and queries to verify that expected use cases succeed.
- Custom-built test harness
Samuel Nitsche, a member of the utPLSQL team, joined Steven for a lively conversation about the obstacles we face when testing our PL/SQL code, and how to get around them.
7. APEX Development Standards
Having a set of Oracle APEX development standards and best practices that you adhere to will ensure you create scalable, secure, and visually appealing applications.
Here at Insum, we have a set of APEX Coding Standards that we make sure all developers follow as much as possible. Of course, every project is different, and if a client has different standards, we’ll work with those.
But again, standards ensure consistency across your team and various applications and set you up for success for years to come.
Examples of APEX Development Standards might include:
- All pages should have authorization schemes
- All items should have help text
- Always ensure special characters are escaped
Looking for a good start for your own standards? Check out Jan’s post: APEX Blueprint
Check out a recent episode of APEX Instant Tips, where Anton and Hayden discussed a packaged app called the APEX Standards app.
8. Error management / Code instrumentation
Decide early how you’ll handle application errors and instrument your code.
There is no single right way to do this, but make sure you decide how you’ll handle different types of errors that may (and will) occur in your production applications.
For years, we’ve used and recommended the open-source utility Logger, and it’s still a fantastic utility with lots of great features. However, the APEX team has been building more and more features into the apex_debug package, and we’ve recently added a tool called Insum Debug to our code base and toolkit to use on our client projects.
It’s a wrapper of sorts for apex_debug and allows us to track and handle errors in a number of ways.
Again, as with many of the points listed above, it’s important to make a decision for your project and handle errors elegantly in ways that won’t have your users scratching their heads at what went wrong.
You might be interested in checking out a number of videos from our Instant Tips series that discussed error handling, debugging, and code instrumentation techniques
9. Auditing / Journaling
Many times, audit columns on tables will suffice. When was the record created, and by whom? Who last updated it, and when?
But when audit columns aren’t enough, and you need a detailed trace of changes, including old and new values for all changes, you’ll need to decide on an auditing strategy.
The good thing is that there are lots of great options available that all likely give you the auditing capabilities you’ll need.
At Insum, we definitely recommend using of Flashback Data Archive whenever possible since it’s fully supported by Oracle, and automatically adjusts to changes in tables.
When you can’t use FDA, Connor McDonald has an audit trigger/package generator for Oracle tables that we like a lot.
SQL*Developer Data Modeler also offers journaling functionality.
Of course, we’ve seen (and also implemented) many different auditing utilities for different requirements. Again, ask yourself early enough in the project what sort of auditing you’ll need. You’re sure to find one that suits you.
Here are some resources you might want to check out if you consider auditing or journaling your Oracle APEX application.
10. Security audit / Scanning
Last, but DEFINITELY not least, rounding up our top 10 elements to include in your next Oracle APEX project is a security audit.
There are a number of common threats to any modern web application that can put your data and your users’ data and privacy at severe risk. You may be interested in learning more about the top 10 OWASP document, representing a broad consensus about the most critical current security risks to web applications.
Developers should understand basic concepts about securing their Oracle APEX applications, but there are also a number of tools available that will highlight flaws and potential risks.
Developers should always run the APEX Advisor against their applications and, even better, leverage tools like APEX Sert or APEX Sec to perform complete scans and help developers address any security flaws before deploying applications to production.
Scott Spendolini gave an extremely thorough presentation mapping OWASP’s top ten security vulnerabilities to Oracle APEX during the APEX@Home event. For each relevant vulnerability, both potential issues and solutions were discussed. A must-watch!
With the help of our InTrack app, we’re currently looking at all our client projects, regardless of where they are in the development life cycle, and tracking which of these elements we’ve got in place and where we might be able to improve.
And while customer policy may dictate some of our choices, we certainly aim to check the boxes against each of these main categories. This helps us ensure we deliver high-quality Oracle APEX applications that delight end users.
