Your Top Tips for PL/SQL Developers?

I will giving a presentation to Oracle Corporation PL/SQL developers (that is, Oracle employees who build internal apps using PL/SQL) in September, lots of them new to the language and starting new development.

The focus of the presentation is on best practices: what are the top recommendations I want to give developers to ensure that they write high quality, maintainable, performant code?

I plan to turn this into a checklist developers can use to stay focused on the Big Picture as they write their code.

So if you could pick just THREE recommendations to developers to help them write great code what would they be?


  1. 1) Instrument your code.. at some point you will need to know exactly where your time is going
    2) Don't process 1 row at a time.. get in the mindset of processing sets of rows
    3) Avoid EXCEPTION WHEN OTHERS if at all possible.. bugs that are incorrectly trapped are very hard to track down

  2. 1. Look for opportunities to build and reuse common APIs.
    2. Use bulk processing.
    3. Learn everything you can about SQL (analytic functions, etc.) There are many times we could handle things in SQL instead of writing it in PL/SQL.

  3. 1. learn pl/sql best practices
    2. apply pl/sql best practices
    3. repeat pl/sql best practices

  4. General Tips:

    1. Single Responsibility Principle. (SRP)
    2. Don’t Repeat Yourself. (DRY)
    3. Keep it Simple, Stupid. (KISS)

  5. 1) Never (ever) repeat a piece of code. Refactor it into a separate program if you need it more than once.
    2) Learn what SQL can and can't do.
    3) Use the database to it's max. If there is a feature available in your database, use it. It's probably more efficient than anything you will ever write (if not, let the guys at Oracle know ;-))

  6. As PL/SQL is continually evolving, individual programming techniques have can have a limited shelf life.
    Basic principles of good software development are rather less mutable. So...

    Think like a User - the success of your application will be directly related to how easy it is for those who use it to accomplish their goals.

    Think like a Scientist - validate hypotheses and assumptions about the best way to accomplish something.

    Think like a Support Engineer - can another programmer easily work out what your code is doing simply by looking at the code and the comments ?

    I know you asked for three, but I feel that this one is just as important...

    Think like an Attacker - explore ways in which your code could be manipulated to do something it's not designed to and how you can harden it to eliminate such vulnerabilities.

  7. Hello All,

    In continuation of the Anti-Kyte's previous post:

    Reading this post raised in my mind something related to Oracle Forms:

    This old good product that I still love so much is however different from other tools:

    To put it simply, it allows you by default to do almost anything "out-of-the-box",
    and, for coding your application, you have to prevent illegal actions or outcome from happening.

    So, the code that you write is "two-faced" : On the one side you write code to perform actions, and on the other side you write code to prevent actions (or, "attacks", for being in tone with the previous poster).

    From my long experience, writing code to perform actions is usualy much easier than writing code to prevent actions, and here is where one's imagination can be endless in building "methods" or "patterns" for different scenarios.

    Adding such "prevention" code is sometimes an "historical" process,
    because each new version offers features that can make your own standard methods more elegant ... and then you cannot resist adding that elegance
    even to older applications' code.

    If I were to add a personal advice to any developer in general ( and PL/SQL developer in particular ), it would be to always be ready to improve your own or others's code by making use of newer features or more interesting and/or efficient ways to accomplish things ... even if that code is already "closed".

    By the way, back to the name of the previous poster,
    I just wonder what is the meaning of "Anti-Kyte" ?!?!?!?

    Since I am one of the so many "Kyte worshipers" in the Oracle world,
    you can imagine why I am curious :)

    Thanks a lot & Best Regards,
    Iudith Mentzel

  8. @iudith,

    if you want to know the gory details of how I adopted this particular monicker, then by all means click on my name for the link then look at the About page.
    No spoilers but it's probably got something to do with having to read too many large volumes of Coding Standards documents as a child :)

  9. @iudith,

    if you want to know the gory details of how I adopted this particular monicker, then by all means click on my name for the link then look at the About page.

    It's probably got somehting to do with reading too many huge (and out-of-date) Coding Standards documents as a child :)

  10. Thanks for all your ideas. Now it is time to put together my presentation for the Thursday webinar. I will, of course, make this presentation available to all, afterwards.


Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Get rid of mutating table trigger errors with the compound trigger

Quick Guide to User-Defined Types in Oracle PL/SQL