Why are there so few resuable PL/SQL components – Discussion triggered by presentation on PL/SQL Design Patterns

Lucas Jellema 7
0 0
Read Time:9 Minute, 12 Second

While the Java – and even the .NET, Ruby, Perl, Python, PHP – arena is teeming with open source projects and reusable components, the PL/SQL scene is quiet. Why is that?

This question was one of the discussion items provoked by the dress rehearsal of the ODTUG 2006 Presentation: PL/SQL Design Patterns – pre-inventing the wheel. This presentation focused on the general concept of Design Patterns, some specific examples from adjacent technology domains and their possible applicability to the PL/SQL realm of programming. Here too we found that for virtually any popular technology stack and programming language, there is an abundance of Design Patterns, implementations of patterns and books about them, while everything seems quiet on the PL/SQL front.Again: why?

This article will not give you an answer. However, we wil discuss some of the possible causes – and you are invited to join in and add your two-cents… Perhaps this discussion can lead to a more explicit, vibrant collection of PL/SQL design patterns as well as reusable components. We can do that too you know!


At stated before,  the Java/J2EE world is full of open source projects, highly active forums, zillions of high- and low-profile blogs, generally accepted design patterns and standards, very visible and sometimes somewhat inflated egos and basically a lot of exchange of ideas and actual code – libraries, frameworks, components, snippets, plugins etc. Whatever you may think about Java, that is on the whole a very positive thing. It is not limited to Java however, though Java is the quintessential example it would seem.

Open source code, resuable components en certainly Design Patterns embody Collective experience and conscience and Shared, Reusable guidelines of the entire developers community. Java/J2EE is known for its Massive volume of Internet Resources, Thriving open source community, Intensive exchange of strong views (and language), Java Community Process that very openly and somewhat democratically directs development of Java and J2EE, Interesting assembly of huge egos etc. Apparently this results in a substantial number of well established Design Patterns – and a truckload of books…

Why does our world of PL/SQL Developers not have the same reuse of code components, shared standards and guidelines and overarching design patterns?

Why not does PL/SQL…

We can look at several reasons why PL/SQL does not have that active community producing reusable components, volumes of coding standards, open source projecs etc.

The PL/SQL Language is not up to it

Of course the PL/SQL language does not promote reuse in the same way Java does. Some of the key Java characteristics, such as inheritance, interfaces, polymorphism, enforced declaration of thrown exceptions, reflection, dynamic proxies, extendable class-loading are a big help for creating reusable components. However, PL/SQL has some capabilities of its own. For example Dynamic PL/SQL opens up a lot of alleys when it comes to design time independence and run-time collaboration.

A fundamental difference between PL/SQL packages and Java Classes and Objects is that a single session in a Java JVM can have many many instances (objects) of a specific Class, whereas a single user session can only have a single instance of a package and its associated variables. Multiple sessions can each have their set of variables but cannot exchange information very easily.

PL/SQL and the Internet are not matched in heaven

More importantly perhaps: PL/SQL and PL/SQL Developer are from the pre-intermet era. While Javan and its practitioners were raised with and sometimes through the internet, using the Internet is not as natural for the PL/SQL community as it is for the Java people. OTN was only established in 1999 for example. Through MetaLink and OTN of course there is quite a lot of discussion and debate and there are many more site that publish information on PL/SQL and SQL, and yet it still is not a joint effort. There does not seem to be movement where people from different organisations and countries and even continents join forces, certainly not the way it happens with for example Open Source projects under the Apache umbrella.

Note that there quite a few open source initiatives in the Oracle arena. See for example: http://www.oracle.com/technology/community/opensource_projects.html and especially http://plnet.org/. I applaud them all. And there are some very valuable initiatives among these, such as Log4PLSQL, PLDoc, utPLSQL etc. However, most of these are tools and utilities. Not reusable components that become part of the applications we develop. They are instruments in building those applications. They are useful. But they are not as far reaching as projects like Struts, MyFaces and the likes. And they certainly do not have the impact of comparable tools in the Java world like Ant, JUnit, Maven, JavaDoc, Log4J etc.

It seems that only small groups of developers are associated with most of these initiatives. And more importantly: there is apparently a very tepid reaction from us: the PL/SQL developers in this world. Why don’t we jump at the opportunity to use these fine tools? What is holding us back? Ignorance? Fear (because of ignorance)? Our DBA or project leader? Our not-invented-here syndrom? Our I-can-do-better-than-that(-even-though-I-should-know-that-I-will-never-find-the-time) attitude?

Steven Feuerstein made a rich library of resuable PL/SQL components, called PL/Vision. It is made available for free by Quest Software. It is really good stuff. However, it does not seem to be used a lot.

