How to start development for Oracle DBMS correctly

Benefit from a powerful IDE designed with database programming in mind

There are many editors and many IDEs designed to work with the Oracle DBMS. Of course, you can use Notepad, but the loss of efficiency will be monstrous. You can also use any of the popular editors, for example, Sublime, and configure it to work with Oracle.

But I will suggest you, however, to download and install the free and powerful IDE developed by Oracle itself: SQL Developer.

Enable compiler and PL/Scope warnings.

The Oracle database has a ton of useful functionality built into it, just waiting for you to use it. For example, Oracle can issue comments (called "compiler warnings") when compiling PL/SQL program modules, which can improve the quality and performance of the code.

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

Most developers have no idea about these features and leave them disabled. Here's what I would suggest to users of the SQL Developer environment:

Open the Preferences menu item and type “compile” in the search field. Then change your settings to the following:

In other words:

1. Turn on all warnings.

   Thus, when compiling any software module, Oracle will provide advice on how to improve your code.

2. Treat all "severe" warnings as compilation errors.

   If the PL/SQL development team believes that these warnings are critical in one way or another, then it is desirable that they are not included in the production code. By setting this parameter to ERROR, we ensure that the code will not compile if they are present.

3. Increase the optimization level to 3 (this is all the necessary settings plus embedding the subroutine code).

   More importantly, ensure that production-ready code in your development environment compiles at the same level of optimization by any means necessary. Check out the following tutorial, written by the PL/SQL team, for more detailed instructions.

4. Enable PL/Scope utility

   It allows you to query your code for information about naming conventions, suboptimal code, and performance improvements.

   You can find useful information and helper utilities for PL/Scope on LiveSQL and GitHub .

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 errors. You will need to trace the execution of the program and also log these errors to prepare the code for production and ensure its stable operation in production.

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