"But that worked yesterday!" or, why you should test with utPLSQL

This article has been published in German in the Red Stack Magazine and in English in the PTK Magazine.

The lament in the headline might be very familiar to most developers and even more painfully to quite a few users. “What happened between yesterday and today to cause my application to break? And why did it have to be reported by my user?”

After some contemplation, we remember a small change we made yesterday, shortly before leaving for home. The customer asked for a new column in the View v_starwars_ characters which we added on the fly. Nothing special, but very urgent, and it seemed so simple: just a new field with information about the movies in which the Star Wars characters appear:

Continue reading

Charsets are a nightmare – Console, Java and Oracle Database

This week, there was this issue about utPLSQL-cli printing russian symbols used in tests as ?, no matter what NLS_LANG or LC_ALL environment variables are set.

I’ll be honest: there are things I really enjoy about programming and open source work – charset issues are definitely not on that list.

Because I know I’ll need this some day in future, I thought I could write it down while it’s still fresh – and maybe it might be helpful for someone else, too. I’m totally not an expert on this topic and some of my conclusions might even be wrong – if so, don’t hesitate to reach out, which will give me a chance to learn something!

Continue reading

Let Tests Tell a Story With utPLSQL Nested Contexts

There’s a great talk by Kevlin Henney about Structure and Interpretation of Test Cases that inspired me to rethink my approach of writing tests.

With the features added in the latest version 3.1.9 of utPLSQL, this approach is even easier to pull off as I want to showcase:

We start with the friend or foe detection system of the deathstar, which decides based upon the weapon and clothing of indiviudals whether they are friend or foe to the empire (you know: red lightsabers mean sith, which are friends, blue or green lightsabers mean jedi, which are foes etc.)

The full example is available on my github repo as always, I’ll skip some parts here.

Continue reading

DOAG 2019 – A whole different experience

This year’s DOAG excibition + conference is over, and it was a blast in many ways.

I can’t say much about the overall quality, topics or audience because I’m lacking comparison data (I’m still very much a conference newbie), but from my point of view it was an extremely well organised and executed conference.

Therefore, I will also focus on my very personal experience and share some thoughts.

Continue reading

DOAG2019 – Talks about automated Self-Testing

This year’s DOAG conference and exhibition really embraces the topic of automated Self-Testing from a developer’s perspective – and I am so glad about that! Too long practices like TDD have been a niche topic in the database world, but times are changing.

To help people interested in automated self-testing practices, I compiled a list of all talks centered around that topic in order of their appearance (talks by utPLSQL maintainers get a little highlight, though. Yes, I’m biased and it’s my list).

The list is highly subjective, there are several presenters I don’t know and while I read through a very large amount of abstracts it is absolutely possible I missed something.

If so, don’t hesitate to reach out to me so I can add the talk to the list or correct the very short description.

(Make sure to revisit this post once in a while for it is a “living” document)

Continue reading

OGB Appreciation Day: Explore a utPLSQL test scenario with “force-manual-rollback”

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 a_force_manual_rollback-Parameter.

Continue reading

Embracing a mentality of prediction – evaluation – exploration

A while ago I started to play a little game when doing test-driven development:

Every time I run my microtest, I predict the outcome. Will the test fail or pass?
When my prediction was right, I continue. When it was not right, I investigate and explore why my code behaved differently than I thought it would be. I will not simply go on and poke around (though I do that frequently, too) but really try to understand why my expectation of the code’s behavior was different to reality.

When I found the cause for the wrong prediction, I try to learn from reality and grow my knowledge about the code. Sometimes I not only find gaps in my knowledge but also flaws in my work process or in the way I do things.

I found this game to be a very powerful tool of learning. I’m still not very seasoned in doing it, but it’s something I want to practice more often.

Today I realized that I can not only use this technique for TDD, but for many different things in software development and also life. We are constantly predicting what will happen, what consequences our actions will have:

Will my code work after merging that branch?
What will change when I change this setting?
Will I catch the bus when I go now?
Will I need an umbrella today?
How will my co-worker react to the feedback I give him?

We constantly predict, but I usually don’t evaluate the outcome in reality and explore the reasons when my prediction was wrong. In not doing the evaluation and exploration, I probably miss a powerful tool to consciously learn and instead stick with a very unconscious and probably tedious trial-and-error approach.

That’s something I want to change: What will happen when I start to notice the predictions I’m doing all the time, when I consciously evaluate their outcome and explore why my prediction was wrong?

My prediction is that I will not only learn faster and become more aware of myself and the things around me, but might also find flaws in my thinking, in the way I approach things and in beliefs I hold.

Sounds like quite some effort and also a bit scary, but I want to try it out.

I would love to hear your thoughts on that!

Concatenate a List of Strings in PL/SQL

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):

dbms_output.put_line(info_util.person(
  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

dbms_output.put_line(info_util.person(
  i_name => 'Vader',
  i_title => 'Darth',
  i_alignment => 'dark',
  i_comment => 'Pretty evil'));
-- Output: Vader, Darth, dark, Pretty evil
Continue reading

PL/SQL Object Types: putting the Structure into the Force Powers Table

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 VARCHAR2 STRUCTURE column with a t_numeric_structure object.

Continue reading

Structure Force Powers with PL/SQL Object Types

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:

Continue reading