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. Craig MartinAugust 20, 2014 at 12:37 PM

    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. amy cAugust 20, 2014 at 7:03 PM

    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. astramieriAugust 21, 2014 at 1:04 AM

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

    ReplyDelete
  4. AnonymousAugust 21, 2014 at 2:11 AM

    General Tips:

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

    ReplyDelete
  5. Patrick BarelAugust 21, 2014 at 2:28 AM

    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. AnonymousAugust 24, 2014 at 10:06 AM

    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. iudithAugust 25, 2014 at 11:12 AM

    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. AnonymousAugust 31, 2014 at 12:15 PM

    @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. AnonymousAugust 31, 2014 at 12:16 PM

    @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. Steven FeuersteinSeptember 8, 2014 at 6:06 AM

    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

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

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, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more