Oracle VM Consulting
view counter

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

12c: New SQL PLAN OPERATIONS and HINTS

This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.

RESULT_CACHE: run-time dependency tracking

As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.

A function gets called twice if the result_cache is used

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.

Conditional Compilation and Static Boolean

One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.

Splitting a Table into Rowid Ranges of Equal Size

In the OTN forum, Jonathan Lewis recently asked for an efficient SQL solution to split a table into 12 ROWID ranges having the same (+-1) number of blocks. I'm posting here a slightly cleaned-up version of my answer so I can change it if necessary after the question gets archived.

Lattice of Ideals

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.

SQL*Plus tips #7: How to find the current script directory

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:

ODCI dynamic table function parser

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.

Escope very early status

Just a brief summary of the status of Escope so far - I know it's very very early and too early to even tell whether it'll become real or not, but anyway ... ;-)

  1. For those who don't know yet, we're trying to see if it is possible to create a conference in Europe like the Kscope conference by ODTUG. See my early thoughts here:http://dspsd.blogspot.com/2015/06/feasibility-survey-of-possible-european.html

How to make dream of Escope come true?

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.

view counter