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:
One of the main annoyances of Oracle databases for me has been its lack of a BOOLEAN SQL-Type.
Yes, BOOLEAN exists in Oracle, but only inside of PL/SQL, not in the SQL context, which means that you can’t select a boolean type and also can’t retreive it via JDBC.
The simplest way to overcome this is to have an easy way of converting BOOLEAN to 1/0 INTEGER – and there are several possibilities to do this:
If you want to add dynamic values in a string with PL/SQL, you can concatenate it like this:
l_alertMessage := 'A bunch of "' || l_attacker ||
'" is attacking with an estimated fleet of ' ||
to_char(l_numOfShips) || ' ships';
But this is both, hard to read and tedious to write, because we have to cast everything which is not a varchar2/nvarchar2 explicitly with TO_CHAR.
There are, however, several nicer ways to achieve that goal. One way is the usage of UTL_LMS.
When I started to blog, I set myself the goal to publish one article per month. Maybe it was too ambitious, maybe I’ll have to learn to write shorter blog posts (which might not be possible because the reason I blog is to get a deeper understanding of a topic and reflect on it – something hardly doable with just scratching the surface).
Fact is I wasn’t able to publish for nearly 3 months and while I have some topics rumbling in my head I won’t just throw some unbaked thoughts on a blank page to satisfy a self-made deadline. Or grow a bigger audience.
This kind of little, personal posts is the only thing I’m willing to try (thanks Nate for your encouragement).
At the moment it feels a bit like writing a diary, but I will take it as an experiment.
The thing which kept me from writing for several weeks now is an amazing open source project named utPLSQL. It’s a unit-testing framework for PL/SQL and I started using it about a year ago when I needed a tool to help me getting a very much “grown” legacy project into a stable state.
After finding a bug around windows line breaks (yes, you can develop Oracle databases on Windows) and contributing a fix I had the pleasure to get in contact with the great guys working on the project. They were incredibly nice and helpful and so it was very natural to stay in contact.
Long story short – I finally ended up being the main contributor for the java-api and command-line-interface. It definitely helped that I could build upon a very good foundation and I learned an insane amount of new things which not only helped me in my job but also widened my horizon as developer.
Yesterday we successfully released version 3.1.0 of java-api and cli. I couldn’t include everything I wanted, but we pushed the project further and it was both fun and satisfying. There are some very positive effects of contributing to open source software I experienced, but that’s stuff for a different post.
To finally give this article at least a bit of technical value I will provide some short examples of the improvements of the new command-line-interface.