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

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…

Resources

Download presentation: odtug2006_PLSQLDesignPatterns.zip (5.8 Mb)