Looking for great PL/SQL

Steve Yegge is a developer who writes way too much. Each one of his blog posts can easily take an hour to read, but they tend to contain nice gems that make the long read worth the time.

Today I spent an hour reading about 50% of a fairly interesting article about how to practice programming. His main point is that just programming on the job will not make you a better programmer. You have to actively strive to improve. (I just saved you an hour of reading!). One of his suggestions on how to become a better programmer is this:

Read through someone else’s code for 20 minutes. For this drill, alternate between reading great code and reading bad code; they’re both instructive. If you’re not sure of the difference, ask a programmer you respect to show you examples of each. Show the code you read to someone else, and see what they think of it.

Sounds like a good advice. Now I only have to find great and terrible code examples. Well, if I want to practice C, this is not an issue – for great code examples I read Linux Kernel code, and for awful examples I read code that I wrote 7 years ago (and it looks worse every year).

But for PL/SQL , I’m a bit stuck. I can still read my own code for bad examples, but where can I find examples for great code? My colleagues write nice enough code, but we have our own coding style and standards, and if something is wrong with these, it’ll be difficult to tell without outside input.  Somehow, there is simply no open-source code written in PL/SQL that I can read to get a good idea of how PL/SQL should be written.

The closest I’ve found is the code examples in Steven Feuerstein’s PL/SQL book. These have been extremely helpfull in teaching me how to handle exceptions, iterate through cursors, work with associative arrays, etc. But these are just examples, I can’t learn how to cleanly build a fairly large working module from these great examples. Its nothing like reading through the Linux kernel.

I think the issue is somewhat related to the problem Tyler Muth raised about lack of reusable PL/SQL modules. Oracle has an amazing community, but for some reason we don’t share our code and we don’t participate in open-source community projects. Most of our community efforts revolve around helping each other learn new features and solve performance issues.

We are probably missing an opportunity to help each other resolve performance problems before they occur.

8 Comments on “Looking for great PL/SQL”

  1. eknoyon says:

    For years I have been fascinated with structured Pascal programming language. I craved it to be one of the finest popular languages in the world. When Delhi came out with a new morph, I saw a shiny morning for a good day to come. Alas! It didn’t happen. Pascal remained mostly unattended and unrecognized.

    But one thing is very much explosive about Pascal. It influenced so many superseded programming languages.

    In later years, when I worked with PL/SQL I just wondered how exactly they implemented Pascal as PL/SQL with just different keywords.

    I think by now you got me quite right. To be good at PL/SQL, learn Pascal first. Once you are acquainted with the language, read PL/SQL libraries and keep a sharp index in your mind about what is where. You don’t need to memories all library functions, just keep and index in your mind.

    If you find this comment useful, may be I have more to tell you, um, yea, and may be some another day.

  2. Chen

    The contents of $ORACLE_HOME/rdbms/admin contain some examples both of greate and not so great PL/SQL – and indeed sql. Well worth reading IMO



  3. prodlife says:

    Hi Eknoyon,

    Why specifically Pascal? Wouldn’t any procedural language do?

    IIRC, PL/SQL was actually modeled after Ada. The types/objects/subtypes system is very similar to what Ada has.

  4. prodlife says:

    Hi Niall,

    Great idea! I really should have thought of it before 🙂

  5. chet says:


    For the past three years or so I’ve read The Daily WTF. Surprisingly, I’ve realized that it has helped me out tremendously…in what NOT to do. Now, there aren’t a lot of PL/SQL examples in there (and really few database specific examples), but that shouldn’t stop you from reading.

    I’m with Niall too…there are many good examples (good and bad) in the rdbms folder!


  6. DDL, have a look at Steven Feuerstein contributions 🙂

  7. […] with Oracle stuff, Chen Shapira (just a simple DBA on a complex production system) is looking for great PL/SQL. Why? To become a better PL/SQL programmer. “But,” she writes, “for PL/SQL , […]

  8. Mohamed Houri says:

    I wrote a procedure that can be easily re-used with very little modification when doing oracle initial load

    You can access it here


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s