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.
A little over a year ago we have ventured into mathematical foundation of relational theory. From algebraic geometry perspective relations were viewed as Finite Varieties. In the followup exposition we were able to describe functional dependencies via explicit analytic formulas. In this article we shift the focus from varieties to dual algebraic object — [polynomial] ideals.
You know that if we want to execute another script from the current script directory, we can call it through @@, but sometimes we want to know the current path exactly, for example if we want to spool something into the file in the same directory.
Unfortunately we cannot use “spool @spoolfile”, but it is easy to find this path, because we know that SQL*Plus shows this path in the error when it can’t to find @@filename.
So we can simply get this path from the error text:
Supposing you've got data as a text string with "row" and "column" delimiters and you would like to parse out those columns and rows. The external table syntax would actually be great for this, but that requires writing the text out to a file first, and then you can import it back again as columns and rows. There are various other alternatives using SQL and/or XML manipulations, but one method that I don't see much used is to use the Data Cartridge functionality to in a sense expand the SQL language to do this.
In my last post, I wrote about a dream of a European version of ODTUG Kscope conference - working title "Escope." ;-) We're trying to determine whether it would be feasible to create such a conference - if you haven't already, go and fill out the survey and help us find out if there's basis for the dream.