Using utPLSQL with multiple schemas
I really like unit testing, and nowadays there is an helping framework for almost every environment and/or programming language. For PL/SQL this is utPLSQL, which consists of a bunch of packages that you need to put on your database and then you should be good to test your could. You should.
I just spent some hours debugging my test code, so I want to share this with you, maybe I can save someone out there some time.
- Schema A: utPLSQL package
- Schema B: package XY (code to test), package UT_XY (test code)
I have two schemas A and B. In schema A utPLSQL is installed, and in B is the code to test. It is recommended to have your code to test and your test code in the same schema.
The following script is used to run the tests:
begin for c in (select object_name,owner from all_objects where object_name like 'UT\_%' escape '\' and object_type ='PACKAGE') loop begin utPLSQL.test( package_in => substr(c.object_name,4), prefix_in => 'UT_', owner_in => c.owner ); exception -- in case of a faulty unittest continue with the others. when others then null; end; end loop; end; /
But I kept getting the error:
Program named "XY" does not exist.
So I thought that somehow the schema A does not “see” the packages in the schema B and created public synonyms. Unfortunately this didn’t help. Then I tried to move the “UT_” package to schema A, which worked, but now I got an error that the code to test and the test code are in different schemas.
There must be something obvious I’m missing!
And there it was: A had not “execute” grant on the packages in B.
grant execute on b.xy to A;
Now everything just worked fine.
blog comments powered by Disqus