Skip to main content

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?

Comments

  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

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

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

    ReplyDelete
  4. General Tips:

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

    ReplyDelete
  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 ;-))

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

    ReplyDelete
  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

    ReplyDelete
  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 :)

    ReplyDelete
  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 :)

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

    ReplyDelete

Post a Comment

Popular posts from this blog

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

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel