Here is the program!

You will find here all the news about the PL/SQL frameworks and libraries that you can find on internet. We will also discuss about the tools needed in each development phase (development, production, maintenance), the error management and so on.
And then, whether frameworks and libraries are commercial or open source, I will try to test them all.
I will compare their features to the needs that I know and that you expressed.
Oracle guys, you are welcome on that blog!
We also have a lot to learn from you for a better use of Oracle features.

Sunday, September 20, 2009

Let us use the right tool at the right moment

Three months ago, we had a very interesting discussion on the LinkedIn Oracle Pro group (see the (1) note below) about the tools used to log, debug and instrument the PL/SQL code.
I initiate that discussion willing to improve the features of the PL/SQL library I develop based on the points of view of the other developers.
We had very different opinions on all the phases of the process, from the tactic and the tools used to debug during the development, to the ones meant to catch the errors in production and finally, the ones destined to follow the performances of the programs.

Here it is a synthesis of the different exchanges and opinions:
  • First, we have to identify the different phases in a standard project ;
  • Then, we will able to discuss about the needs of each phase ;
  • Finally, the choice of the tools that will fit the needs will follow logically.
I was also inspired by a recent article of Steve Feuerstein (see the (2) note below).
Maybe sometimes we do not agree with all he is saying, but we must admit that he is a reference in the PL/SQL's world.
On my part, I am sure to have a lot to learn from him.

Of course, each of your projects is different and each of you as a programer or a team you have your own habits and the knowledge of a particular tool. Definitely, the article will not reflect necessarily your choices.
Anyway, most of the time, there are several ways to solve a problem. I hope that this could be an opportunity to make an assessment of our practices and find ideas to improve them.

Classically, what are the development phases ?
We can identify the following phases in the life cycle of a program :
  • Development ;
  • Tests (yes, it is always better to test the developments, even if it is a long and complex process ;-) ) ;
  • Pre-production (it is really more secure when you have it);
  • Production ;
  • Maintenance.
The Development phase
As you are just supposed to write the code, there is no particular need for logging in that phase.
But you will test it...Maybe not in the same way than in the Test phase, but you will want to be sure that the program starts when you press [Enter].
If for no other reasons, at least in order to keep your good reputation.

At this point, we have to answer the question : what is the limit between being sure that the program runs and testing it ?
I would answer that at this step, we have to assume the complete unit tests of the procedures. Maybe we can be more focused on testing all the cases rather than on values (if you disagree, you can comment below ;-) ).

If you just want to know where the program goes, DBMS_OUTPUT.PUT_LINE is enough. But if the program is complex, maybe you will be bored to put your DBMS_OUTPUT.PUT_LINE statements and to delete or to comment them at the end of development.
So you can use a debugger and some breakpoints to verify the program. It is easier to do and you do not touch to the code at the end, which is good.

On another side, if you want to log the activity of your program and the errors in production, you must implement the mechanism in this phase.
It means that the logging tool is already developed and reliable.
It also means that this tool can be another way to verify that your program is working, but this depends on the features of your logging library.

The second phase of development runs after the Tests phase, when you have to find why the results of the tests are not as expected. And the IDE step by step features as well as logging libraries or the famous DBMS_OUTPUT.PUT_LINE can be good, of course, depending on the code complexity.

The Tests phase
Testing a program is a full time job. There are numerous solutions, from manual testing (possible for short and not too complex code) to automated testing with specialized frameworks.
According to Steve Feuerstein, the most recommendable are utPLSQL, PLUTO, PL/Unit, DbFit, and Quest Code Tester for Oracle (of course).
For that job, the logging libraries are only interesting if you are doing manual testing. For automatic testing, it must be already prepared with special features.
At this point, it seems difficult to choose a tool as it depends on your code and the type of test you want to do.

The Pre-production phase
In the pre-production phase, you always have access to the code and the tables. Nevertheless, depending on your organization, maybe your DBA does not allow you to modify anything on the server or may be you can only modify the parameters.
If you are developing a program that you can start when you want, probably you can use debuggers, and even DBMS_OUTPUT.PUT_LINE.
If your process is scheduled or dependent of a particular context, it will be trickier. In that case, if you do not want to sleep next to the server, you will need to register the results or the key points of the process. Writing records in files (if your DBA agrees and let you write on his server) or tables, with or without logging library, can be a good solution.

The Production phase
You will probably have three needs in this phase.
  • First, the performances. You can use TKPROF, which seems very powerful but difficult to use. That is why, maybe, I have not met a lot of experts of TKPROF at this moment. You also have the Oracle logs, but it is hard to understand. And anyway, you will need your DBA to obtain them but today he is very busy (as yesterday and probably tomorrow). And you will not have the information for the exact portion of the code that you want to examine.
  • Then, the technical error management. Here, there is no miracle, if you have set nothing in your exception section, you may have some surprises. Not only that the process can stop, but you will also loose the error context. I mean, you can try to examine the Oracle logs, but the variables, records or field that were in that context can be hard to find.
  • And finally, the functional errors. The bad news is that if you do not set nothing in your code, the the process continues and the Oracle logs contain nothing particular.
    And the worse thing is that the results of the error may stay hidden for several days or weeks, for you and your customer, so you will have no idea that errors are encountered in the process.

In all these cases, you will need a logging tool connected with an error management.

The Maintenance phase
If you stay some months or years in a company, you will probably have to maintain your own code.
It will be your punishment if you wrote poor code, or a pleasure if you wrote a good one.

Saying that in writing good code, 90% of the effort comes from your brain is perfectly true.
No tool will help you if your code is written against the language and the good practices.

But when you work for IT services company and you have another customer each 3 or 6 months (like me), most of the time you do not have to maintain your code but the code of other people who preceded you at your position. And sometimes it is not funny, especially when the code is unclear and you have few documents on conception and/or technical realization.
And do not tell me that in your company all your documentation is always updated, I would be hurt to think that I am not lucky in my missions ;-).
So you need good tools or a lot of time (sometimes both) because brain power is not enough in that case.
The full range of the available tools can be employed, from debuggers to DBMS_OUTPUT.PUT_LINE statements, and including logging libraries to recover an understanding of the code.

In almost every phase, you have several solutions to do your job. But as you can see, the only one which can be used or helpful each time is a logging tool linked to an error management.
That is why I developed one, under LGPL open source license, The Log & DBug library (
The PL/SQL Libraries and Frameworks cannot be an universal solution because the needs are different in all companies and organizations, and the practices of the developers are also different.
I know several companies where installing any code or table outside the core business is forbidden. This means that libraries or frameworks cannot be used.
So we will also need the others tools like SQL Developer, TOAD, or DBMS_OUTPUT.PUT_LINE statements.
I just think that with libraries, you can win time and reliability in your code. Also, we have to recognize that it is more efficient and funny to be concentrate on your core business rather than on reinventing the wheel.

These conclusions are just an approach on development needs and tools, not the truth.
Your opinions and arguments are more than welcomed!

I have started a discussion on the forum of The Log & DBug Library project on
that you can join at :

  1. the discussion link is

  2. the article link is

No comments:

Post a Comment