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:
As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.
Test function and tables
create or replace function f_without_deps(p_tab varchar2) return varchar2
execute immediate 'select '''||p_tab||''' from '||p_tab||' where rownum=1' into res;
create table a as select 'a' a from dual;
create table b as select 'b' b from dual;
create view v_ab as select a,b from a,b;
Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.