#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 – Only one active protocol

Sometimes you have the situation that you store several different variants of an entity in the database, but you have to be absolutely sure there is only one of these variants active at a time.

Examples could be different configurations or color schemes you can choose from, the active financial year in accounting-related scenarios or a default entry.

There’s a similar thing in the deathstar, which runs on one of several protocols.

IDLABELALERT_LEVELDEFENSE_MODEPOWER_LEVEL
1Everything easyLOWBE_KIND80
2Be carefulMEDIUMBE_SUSPICIOUS90
3OMG the rebels!VERY HIGHSHOOT_FIRST_ASK_LATER120

To make sure, there is only ever one protocol active, the database developers use simple tools most relational databases provide:

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

#100CodeExamples – utPLSQL Cursor Comparison with user-defined Types

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:

Continue reading

#100CodeExamples – PL/SQL Objects: Basics of force-sensitive beings

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:

Continue reading

#100CodeExamples – PL/SQL BOOLEAN to INT

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:

Continue reading

#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