Thursday, July 6, 2017

Three tips for getting started right with Oracle Database development

By "Oracle Database development", I mean, more or less, writing SQL and PL/SQL. I assume in this post that you have access to Oracle Database (which you can get via Cloud services, Docker, GitHub and OTN).

A. Use a powerful IDE, designed with database programming in mind.

There are lots of editors out there, and many IDEs that work with Oracle Database. Sure, you could use Notepad, but OMG the productivity loss. You could also use a popular editor like Sublime, and then it get it working with Oracle.

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

If you like to complement your graphical IDE with a command line tool (or OMG if you actually prefer a command line tool to a graphical interface), you should also check out the relatively new and generating-lots-of-excitement SQLcl.

B. Enable compile-time warnings and PL/Scope.

The database has tons of useful functionality burned right into it, ready for you to use. For example, when PL/SQL program units are compiled, Oracle can give you feedback (aka, "compile-time warnings) to improve the quality and performance of your code.

In addition, PL/Scope - when enabled - will gather information about your identifiers and (in 12.2) SQL statements. This will allow you to do some very impressive impact analysis of your code.

Most developers are not aware of these features and so leave them turned off. Here's my suggestion for SQL Developer users:

Open up Preferences, type "compile" in the search field. Then change your settings to match these:

In other words:

1. Enable all warnings. 

This way, whenever you compile a program unit, Oracle will give you advice about ways to improve your code.

2. Treat all "severe" warnings as compile-time errors

If the PL/SQL team thinks these warnings are critical in some way, then I want to make my production code is free of such warnings. By setting this caregory to ERROR, I ensure that the code will not compile unless it is "clean". 

3. Tweak your optimization level up to 3 (all the good stuff plus subprogram inlining).

And even more important, take whatever steps are appropriate in your development environment to ensure that production code is compiled at this level of optimization as well. Check out this guidance from the PL/SQL dev team for more details.

4. Turn on PL/Scope.

You can then execute queries against your code to get information regarding naming conventions, sub-optimal code, and opportunities for performance improvements. 

Resources to help you with PL/Scope may be found on LiveSQL and GitHub.
Important Note: These are settings for use in DEVELOPMENT - and they will be applied to all connections made in SQL Developer. When you deploy to production, you should use a script that explicitly sets values for warnings, optimization level (still 3) and PL/Scope (off).
C. Decide RIGHT NOW on logging and instrumentation.

Before you start writing you next program, accept this reality: your code will be full of bugs. You will need to trace execution as well as log those bugs, in order to get your code ready for production and then keep it running smoothly in production.

You need a logging utility for this, and I suggest you use the open-source, widely-used Logger utility available from GitHub.


  1. Hello Steven,

    It might sound a little cynical from my side,
    but to me personally it looks like mastering PL/Scope at a level that would allow some really useful and deep code analysis
    is far more demanding than mastering all the other best practices of writing good, correct and efficient code.

    This goes "inline" with the fact that in most programming languages, mastering the debugger is much more difficult a task
    than never needing it at all ...

    A chicken and egg story ...

    Regarding the "clever" development tools ...
    As the old-fashioned that I am, all these IDE-s came in too late
    for me ... delving into any one of them would have reduced my already gained usual productivity back to zero ...

    I strongly believe that writing code using a "fashion-less"
    code editor like Notepad does "gym the brain" far stronger
    than any IDE tool that stops your thinking flow each moment with all kinds of bells and whistles attempting "to help you keep the spoon near your mouth" ...

    But, yes, those who start out today might think differently ...
    but only the exam of the results could really confirm which
    "thinking school" was better ...

    Thanks a lot & Best Regards,

    Thanks a lot & Best Regards,

    1. "mastering PL/Scope at a level that would allow some really useful and deep code analysis is far more demanding than mastering all the other best practices of writing good, correct and efficient code."

      Good point. We at Oracle need to build PL/Scope *into* products like SQL Developer, and not expect people to run their own queries, etc.

      But I don't think it's either-or. I am "old school" but I think that to ignore at least the basic productivity boost you get from an IDE over a "plain" editor is to sacrifice too much.

      And denying yourself some of that power does not, I think, lead you to a weaker knowledge of the language, or more careless application of it. Just the opposite - for example, if you follow my advice to use the compile-time warnings.

  2. Hi Steven, recently discovered SQLcl and think it deserves a mention as well, great tool for use within the terminal. I typically use screen (alternatively tmux) to create a window dedicated to SQLcl, and a window for Vim and I'm good to go for most of my day to day tasks (which typically includes writing ad hoc queries, writing/editing plsql code, and some shell scripting). The big benefit for me is that I don't have to leave the terminal (hovering and clicking, ugh) and have fast access to all my favorite tools, e.g. awk, git, screen, sed, etc...

    1. Great suggestion, Jack. I will add that under the first tip.

  3. Like iudith, I like to write PL/SQL in notepad, but I also like JDeveloper for debugging and comparing files, which are very useful. I recently became aware of the SQLcl at our local Oracle user group meeting.

    1. Notepad?!? I hope you at least meant Notepad++.