#100CodeExamples
Structure Force Powers with PL/SQL Object Types
Modern SQL and especially PL/SQL is turing complete and can be used to solve every problem, but Oracle SQL Types can help greatly to do it in a much more readable way.
#100CodeExamples
Narrate Star Wars Movies with utPLSQL annotations
Did you know you can narrate the story of the Star Wars movies with utPLSQL annotations?
In this blog post I show you how and list 4 takeaways you might not have known.
#100CodeExamples
More Pitfalls of the MINUS comparison
Comparing the results of 2 queries is a task more complicated than I thought initially.
I show some more pitfalls of the MINUS approach (which I still find convenient in some cases) and alternatives.
#100CodeExamples
Testing the Deathstar security with utPLSQL contexts
The context-feature of #utPLSQL can greatly help to organize your tests and reduce the setup/teardown time of tests.
Learn how to use them to test the Deathstar security system.
#100CodeExamples
#100CodeExamples
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 ID | Planet Name | Planet Faction |
| 1 | Korriban | imperium |
| 2 | Korriban | imperium |
| 3 | Dromund Kaas | imperium |
| 4 | Hoth | republic |
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>)
(more…)#100CodeExamples
Pitfalls of the MINUS-comparison
The MINUS comparison of two views (or other statements) is a powerful tool to check the validity of a new, rewritten approach I use often.
However, be aware of
#100CodeExamples
#100CodeExamples – Test updatable views with utPLSQL Cursors
If it comes to testing updatable views, the use of nested tables from ROWTYPE records in combination with utPLSQL’s cursor comparison is invaluable.
#100CodeExamples
#100CodeExamples – Constants in PL/SQL and SQL
Constants help us lazy devs in many ways, reduce the likelyness of unnoticed typos and prevent a given value from changing.
While PL/SQL supports them, it’s a bit tricky to get them into SQL.
#100CodeExamples
#100CodeExamples – Only one active protocol
For those who didn’t know: the death star runs on one of several protocols, but only one may be active at a time.
This can be easily achieved with simple tools most relational databases provide.
#100CodeExamples
#100CodeExamples – Use the force to replace objects
Objects in oracle can be used to create nested table collections, which are accessible from SQL.
However, to change these objects once referenced by a table type can be very annoying unless you use the force.