Lack of Involvement from Oracle Corporation

To me the level of support Oracle Corporation is giving to open source and central standards and guidelines development is restraint at best. While touting their involvement with high-profile (open source) communities like PHP (Zend Core), Ruby, Linux, Apache, Berkely DB and of course Java (EJB 3.0, JSF, Glassfish, ADF Faces/MyFaces etc.), I do not see much going on at Oracle with regard to PL/SQL. There is no PL/SQL Community Process where developers help steer the course of the PL/SQL language (or at least not an open, internet-izedprocess). There is no infrastructure for the development or publication of standards or code especially for PL/SQL Developers (though we can use SourceForge).

The way we use Packages

Since PL/SQL packages are in the database and are usually associated with performing tasks on behalf of applications that use database tables, there often is no clear separation in packages between their data-oriented role and their application tasks. Many packages have dependencies on tables and views, even though much of their code is about processing data, calculating stuff and not about SQL Query or DML operations at all. However, since the database is so much at our fingertips, instead of neatly encapsulating database access in packages that have no other role than to access the database through SQL and having all other packages call these “data access service objects” , we sprinkle SQL statements and thereby hard-coded database dependencies throughout the packages. It is virtually impossible to disentangle most packages from their database and make them reusable in other environments. Separation of concerns, encapsulation of database access, reducing dependencies, injecting helper objects (see Design Patterns in PL/SQL – Interface Injection for even looser coupling< /a> ) and a better structure
d approach to PL/SQL programming could make a lot of difference!

It’s us – the developers, stupid!

There can be several reasons in ourselves why we PL/SQL Developers do not publish our work, create reusable components and actively engage in open source projects. For example:

  • we don’t want to – we worked hard to get something to work, we are not just going to give it away to the rest of the world – maybe if we have gotten something useful from the community
  • we are not allowed to – for commercial reasons, somewhat old-fashioned company policies, SOX related regulations, overzealous legal departements it may just be not allowed to publish the code we wrote for our boss; when I worked at Oracle for example, it was very clear that everything we created – even if we did so at the expense of a customer – was Oracle’s intellectual property. And that was to be just given away! That was back in 2002, perhaps things have changed at Oraclem, but even so there will be many companies where publishing code is just stimulated (tongue in cheek).
  • we (well, you…) are to modest to – perhaps in general – flaunt our work. Maybe the PL/SQL community does not care for thought-leaders, self-proclaimed gurus or even well-meaning code-sharers. And perhaps we do not feel comfortable publishing our code as if to suggest that it is of superb quality. We do not necessarily have to write books, exploit the personal feelings of our loved ones on weblogs, present audiences to death with our hobbies. Or we are scared shitless to do so.
  • we do not know how to do it – we have not been doing this open source thing; we have not been creating our PL/SQL packages for reuse. Frankly, it is hard enough to get the darned stuff working, without being bothered with reusability or even Code Standards and Desgn Patterns. And even if we had something to share, how do you do it? How do you start an open source project? How do you get the community involved? How do publicize your stuff?
  • there aren’t enough of us – the PL/SQL community is too small for this kind of thing. Or is it?

Should we want to have (more of) this community thing

Are we the hicks from the sticks compared to those Java guys for not having Design Patterns and Reusable Components and Open Source projects? Well that would be exaggerating. We can do without. However, to further professionalize PL/SQL Development in general and our own skills as professional software developers in particular, having more interaction with our fellow developers around the world, benefiting from experience gained by countless other PL/SQL experts, reusing code that solves our dullest challenges and seeing many others benefiting from my work – and giving me credit and thanks is not such a bad thing.

And leveraging ideas and approaches from other communities, like the Java community, is not such a bad thing either. Sure, we can teach them a lesson or two when it comes to proper database programming. But when it comes to sharing ideas, developing common understanding across organisational boundaries and even country borders, there is a world to be gained.

In the presentation that started this discussion, I borrowd four well established patterns from Java land and tried to apply them to PL/SQL programming. Whether or not that was a useful exercise, I am not sure. But the discussion that erupted and the brainstorm that we had was certainly valuable. Who knows what ball we started rolling…


Download presentation: odtug2006_PLSQLDesignPatterns.zip (5.8 Mb)

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
0 %
0 %
0 %
0 %
0 %
0 %

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

