utPlsql
I just received an email suggesting to look at utplsql for pl/sql unit testing. I actually downloaded utplsql a couple of days ago, but haven’t used it yet. I did see that it requires you to install some packages and tables into your database. I’m currenlty working on a large oracle applications database, not sure if the dba’s will allow me to install all of this in the database. Personally i would prefer some unit test tool which i could use without installing anything. We created some unix shell scripts which use sqlplus to run all the tests. I feel this is good enough for now.
Just to clarify why i think refactoring pl/sql is harder than refactoring java:
1) refactoring java usually doesn’t impact production data, but when refactoring sql you have to think about migrating data in production, which usually means creating a lot of migration scripts. 2) there’s no tool support for pl/sql refactoring. For java i mostly use eclipse, which makes refactoring very easy, just select a method, and use a menu option to move it to it’s own class or to a different class. Or select some java lines, and use a menu option to move it into a new method, eclipse will
determine all the parameters for you. I would really like to see some of this in pl/sql tools.
October 14th, 2003 at 4:32 pm
As one of the main contributors to utPLSQL, I urge you to try it. Can you explain how you manage to do any coding if you are unable to create any tables or packages in a development environment?
October 14th, 2003 at 8:15 pm
Easy, use an anonymous pl/sql block executed with sqlplus in a shell script. Will automate all your tests, and output a nice list of tests which succeeded and which failed. This way i do not have to install anything in the database that isn’t going to be migrated to stage and prod.
October 15th, 2003 at 10:22 am
I said how do you do any coding when you can’t create any tables or packages. So it looks like the answer is that you can, but you don’t want to.
The problem with the shell script approach is that you end up coding the same stuff over and over again. You could do unit testing in Java without using JUnit, but it saves having to write code to run tests, display results etc etc.
October 16th, 2003 at 7:45 am
I agree with you that i need to do some code over and over again, but it’s not as bad as you suggest it is. We have reusable shell scripts to deploy and unit test our code. It’s easy to write a shell script that finds all the unit test script based on some file name pattern, and then runs all of these. I find that the only code that i rewrite is the part that tests the outcome of a test and prints the result. So this is something that i cut and paste into every test script.
October 16th, 2003 at 10:31 am
I bet it isn’t possible for you to unit test a package with a single key stroke from TOAD, is it?
I am intrigued about your reluctance to install anything in development that will not make it to production. What about things like DBMS_PROFILER?
October 16th, 2003 at 11:00 am
No, but how about one keystroke on the unix shell?
I recently upgraded to TOAD 7.5 and as i noticed i couldn’t compile my packages in the procedure editor anymore, as the files contain more than only the create package statement. We need to put ‘whenever sqlerror…’ and ‘show error’ commands in the create package files, so that these files can be deployed with our change management tool (Kintana A*I). This tool basically uses sqlplus and ftp to deploy files. Now that i cannot compile my packages in toad anymore, i’ve created a shell script that deploys all the files (in exactly the same order as they will be put in the change management tool, so that will work). This deploy script will automatically run the unit tests after objects have been installed.
So my question to you: can you deploy all your objects and unit test them with a single keystroke?
October 16th, 2003 at 2:08 pm
Hi Andrej,
I am the original designer of utPLSQL. My first release DID work less with underlying tables and I can certainly see your desire for a “lighter” product that can be used with DB object dependencies. I am pretty sure this has come up on the discussion forum before. Perhaps you would like to join the open source dev team and help us craft this alternative.
I moved to DB tables because they offer more flexibility, plus persistence. Did you actually ask your DBA to do the install and she/he said no? I find it a bit hard to believe that DBAs would generally say no to tools that install tables into, usually, their own schema, affecting nothing else.
Your solution works for you, but I am not sure how easily it is extended to other developers. I have found that most developers do not have your commitment and discipline. They need something that is push-button easy (to install and use). I think that utPLSQL and Ounit (www.ounit.com) come closer to doing this than anything else I have seen.
Your comments on refactoring are very well taken. Isn’t it irritating that the tools vendors have not seen fit to give us what so many other developers have?
Finally, I will be teaching a class in de Meern for Oracle the first week of November. Have you heard about it? I will also be doing an user group presentation on the night of the 4th. I would love to meet you and talk about these issues some more!
Warm regards, Steven
November 11th, 2003 at 9:57 am
Hi Steven
We need to test utPLSQL testing tool for our application which is built up using oracle stored procedures and packages.
Can this tool be of any help to us ?
Regards
Parikshit
November 11th, 2003 at 10:22 am
Yes, you can test oracle stored procedures and packages using utplsql. You’ll have to create package that contain these tests, and then you can use utplsql to run the tests. I recommend you also have a look at Ounit. This is a nice GUI to run your tests.
November 13th, 2003 at 9:49 pm
Junit for PL/SQL
Junit for PL/SQL
November 13th, 2003 at 9:51 pm
Refactoring PL/SQL
utPLsql
February 26th, 2004 at 5:22 pm
I do not agree with Andrej on his statements ” pl/sql is harder than refactoring java”
sql: we refactor our sql alot with the sole impact of having better performance. this sql may be embedded in jdbc call’s or in pl/sql … does’nt really matter.
java: where as when we start refactoring Compoment Managed Beans (J2EE) , we really have to start thinking about migrating data and backwards compatability.
so what you really mean, is that, when you start refactoring your ddl, you may have migration issues: this is certainly not programming language inherent.
I agree with Steven completely, when he says : ” Isn’t it irritating that the tools vendors have not seen fit to give us what so many other developers have?”
December 10th, 2005 at 7:12 am
In testing the procedure it is require to configure the utl_file so i want to know how to do that and how to install utl_file in windows or dos enviornment.