Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the FROM clause), but then they get errors like this:
Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions. Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.
Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences. Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes). Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99) To solve this cent-problem, we decide that the difference should be added (or subtracted) on the last row.
I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12. Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches. But can there be uses where we want overlapping or even nested matches? Well, I found at least one case where I think it makes perfect sense...
In the August 2015 issue of Scientific American, I came across an article titled "Planet Hard Drive", a "thought experiment" arguing that we can think of Earth as a kind of "hard drive" and "although Earth has an enormous capacity to store information, order is still rare....but the growth of order on Earth also stems from the production of cultural information."
When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types. The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure. If you are unfamiliair with this technique, there are some links at the bottom of this article.
Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.
I gave my students this ORDER BY clause as an example: