After I published my last post, some folks rightly pointed out that the way I implemented the
add_popular_sith procedure was not well-testable and that I should rather change my code than trying to control the underlying sequence.
And I totally agree. For several reasons:
- Controlling the sequence adds a lot of complexity to my testing code
- It invalidates depending packages (and yes, there are ways around that problem)
- It also forces me to touch internals. That means when I change the sequence (even if it’s the sequence name) my test will fail even though the procedure still works. Same happens if I replace the sequence with an identity column.
The whole test-setup around controlling the sequence is a “smell” that shows me one thing: I have functionality that is hard to test (and therefore probably also hard to change and maintain).
A nasty problem has been haunting me for a while now in my codebase: Every time I run my full utPLSQL test suite, one specific test-package fails every test with
ORA-04061 - Existing state of Package has been invalidated. But when I run this specific test package, everything works fine.
Today I followed the rabbit hole and to get a better understanding of what’s going on I tried to extract what I learned into a simple example in my favourite universe.
I ran into a requirement today where I had to combine a number of strings (varchars) into a delimited list. Some strings would be NULL and should be ignored.
I played around and found a bunch of different approaches which I’d like to share.
Let’s assume we have a package
info_util with the function
person which will combine a number of information about a person to a delimited list (to simplify the example all information is passed by parameter):
i_name => 'Luke Skywalker',
i_alignment => 'light',
i_comment => 'Most powerful jedi of all times'));
-- Output: Luke Skywalker, light, Most powerful jedi of all times
i_name => 'Vader',
i_title => 'Darth',
i_alignment => 'dark',
i_comment => 'Pretty evil'));
-- Output: Vader, Darth, dark, Pretty evil
Earlier this week, I introduced a way to extract a chunk of functionality from a view into an Object Type, using the object-oriented capabilities of the Oracle database.
Today I want to go one step further and make the Object Type not part of the view logic, but part of the underlying table. The goal is to get rid of the still rather complicated view and replace the
STRUCTURE column with a
I had two examples about PL/SQL object types in the past, but didn’t manage to showcase their practical use yet: For modern SQL is turing complete you can somehow solve every problem with SQL and it is even easier with the capabilities of procedural PL/SQL.
There are, however, situations where the usage of object types (SQL Types) can help you greatly and for they are available in Oracle database since version 8i (1999), they should be part of every database developer’s toolkit.
We just got the following, structured list of force powers, categorized by their main nature (Universal, Light and Dark) and skill dependencies:
I recently played around a bit with the various utPLSQL annotations and found them pretty powerful.
I’m pretty sure I will write some more about how you can use annotations like
--%context to make your tests more expressive, but today I just want to showcase some techniques and tell the story of the original Star Wars movies.
For the interesting part is completely in the package specification I will skip the body in the highlighted code here. Each procedure implementation only contains a call to
dbms_output.put_line with some text.
You can get the whole example as always from my github.
When I did my presentation at APEX Connect, Erik van Roon explained another pitfall of my MINUS approach to compare the content of two views to me – something some people already tried to show me and failed (well, I failed getting the point).
Let’s start with the same situation as in the last example, a small list of Star Wars characters and the movie episodes in which they appear:
|1||Darth Vader||3, 4, 5, 6|
|2||Luke Skywalker||4, 5, 6, 7, 8|
Now let’s assume we are forced to do overwork by the sithlord in charge, it’s 3 am, we are terribly tired and create a new view but miss the
group by statement:
One of the really great features of utPLSQL are contexts. They allow to further organize tests inside a test suite (which can also be organized hierarchically by suitepaths).
Contexts can help with several things:
- They can reduce the setup/teardown time (things can be done once per context instead of before every test)
- They can help to reveal the intention by grouping several tests
- They can be used to avoid duplication
For this example we build upon the active deathstar protocol which in our case controls how the security system reacts to an unknown, hooded person (you all know that hooded strangers on a deathstar are always undercover jedi).
I am working professionally with databases for over 15 years now and have a huge focus on Oracle – but I really keep forgetting how to update a table with values of a different one (this is one thing which is so much easier in SQL Server by the way).
Therefore let’s assume we have a table containing planets and one containing garrisons which are on these planets.
|Garrison ID||Planet Name||Planet Faction|
We would now like to have a new column in the garrisons table which can contain a name.
alter table garrisons add name varchar2(300)
The imperial side now has a request to update all their garrisons with a name according to this schema: <PlanetName> (<Garrison ID>)
My work includes a lot of database views because they work as a kind of public API in our case. Since I started talking about self-testing and utPLSQL, the MINUS-comparison has always had its place when assuring that a new, reconstructed view has the exact same content as the old one.
However, I did it wrong until recently when I discovered a major pitfall.
For I like to tell stories let’s start with a Star Wars scenario again: We have a table which contains all the Star Wars characters and another table which holds information in which of the “Episode”-movies they appeared.