Reusable PL/SQL
Posted by Tyler Muth on June 1, 2008
Last week I was talking with a colleague about some upcoming projects. Some of them involve some pretty challenging concepts like repeating calendar events. He asked me if some of these concepts had already been written so we could get a head start on the projects. While I know they have been written before (I worked on a calendar with the APEX team back in the day), I can’t say that the code was very reusable beyond the specific project they were written for.
When I look at other languages like Java, Python, Perl, PHP, etc., I see a lot of reusable libraries that are common in those communities and simply imported or referenced in many projects. Outside of the PL/SQL libraries that ship with the database, I just don’t see a lot of this in the PL/SQL community. Don’t get me wrong, there is a lot of PL/SQL sample code out there which is very helpful, but it’s typically not a library that I simply import and call, more like a block of code that I would re-write for my specific project.
So, a few questions for the community. Am I on to something here about the community as a whole, or is it just my own coding style that needs to become more reusable? If this is an issue, is it a symptom of all procedural languages? Could it be that a big part of the problem is that PL/SQL is compiled in a schema right next to tables, so it’s just too easy to couple your logic and DML, thus making your code less reusable?
Tyler Muth's Blog 
Doug Gault said
While I agree that there is not the plethora of libraries like there are for other languages, there are some reusable libraries out there. Just a few for example (available on SourceForge) are :
utPLSQL – Unit Test Framework
Hotsos ILO – Code Instrumentation and Tracing
log4plsql – Logging Framework
PL/SQL XML Compare – Utility to compare 2 XML files and return the difference.
In fact I believe I remember that QUEST had put together a site where reusable PL/SQL code could be uploaded and shared with the world.
What ever the case, I agree with you that it is far to easy to create non-reusable code, partially because it’s so close to the data, and partially because us old folks that have been writing PL/SQL since Dinosaurs Roamed the earth (Pre-JAVA) are so used to doing it that way, and it’s hard to break out of that mold.
Daniel McGhan said
Tyler,
I’m developing something called tapiGen. It’s an open source PL/SQL table api generator that works great with ApEx. I expect to have the first release out within the next week.
I see a lot of reusable PL/SQL code out there – just think about the ApEx packages 😉 I think one of the problems that goes with writing code that can be reused by everyone is that it must be very feature rich, filled with all kinds of options to meet the various end user’s needs, and that takes a lot more time to develop and can eventually lead to a little performance degradation.
I wish someone would tackle the repeating calendar events! I’ve had to work with that one a lot. Perhaps I’ll start another project for that when I get the first release of tapiGen out.
Regards,
Dan
Tyler Muth said
Doug,
I’ve used log4plsql, but it hasn’t been updated since 2004, needs some changes for 10 and 11g, and is a bit challenging to use, especially for a logging framework. utPLSQL hasn’t been updated since 2005. I remember Quest promoting an initiative like this but I see a slight problem with that. Oracle and Quest are competitive partners. We rely on each other, but also regularly compete against each other. I can see a lot Oracle employees being reluctant to contribute to Quest hosted projects, just as I can imagine some Quest employees may not want to contribute to Oracle hosted projects. IMHO, sourceforge is a nice neutral ground where this code should be hosted.
I do agree with you that these individual projects were in the spirit of what I was talking about. I wonder if a central repository on sourceforge for a bunch of different PL/SQL libraries would get better traction?
Syed Zainul said
I want to learn ORACLE please tell me the best book.
Thanks & Regards
Tyler Muth said
Daniel,
Does tapiGen generate database tables (create table foo…) or PL/SQL “Arrays”? I’d love to hear more about it. Also don’t forget about the extensible nature of SQL Developer, if it makes sense as a UI for tapiGen.
There’s a 99% chance I’ll be tackling the repeating calendar issue in the next 2 months. I will absolutely make sure it’s a reusable library, as we actually need it for 2 projects.
John C said
I think it’s telling that all the examples you give (Java, Python, etc) are free not only for developers, but for end consumers as well (at least in effect). Oracle is most emphatically not free for the end user! So much of what people do with PL/SQL, because it’s Oracle-based, is for proprietary development. Thus there’s not a lot of drive from communities to release open-source, reusable libraries – all my PL/SQL code is the IP of my company.
The correspondence, I guess, is with SQL Server and T-SQL. Being an Oracle guy I don’t know for sure, but I’d imagine that suffers from a lack of community libraries as well. mySQL, not having its own procedural language, has rich libraries in each of the languages you mention.
Dimitri Gielis said
Hi Tyler,
This is a question that we get a lot of time.
The last weeks I’ve also started to do research again on all available tools to help.
In APEX (SQL Workshop) there’s a table api generator. If you create a package it gives you the option to base it on a table and it will generate already some code for you.
About reusable components for PLSQL, I see some different arrays:
– Error and Exception handling
– Audit and logging
– Globalization and localization (important in Belgium as we speak multiple languages)
– Testing
I guess all companies/developers have a code library. Some well known were written by Steven Feuerstein and Quest, but there’re some others too.
It would be nice to have some APEX related plsql libraries…
Definitely an interesting topic to get into.
Dimitri
Todd said
Tyler et al,
I feel that while other languages (i.e. Java, Python and Ruby) have adopted architecture that separates the business logic from the data logic (i.e. MVC), PL/SQL has been left in the old school procedural ways of coding. Due to PL/SQL practitioners not progressing in MVC architecture styles and designs patterns, most of the code written in PL/SQL becomes proprietary and deeply tied to the data structures for which it is written.
-TP
John Scott said
Tyler,
I completely agree, it’s an area that is really lacking in the PL/SQL community (when compared to others).
However, I don’t believe it’s a technical issue, I see no reason why the PL/SQL plugin/reusable code community can’t be as rich as some of the others.
Perhaps it’s more to do with mind-shift and types of projects that the other technologies you mentioned are used for. For example there are a whole host of free/cheap blog/CMS etc type applications written in PHP/Ruby etc and those type of applications *need* plugins (like calendar components, RSS feed readers etc), so perhaps the people working in those areas are already focusing on ‘how can I write this to make it easier to share’.
Traditionally many PL/SQL based applications I see are either developed to be used in-house, or developed to be sold to end-customers and not built to be extensible by third-parties, that doesn’t mean it can’t/shouldn’t be done though!
John.
Stew said
I can’t say I have much to contribute to the “why don’t people provide shared code packages.” In my department, I think it’s because we mostly work solo on projects and don’t recognize that our utilities could be useful by others in the department. And if you can’t imagine that, I don’t think you would normally make the further leap that people outside your department would find them useful.
As to Daniel McGhan’s development of tapiGen, Steven Feuerstein developed a tool (CodeGen?) that does this, among other things. I believe that after Quest tried to sell it for a while, he eventually released it as freeware.
Tyler,
I think your idea here has a lot of merit:
“I wonder if a central repository on sourceforge for a bunch of different PL/SQL libraries would get better traction?”
It seems like there are so many sources out there for Oracle information that it’s difficult to find a library to solve your need; to the point it might as well not exist. So a common, vendor-neutral repository would be handy.
Actually, I wish there was a single repository for all the Javascript and AJAX scripts that can be used for Apex, something along the lines of FireFox add-ons library. Dimitri’s application is great, but Carl’s got one too and so do many others. I’d love to see an common application/site where an author could post their snippet(s) that solve a specific problem, maybe include a screenshot showing the result, and instructions on where to post the snippet(s) (report header, etc). Then allow other developers to comment.
Carl Backstrom said
>>
I’d love to see an common application/site
>>
Well one good place to aggregate links to all the different examples and tips would be the all this would be the official Oracle wiki.
I’ve started a couple pages http://wiki.oracle.com/page/Apex+Tips+and+Tricks and http://wiki.oracle.com/page/Apex+and+AJAX
anytime you run into an example just through a link and a brief description there, it is at least a start and consolidating a place to search for and look for examples.
Carl
Kevin Taufner said
Steven Feuerstein used to have a set of packages he had developed over time called ‘PL/Vision’, which was sold as a set of libraries. These were included with some of his books on PL/SQL programming from O’Reilly books for free. Honestly, since things have moved around a bit on the web, and his departure from RevealNet, I’m not sure what happened to those libraries, but they included quite a number of generically written functions.
Likely, as Carl had just stated, the best place would likely to organize some kind of Taxonomy for new code snippets on the Oracle sponsored Wiki. Then again, perhaps a SourceForge project is in order?
Kevin Taufner said
Ah, just found them again. I guess when Quest software bought out RevealNet, they also got the PL/Vision libraries. They are distributing them as part of their Toad set of tools, as freeware at:
Kevin Taufner said
Okay, goofy href didn’t work right… here’s the full path:
http://www.toadworld.com/Knowledge/DatabaseKnowledge/StevenFeuersteinsPLSQLObsession/MyPetProjectsandContributions/PLVisionCodeLibrary/tabid/316/Default.aspx
karthickarp said
Code reusability in oracle pl/sql
First to start with pl/sql is procedural language which is not object oriented. Once said that it implies that this language is not familiar with the concept of inheritance. I have been using pl/sql for quite some time. It’s a great programming language. It would be even better if it had the class concept in it.
Class concept would come handy when we want to build a framework which can be utilized over and over.
Currently I am working on a masking utility. We have different masking algorithms say…
Algorithm 1, Algorithm 2, Algorithm 3 etc.
The general framework of this masking is something like this.
1. Get the field from user which needs to be masked.
2. Get the master table for that field.
3. Load the distinct data for the field from master table into a staging table
4. Apply your masking logic on the staging table.
5. Find all the transaction tables that have this field in it.
6. Using the staging table update all the transaction table.
Here this is a very good candidate for class concept where I can have a parent class which will have the entire frame work of what I have given in six steps. And I can have one child class for each algorithm and in each child class I can just over write the step four where I can apply my masking algorithm. But unfortunately we don’t have it. Hence I went for package, Use some CASE statements and hooked in my algorithms and made it as optimal as possible. I basically hate writing lot of code. If Oracle can introduce OOP(Object Oriented Programming) in PL/SQL it would be of great use and even more flexible.
karthickarp said
Hi,
i came across your blog when checking out tom’s blog.
http://tkyte.blogspot.com/2008/06/seems-like-good-idea.html
found your postings to be very interesting. Keep up the good work.
Thanks,
Karthick.
http://www.karthickarp.blogspot.com/
Alastair Green said
@karthickarp (14)
Can’t you do this with OBJECTS in PL/SQL?
Tyler Muth said
@karthickarp (14)
While I believe that inheritance helps to promote reusable code, that doesn’t mean you can’t write reusable code in PL/SQL. If you simply moved your masking algorithms to their own package that did not include any DML, this would make it more reusable. For example, I’m going to start on a project that requires calendar functions such as repeating appointments and generating an iCal feed. Instead of burning these all into one big calendar package with my DML, I plan to build a calendar_util package which contains a bunch of utility functions that I can call. I can then release this calenday_util package to the public so the next person doesn’t have to write the repeating logic again.
John Flack said
There was a discussion about the possibility of creating a library of PL/SQL code at the 2006 ODTUG Kaleidoscope Conference. Steven Feuerstein was there as were other top PL/SQL developers. ODTUG does have an extensive PL/SQL SIG section on its website, http://www.odtug.com/. Several of us, including me, stated that their code was already available for downloading. One of the things that we did was to make sure that the ODTUG site has links to as many sources of PL/SQL code as we could find. My available code is at: John Flack Source Code.
By the way ODTUG Kaleidoscope 2008 is coming soon. It will be in New Orleans from June 15-19, 2008. More information is available at ODTUG Kaleidoscope. This is THE place to be for PL/SQL, ApEx, JDeveloper, Oracle Forms, Oracle Reports, Oracle Designer, Hyperion, and ANY tool used to develop applications against an Oracle database.
Steven Feuerstein said
Great discussion! A few thoughts from my experience with PL/SQL development:
** You can create object type hierarchies, with support for inheritance and dynamic polymorphism, in PL/SQL. There are still some weaknesses to Oracle’s OO support, but it’s at least recognizable and functional at this point.
** There is no inherent barrier to reuse in the world of PL/SQL, either through packages or object types. The barriers have much more to do with the culture of PL/SQL development. Our community never developed much of an orientation to using third-party libraries and utilities; PL/SQL dev shops tend to work in isolation, which is sad but true….
** I have created and published (mostly through Quest these days) a number of freeware utilities and code sources. In addition to PL/Vision (discussed above)….
Quest Error Manager – reusable error management framework (raise, handle, log errors, application tracing, assertion routines, etc.)
Quest CodeGen Utility – design pattern factory tool, which among other things generates table APIs.
Both of the above are available at:
http://toadworld.com/Downloads/ExclusiveToadWorldSoftware/tabid/78/Default.aspx
I also offer a “demo.zip” file that contains about 2000 scripts, some of it reusable code, from my PL/Obsession training materials page. These scripts are mainly useful/accessible by perusing my trainings on that same page and then finding links to the associated files.
PL/SQL Obsession: http://www.ToadWorld.com/SF
I totally agree that it would be fantastic to have a shared repository of PL/SQL code, but I have found it very difficult to get any momentum built around such an effort.
Warm regards,
SF
Steven Feuerstein said
MESSAGE TO TYLER: I submitted a note for posting here a few days ago. Did you not see it? Did you not post it for a particular reason?
Thanks, SF
steven@stevenfeuerstein.com
Steven Feuerstein said
Hmmmm. Well it sure worked that time; here I thought the posts needed to be approved! OK, I will repeat my posting from before; not sure what happened to it.
I think that the PL/SQL community is a very interesting one. Most of us are very heads-down focused on getting our jobs done, and pay very little attention to the bigger world “out there” of software development – whether it be PL/SQL-related utilities or even less likely non-PL/SQL initiatives like TDD (test driven development). There is also very little experience with or (perhaps from a management perspective) trust of third party free software.
Clearly, we SHOULD all be building our apps based on existing libraries. Just as clearly, it is going to be a very difficult thing to achieve.
Here are some free utilities and source code sets that I have made available:
Quest Error Manager – general error management framework. http://www.ToadWorld.com Downloads
Quest CodeGen Utility – general design pattern factory, with an emphasis on table API generation. http://www.ToadWorld.com Downloads
PL/Vision – library of 1000 reusable procedures and functions, but it is quite old at this point and not maintained. http://www.quest-pipelines.com
PL/SQL Obsession – my online portal for PL/SQL resources. Visit the “Trainings, Seminars…” page and download demo.zip, a set of 2000 scripts that are companions to my training materials.
Steven Feuerstein said
One other thing: PL/SQL does offer a minimal but functional set of OO constructs – object types not support inheritance, for example. But it will never become a TRUE OO language – and it doesn’t have to in order to promote and achieve excellent code reuse – through packages.
SF
Tyler Muth said
Steven,
The Akismet spam filter was a little aggressive with a few of the comments posted here. It’s usually pretty good, but clearly not this time. I really appreciate your input on this as you’ve been thinking about it and trying to solve it for a lot longer than I have. Did you have any legal issues with Quest as far as posting code? Is there any license attached to your code?
Daniel said
Tyler,
Sorry, I didn’t see your response until now. Actually, it doesn’t do either…
tapiGen looks for tables that meet the following conditions:
1. NUMBER OR VARCHAR2 column named “id”
2. VARCHAR2 column named “created_by”
3. DATE column named “created_date”
4. VARCHAR2 column named “modified_by”
5. DATE column named “modified_date”
6. SEQUENCE named TABLE_NAME_SEQ
For each table it finds that meets these minimums, a package is created. The name of the package follows the format: TABLE_NAME_TE. Each of these packages has a variety of functions and procedures that make common table operations a lot easier.
I’m working on getting the first release out today. I’ll let you know when you can take a look if you’re interested.
Regards,
Dan
Dan said
Tyler,
Ok, it’s out there…
http://sourceforge.net/projects/tapigen/
Let me know what you think!
Regards,
Dan
Robert Treat said
I don’t think is something specific to pl/sql, I think it is more systemic of 1) writing procedural code, which by nature tends to be quite tied to specific schemas, and 2) much of that code is being written by DBA’s who don’t have a culture of shared development like many app coders. This seems true regardless of databases; ie. I don’t know of any big libraries of T-SQL code, and of the few PostgreSQL libraries, none are particular well known within that community either. Actually with Postgres we probably get more re-use out of the scripting language based procedural languages like pl/perl, which can make use of CPAN modules inside the database.
Gerry Bragg said
PL/SQL is simply a niche language loosely based on ADA. For database programming it is an excelent language and you certainly can achieve good seperation of business logic (if not in mid-tier) and transctional logic. There is nothing in PL/SQL that prevents this. For projects that don’t need, have the time for managing the object-relational mapping configuration of a persistence layer such as hibernate, toplink, kodo, etc.. then a PL/SQL self-generating transational layer can be a lifesaver and scales very well. YOu can also reduce ’round trips’ between the app server and database w/o using Oracle JDBC extensions, compare new and old values using check sums before you update that column in the 50m row table that is a fk supported by a low cardinality index. ALSO underutilized is how easy it is to do ‘metaprogramming’ in PL/SQL using dynamic SQL. This works well to genericize verious routines like data validation logic. In order to maximize reuse thru ‘real time’ code generation (dynamic sql) and static code generation you must have well established conventions that are consistently followed. Anyone who has done Ruby/Rails coding and not pluralized their table names will know what I mean.
Looking for great PL/SQL « I’m just a simple DBA on a complex production system said
[…] 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 […]
Seems like a good idea… | El Mundo Con Otros Ojos said
[…] Check it out – and comment away… Tyler has a good idea/thought going on over there. […]
charlie said
isn’t oracle responsible for dropping the ball on this?
i mean, java has reusable code libraries because someone wrote one and then the governing body decided that that component would be shipped with the sdk. so, you can write your own but why do it?
microsoft governs a number of their own languages and does the same.
oracle and pl/sql? sure, there are some api libraries but not anywhere as extensive. oracle just doesn’t seem to be interested…
TAPI vs. XAPI « Jeff Kemp on Oracle said
[…] Reusable PL/SQL (Tyler Muth) […]
Gaptek said
Gaptek…
[…]Reusable PL/SQL « Tyler Muth’s Blog[…]…
furnizor mobila said
furnizor mobila…
[…]Reusable PL/SQL « Tyler Muth’s Blog[…]…
Keshia Palmitessa said
This has been quite remarkably open-handed with you to
supply unreservedly just what most people could possibly have sold for an e book in making some
bucks on their own, certainly given that you might well have tried it in the event you
desired. These thoughts additionally worked as a fantastic way to comprehend the rest have the same dream just like my personal own to know somewhat more around this condition.
I am sure there are many more fun moments up front for many who
browse through your blog post.
louiscunk.centerblog.net said
I always emailed this website post page to all my
friends, for the reason that if like to read it
after that my friends will too.
Webmetro said
I wonder if the project is still live?
ayurveda and essential oils said
Wow! This blog looks exactly like my old one!
It’s on a completely different subject but it has pretty much the same
page layout and design. Wonderful choice of colors!
triple net lease said
What’s up mates, its fantastic piece of writing on thhe opic
of tutoringand completely explained, keep iit up alll the time.
dental veneers davison mi said
Very quickly this web page will be famous among all blog people,
due to it’s fastidious posts
home said
Hey! This post could not be written any better! Reading through this post reminds me of
my previous room mate! He always kept talking about this.
I will forward this post to him. Fairly certain he will have
a good read. Many thanks for sharing!
nero 2014 free said
Attractive section of content. I just stumbled upon your web site
and in accession capital to assert that I acquire in
fact enjoyed account your blog posts. Any way I will be subscribing
to your feeds and even I achievement you access consistently quickly.
teacher bulletin board supplies said
Quality articles is the crucial to interest
the people to pay a quick visit the web page, that’s
what this website is providing.
free blogs online said
Pretty nice post. I just stumbled upon your weblog and wanted to say that I
have truly enjoyed surfing around your blog posts.
In any case I will be subscribing to your rss feed and I hope you write again soon!
technology blog said
Any one know how to get a free copyright for articles published in blogs..?
Narayana P said
pl/vision libraries..any one got current link?