7 thoughts on “Why are there so few resuable PL/SQL components – Discussion triggered by presentation on PL/SQL Design Patterns

  1. Reusable components, systems, and libraries are generally written in languages like Perl, Java, PHP, Python, Ruby, or even C…and those all have another thing in common that hasn’t yet been mentioned in this thread:

    None are owned by a single vendor.

    (One could argue that Java is owned by Sun, but our industry generally perceives Java as non-proprietary.)

    My hypothesis is that proprietary languages like PL/SQL do not spawn reusable components “in the wild.” Programmers are not inspired to spend their free time on something that indirectly adds to the vendor’s bottom line. Also, in theory, the vendor could change or desupport the product in such a way that would break our code (not that I think that would really happen).

    I wish I was wrong about this…but can anyone name a single proprietary language that has spawned public repositories of reusable components?

  2. Hi,
    PL/SQL has a nice feature for real Reusablility – the package concept. A Modul with it’s own datatypes, public interface, and internal logic. Inheritance for example could make problems to reusablitly because of the misssing ineritance parent tree where the component should be located.

    One reason i think weak PL/SQL Desing are it’s users, mostley DBA and data oriented developers with no vision of strong procedural/modular design (which isn’t the focus of their role).


  3. What a tremendous blog – such an incredibly long overdue topic! Perhaps like ANSI joins and the CASE statement, practices such as these will finally penetrate the Oracle world, albeit years after being accepted in other arenas. Better late than never I suppose, and I feel there have been strides taken by Oracle of late to aid in this movement. The recent release of two products, SQL Developer and Oracle Database 10g Express Edition, is further evidence that the company is finally interested and aware of the ever growing and important market of open source database application development. While the source code of the database in itself is not open to the world, the product does entail one essential aspect of open source products – its price. Free licensing, along with the newly released SQL Developer tool (unfortunately released with many bugs), stands to increase the appeal of Oracle application development and consequently PL/SQL. Furthermore, the ingenious move to cater to aftermarket, custom java extensions stands to increase the allure of the product exponentially. It is this step that I hope will bring the open source mentality to PL/SQL development and perhaps finally bring discovery of the true power of the open source community. I’m on board and ready for launch. So where do we go from here?

  4. Actually, I am really surprised that Oracle doesn’t encourage using PL/SQL as much as possible. Since any PL/SQL burns some CPU, active usage of PL/SQL generates pure profit for Oracle as main licensing scenario is per CPU.
    On this basis I believe that using complex PL/SQL is quite expensive unless it saves a lot of runtime and/or various resources (human and hardware) compare to “remote” (i.e. not running on DB -tier) implementations.

  5. Steven,

    On QNXO: I was on the verge of mentioning it. Two things held me back: it is not free (though quite cheap) and for some reason that made he hesitate. Second of all: I really do not know enough about it, I am ashamed to admit. So what I did do, after posting the article but before you had a chance to react was ask two colleagues to make an investigation into QNXO, write a Weblog Article and prepare an internal presentation and workshop. The result of the latter will hopefully be a) good insight in what QNXO can do for us b) a lot of support from the developers in our company and c) the decision to actually acquire it and start using it. When we do so, we will definitely write about our experiences on the weblog.

    I am looking forward to seeing you again at ODTUG later this month.


  6. Great post (me DBA ;=)

    Maybe it is time, to post and share all those (>>100) sql scrips and other small jewels I created myself or do credit to those I borrowed from. Even the other day i tried to make a simple PL/SQL task as generic and modulair as possible so I could re-use it in the future.

    Ahhh, but I am only a small DBA guy (pre-internet) – not so good as programmer guy, so maybe i shouldn’t share 😉

    Keep up bringen such important issues. Good food for thought (and hopefully action)

  7. Dear Lucas,

    Excellent essay! You raise important questions and offer some clues to answers. I sure don’t have a whole lot more of those.

    Thanks for the mention of PL/Vision. It does remain the only PL/SQL library and that is just crazy!

    I thought I would also suggest that readers check out Qnxo (www.qnxo.com). The name is awful, but the idea (and implementation) quite wonderful. Qnxo is essentially a design pattern factory and a repository for reusable code.

    I won’t go on about it; I don’t want to overstay my welcome on your blog promoting my commercial products. But it *does* address a number of issues you raise.

    Beyond that, I do think that there is a possiblity of creating a vibrant PL/SQL community. Perhaps we can chat at ODTUG in DC in a few weeks about some possible concrete next steps….


Comments are closed.

Next Post

Sorting PL/SQL Collections, the hard way, the intermediate way and the quite simple way (part one)

Bubble Sort, Quick Sort, Insertion Sort, Shuttle Sort… When time is short, these are not the easiest ways of implementing sort operations on your PL/SQL Collections. In this brief article – or at least I will try to be brief – we will discuss two smarter ways of sorting collections […]
%d bloggers like this: