How to get started with Oracle DBMS development

We bring to your attention a translation of an article by Stephen Feuerstein , an author of books and a specialist in Oracle and SQL DBMS. By "Developing for Oracle" I mean writing code in SQL or PL/SQL. In this article, I'm assuming that you have access to an Oracle database (which can be accessed through Oracle cloud services, as well as Docker, GitHub, and OTN).

Take advantage of a powerful IDE designed with database programming in mind

There are many editors and many IDEs designed to work with the Oracle database. Of course, you can use Notepad, but the loss in efficiency will be monstrous. You can also use one of the popular editors, such as Sublime, and configure it to work with Oracle.

But I will suggest you, however, download and install Oracle's own free and powerful IDE: SQL Developer.

Turn on compiler and PL/Scope warnings.

The Oracle database has a lot of useful functionality built right in, just waiting for you to start using it. For example, Oracle can issue warnings (so-called "compiler warnings") when compiling PL/SQL program modules to improve code quality and performance.

In addition, the PL/Scope utility - if enabled - collects information about identifiers and (in version 12.2) SQL statements. This allows for a very impressive analysis of required code changes.

Most developers are unaware of these features and leave them disabled. Here is what I would suggest to users of the SQL Developer environment:

Open the menu item Preferences ("Settings"), type in the search field "compile". Then change your settings to the following:

In other words:
1. Turn on all alerts.
  
  Thus, when compiling any program module, Oracle will give advice on how to improve your code.
2. Treat all severe ("severe") warnings as compilation errors.
  
  If the PL/SQL development team believes that these warnings are critical in one way or another, then production code should not have them. By setting the value of this parameter to ERROR, we guarantee that the code will not be compiled if they are present.
3. Raise the optimization level to 3 (that's all the settings you want plus embedding subroutine code).
  
  And more importantly, by all means ensure that production-ready code is compiled in your development environment at the same level of optimization. See the following manual written by the PL/SQL development team for more detailed instructions.
4. Turn on the PL/Scope utility
  
  It allows you to query your code for information on naming conventions, sub-optimal code, and performance improvements.
  
  You can find useful information and helper utilities for PL/Scope on the LiveSQL and GitHub sites .

DO NOT delay making decisions about logging and tools.

Before you start writing your next program, accept that your code will inevitably be full of bugs. You will need to trace program execution as well as log these errors in order to prepare your code for production and ensure that it runs smoothly in production.

To do this, you'll need a logging tool, and I recommend you use the widely used open source tool Logger, available on GitHub.