#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