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

More Pitfalls of the MINUS comparison

When I did my presentation at APEX Connect, Erik van Roon explained another pitfall of my MINUS approach to compare the content of two views to me – something some people already tried to show me and failed (well, I failed getting the point).

Let’s start with the same situation as in the last example, a small list of Star Wars characters and the movie episodes in which they appear:

IDCharacterEpisodes
1Darth Vader3, 4, 5, 6
2Luke Skywalker4, 5, 6, 7, 8
3Rey7, 8

Now let’s assume we are forced to do overwork by the sithlord in charge, it’s 3 am, we are terribly tired and create a new view but miss the group by statement:

Continue reading

Testing the Deathstar security with utPLSQL contexts

One of the really great features of utPLSQL are contexts. They allow to further organize tests inside a test suite (which can also be organized hierarchically by suitepaths).

Contexts can help with several things:

  • They can reduce the setup/teardown time (things can be done once per context instead of before every test)
  • They can help to reveal the intention by grouping several tests
  • They can be used to avoid duplication

For this example we build upon the active deathstar protocol which in our case controls how the security system reacts to an unknown, hooded person (you all know that hooded strangers on a deathstar are always undercover jedi).

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

Pitfalls of the MINUS-comparison

My work includes a lot of database views because they work as a kind of public API in our case. Since I started talking about self-testing and utPLSQL, the MINUS-comparison has always had its place when assuring that a new, reconstructed view has the exact same content as the old one.

However, I did it wrong until recently when I discovered a major pitfall.

For I like to tell stories let’s start with a Star Wars scenario again: We have a table which contains all the Star Wars characters and another table which holds information in which of the “Episode”-movies they appeared.

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