“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

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

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

♠ Oracle ACE – what it means to me

Last week, I received the official feedback that I am now accepted to the Oracle ACE program, listed in the directory of groundbreakers and allowed to use this logo:

Oracle ACE Logo

Im am very honored and grateful to receive this award and be a member of a group of over 450 Oracle experts around the world who love to share their knowledge.

For me, it’s a kind of “pat on the back” and a confirmation that what I write and talk about is seen as helpful for others – which is a huge motivation for me and has always be a main goal.

When I started to blog nearly two years ago, it was first of all for myself, because I enjoy writing about things that intrigue or bother me, but I wanted to do it in a way that probably could be helpful for others.

This very same motivation is still behind every post I write, it’s the reason I started my #100CodeExamples-Challenge (I’m still motivated to fulfil it, just not sure how long it’ll take) and it’s also the reason I started to present about stuff I care for.
I genuinely enjoy public talking (although it’s been very scary, too), but I want to do it in a way that is helpful and probably even entertaining for others.

Being an Oracle ACE now is an affirmation for me that I’m on the right track and that some people think I’m doing it well. That feels good and I appreciate and enjoy it, especially because everything I do in that matter is 100% voluntary.

Continue reading

APEX Connect 19 – Great event, great people, great experience

The APEX Connect 19 conference in Bonn is over for a week now – and I still feel a bit of what I call “After-Conference-Blues”: When you’ve been on high adrenaline for three days, one highlight chasing the other, meeting dozens of people and constantly out of your comfort zone, returning back to your normal day job feels somehow numb and gray.

It has been a great event, I perceived it as very well organized although I might not be the best person to judge for it was only my 2nd Oracle-related conference (and the 2nd for several years now).
The only thing I would really wish to see improvements are the badges (bigger, especially with bigger names so you don’t have to stare intensely at other people when you didn’t understand or forgot their name).

It was also a different experience for me: While I tried to attend a talk in every slot at DOAG last year, I intentionally skipped several slots to meet people, have a chat, give myself some breathing room and reduce the “being on a schedule”-feeling.
Everything has its ups and downs and while I didn’t get as much informational input, I met a lot of awesome people and therefore this review will be more about the people than the talks.

Continue reading

Updating a Table with the Values of a Different One

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 IDPlanet NamePlanet Faction
1Korribanimperium
2Korribanimperium
3Dromund Kaasimperium
4Hothrepublic

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

Continue reading

#100CodeExamples – Test updatable views with utPLSQL Cursors

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 reading

#100CodeExamples – Constants in PL/SQL and SQL

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.

Continue reading

#100CodeExamples – Use the force to replace objects

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:

Continue reading