Object Types are a very useful thing when used in PL/SQL, but Oracle even allows us to have them inside a table.
Let’s see how we can put the numeric_strucutre-logic inside the Force Powers table and use indizes on the new column.
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.
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.
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.
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
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…)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
If it comes to testing updatable views, the use of nested tables from ROWTYPE records in combination with utPLSQL’s cursor comparison is invaluable.
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.
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.