Most developers have – at some point – to deal with code they don’t know. Sometimes they don’t even have a clue what something is doing, but are expected to understand and probably change that something quickly. The situation gets worse if the functionality contains some side-effects or non obvious functionality, triggers for example in the database sphere.
I often encourage people to use unit tests to explore an unknown scenario or functionality, and utPLSQL provides a great tool to do that (since version 3.1.6): The
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:
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).
We already looked at utPLSQL’s cursor comparison with user-defined types. If we have to deal with updatable views, we can use an even simpler approach and create nested table collections from %ROWTYPEs.
The scenario is similar to the previous one: we have a number of planets with or without garrisons. Now we want to be able to update the view with a garrison-ID which then automatically creates a new garrison-entry.
We often deal with the same things in several places of our codebase, be it names, IDs, codes or something similiar. Problems arise when we write them slightly different, which we often don’t notice because it won’t create a compiler error.
Additionally, these things should often not be changeable once set, therefore most programming languages provide CONSTANTS.
PL/SQL does so, too, but to get these constants into SQL can be a bit tricky.
In a previous example we learned about the basic usage of objects in PL/SQL with which we can start object-oriented development in an oracle database.
A more common usage for objects, however, is to use them as a base type for nested table collections (table of objects). That way we can make objects accessible from SQL, which is a very powerful possibility to return table-structures from PL/SQL functions.
The annoyances often start when we want to change an object which is already referenced in a table type: we get ORA-02303 dependency errors. The “force”, as usual, can help here:
I had the pleasure to present utPLSQL to the local DOAG usergroup in Karlsruhe last thursday and one of the questions after the presentation was how we can control the expected results of a cursor-comparison.
We already had an example using SELECT FROM DUAL and UNION ALL, but sometimes our expected outcomes are more complex.
In such situations, we can use user-defined types and the power of Nested Table-collections:
I dived into object-oriented programming in PL/SQL a while ago due to utPLSQL and also used it several times for my normal work meanwhile.
This powerful feature of the PL/SQL language is highly underused and therefore I want to create some examples around it in hopes to show some of the advantages objects in PL/SQL have to offer.
Let’s assume we want to implement some behavior for force-sensitive beings, whose most prominent representatives are, of course, Jedi and Sith: