#100CodeExamples – SYSDATE vs. CURRENT_DATE

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

Continue reading

#100CodeExamples – Dynamic values in a String: UTL_LMS

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.

Continue reading

#100CodeExamples – A challenge to learn and teach: Primary/Secondary Lookup

A while ago, Gregory Brown brought up the idea to do a “100 Code Examples” challenge:

I liked the idea very much due to several reasons:

  • The challenge will probably help me to track/be aware of what I learned
  • Breaking down what I learned into small samples will reinforce the knowledge
  • It will hopefully also exercise my skills to teach and share knowledge

I won’t put myself under pressure because that’s not the point of the challenge, but if I can manage I will post 1-2 examples on my blog here so the 100 Code Examples should be done in roughly a year.

The topics of the examples will most likely be related to my current work, so mainly SQL, PL/SQL and I will also try to add several examples around utPLSQL.
Every example will come with a very brief explanation of the goal and the critical snippet – hopefully annotated in a way that it’s understandable (I’d really appreciate your feedback on the latter part!).
If it’s possible I’ll also share a link to LiveSQL or a different environment where the example can be run immediately (won’t work with utPLSQL examples so easily).

Example: Primary/Secondary Lookup

We often have Primary/Secondary situations and sometimes we need an easy way to get all related entries based of a single entry’s ID.

In my Star Wars example, the Deathstar operates on several power nodes, some of them have secondary nodes in case the primary power node is no longer working.
We want to create a view one can query with the ID of any power node, no matter whether a primary or secondary, and get the full list of related power nodes as result.

Continue reading