Skip to main content

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++.


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Working With JSON Arrays in PL/SQL

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks. In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods. Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods. Here are the methods you are most likely to use: A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL: Error Handling Behavior By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not  raised back to your block. If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greate