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.
Continue readingI 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:
Continue readingI ran into a strange error yesterday: when I ran my test-suite from IDE I suddenly got a number of failed tests, but they were completely okay when I ran it from utPLSQL-cli.
I panicked a bit because that’s one of the worst possible situations you can imagine for a testing framework.
After some investigation I found the problem.
Look at the following example-test suite which assures that the current date and time is set for “arrival” when a new entry is inserted into the “starport_flights”-table:
utPLSQL gives you the possibility to organize test-suites in a hierarchical way. We can use this for collecting setup-/cleanup-methods into separate parent-packages.
We can not only outsource our setup-methods into a single place (following DRY principle), but we can also make clear that several test-suites are somehow connected.
To be sure the Lookup for Primary/Secondary Power-Nodes of our Deathstar works correctly and keeps working in future changes of our application, we want to have some basic unit-tests, using the cursor-comparison capabilities of utPLSQL.
Continue reading
The DOAG exhibition and conference in Nuremberg, 2018 probably the biggest Oracle conference in Europe with over 2000 visitors, is over. And I had the pleasure and privilege to be part of it.
As a first-time attendee and first-time speaker, the conference was both, very exciting and immensely frightening to me. The great location alone with its 4 floors arranged around a huge hall made me question whether I belonged there right when entering. Nonetheless, I proudly got my speaker shirt and the following three days turned out to be one of the greatest highlights 2018 for me.
19 Sessions in parallel every hour made me really spoilt for choice and in the end, I even skipped several slots because of chats with the amazing people I met – and pure exhaustion.
Some of my personal highlights in no particular order:
Organization, Food, Location, Transport – all of these things were exceptionally well done and made the conference a great and comfortable experience! Excellent job, DOAG e.V.!
The talk before mine finished a bit early so I had even more time to prepare, put on my sith robe, setup my notebook and – OH WHAT THE F**K THIS HDMI CABLE DOESN’T FIT INTO MY NOTEBOOK!! WHAT DO I DO NOW? I NEED THIS CAREFULLY PREPARED DEVICE WITH LIVE DEMO AND…
Luckily it was just a display port adapter still attached to the HDMI cable (sorry to the volunteers who had to deal with me while panicking). Maybe you get my nervousness.
I was very glad some people I already knew from the day before showed up pretty soon and I had someone to talk to – until the doors got closed about 3 minutes before the scheduled start.
The topic struck a nerve, the room was completely full (as was the other talk about utPLSQL by Philipp Salvisberg the next day) and people had to be sent away.
I was very glad I had memorized my talk, so I didn’t need much brain capacity to search for words. And after some minutes it felt much more natural to me (my hands were still shaking pretty much when scrolling through the live demos, but people I spoke with afterward said it was not visible).
I got lots of really great feedback and was even asked if I wanted to do the talk at another conference. Which I might do, because after the first five minutes I really started to enjoy presenting what I had prepared for months.
For me, a blog article doesn’t feel right if there is nothing actionable I have to share so I will try to point out some things which were immensely helpful for me – maybe they’ll help or encourage other people who want to do a public presentation.
If you aren’t interested in that, just scroll down to the last and by far the most important part of this blog-post.
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.
Software-Testing has always been a very important topic in professional development because software – unlike for example buildings – changes a lot from the first line of code to release and from there during its whole lifetime. Due to that constant change and its complexity every honest developer will have to accept someday, that there is no software without bugs.
The rise of agile methods, ideas like continuous integration and the urge to have more frequent releases led to an even stronger focus on modern testing-strategies (interesting blog post about correlation of release frequency and agile practices – especially including automated testing).
Unfortunately there is some kind of “holy war” about some programming techniques around testing (I will write a bit more on that in the last chapter of this blog post), but nonetheless the need for automated self-testing (be it system-, integration- or unit-testing) in todays software development is real and uncontroversial among the experts. Therefore I will use the general term of “self-testing” in this article, concentrating on the very basic idea and trying to give an introduction on how it can be done in database projects. In my impression still many of today’s database projects don’t even know about the basics of self-testing, so let me try to show you the benefits without nit-picking about specific techniques.
Clean Database Development 