Introduction to the test methodology for PL/SQL libraries

Presentation of the articles series

Discussing about a methodology to test PL/SQL frameworks and libraries seems too long and technical to be done in one article. So I decided to split the article in seven parts (one by category to evaluate) to give you something more digest and let you give your feedback on a limited number of subjects. You will have a weekly “pill to take” and talk about the effects.

We start the series with this article.

Introduction

Testing PL/SQL frameworks and libraries demands a lot of work and a method is necessary to evaluate multiple tools with different features and different approach of the development.

As developers, we all have different sensibilities and each people has to find his own comfortable path to develop. With these tests, I just want to highlight the strengths and weaknesses of the tools to let you choose which one better fits your needs with a maximum of information.

That is why I publish the methodology and ask you to help me to build the most "objective" one.

Information on frameworks and libraries

In this article I define a library as a collection of functions and procedures on a specific topic and a framework like a collection of libraries. And of course, one of the greatest interests of a framework is the compatibility between its libraries.

I will use here the word of library to indicate a library or a framework.

To have an overview of each library, I will provide the following information on each one :

• Type (framework or library)

• Type of release (alpha, beta, production...)

• Version of the release

• Start date of the project

• Date of the last version

• Translations available

• Number of developers

• Web site

when they are known.

General principles of the notation

As it is difficult to compare numerous libraries that does have not the same features and documentation, I will note all the features or documentation present in each one.

The features and documentation will be group in categories.

The note for each feature or documentation will be summed to give a note to their category.

If a feature or documentation only exists in a library, only this one will be noted. Others libraries will have 0 for that feature or documentation.

It means that the notes of the categories are not limited, it is an open notation.

Moreover some extra points can be given in some conditions (if no DBA action required for example).

Anyway, a notation reference is detailed before the each evaluation of a category.

The main categories to evaluate

I have identified seven categories than should be evaluated :

1. Install/uninstall and upgrade

2. Ownership and support

3. Development features

4. Documentation

5. Interoperability

6. Code quality & architecture

7. Performances

Install/Uninstall and upgrade

Notation

Prerequisites :

• 0 point if several prerequisites

• 1 point if one prerequisite

• 2 points if no prerequisite

Scripts :

• 0 if it does not work (>15 min to use it) or it is not present or if the results are not reproducible

• 1 point for >10 min to use it

• 2 points for >5 min and <10>

• 3 points for <5>

Bonus :

• 1 more point if no DBA action is required.

• 1 more point if the number of objects created by library is less or equal to 10

Features to evaluate

• Installation or upgrade :

• Install script : from scratch

• Upgrade script : from a previous version of the library

• Example : a script or package is given as example to verify immediately that the library works correctly.

• Prerequisites : dedicated schema, administrator rights, others.

• Number of objects installed : Packages, tables, views, sequence...

• Uninstall :

  • Uninstall script : from the last version of the library

  • Cleaning of the installed objects.

This is the end of the first article of our serial. I am waiting for your feedback, ideas and contributions. It is up to you now !

And see you next week for the second article...

Let us use the right tool at the right moment

Introduction

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.

Conclusion

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 (http://dbuglibraryora.sourceforge.net).

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!

Discussion

I have started a discussion on the forum of The Log & DBug Library project on Sourceforge.net

that you can join at : http://sourceforge.net/projects/dbuglibraryora/forums/forum/961099

Notes

1. the discussion link is http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&gid=60191&discussionID=4623837&sik=1253278911278&trk=ug_qa_q&goback=.ana_60191_1253273449342_3_2.ana_60191_1253278911278_3_1

2. the article link is http://www.oracle.com/technology/oramag/oracle/09-jul/o49plsql.html?goback=.ana_60191_1247036341672_3_1.nvr_60191_1247036355474_1

Why do we need PL/SQL frameworks and libraries ?

As a PL/SQL developer in various IT services companies, I felt many times the need of more libraries to help me ; I had a few time (the project was late, what a surprise !), not a lot of tools to help me (most of the time older versions of Toad without any options) and all to invent because the organization had no written standard for PL/SQL.
But it was difficult to find some libraries that you can use in 5 minutes and add the features you need : basically, logging and error handling. And you also need to be sure it's working well. You have enough with your bugs, you don't need the bugs from other people...
And after logging, like a lot of people, the file management : UTL_FILE is good but anyone has a procedure to write Excel files or others formats ?
And so on.

I think that a lot of PL/SQL developers love that language but need more support to have the benefits of reliable code that save their time and make development more pleasant.

Probably sometimes we don't have all the knowing of the Oracle's features and the standard features are not the same in the different versions or Oracle ; but in fact, most of the time the features available in the standard packages does not meet all the needs or are not convenient.
And that's why we need frameworks and libraries.
It is not an universal answer to the PL/SQL developers needs but it is a good help.
That's why I decided to compile all the code I wrote during several year in The Log & DBug library.

But to go further, I think it's interesting to examine all the libraries and frameworks available on the net to see what part of each can be usable for us. I will do these tests it in the next months and share the results with you.
And on my part, I will do my best to integrated your feedback and needs to assure the evolution of my library.

In my next post, I will use the result of a very interesting discussion we have on a the Oracle Pro group on LinkedIn to try to distinguish what are the tools needed according to the development phase the context and the organization.