msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Cool Undocumented OVERLAPS Predicate

David Aldridge’s post about OVERLAPS caught my attention. I did not know that you could do something like this in Oracle:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 22:06:24 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect eddie/awad
Connected.
SQL>  SELECT *
  2     FROM dual
  3    WHERE (add_months(sysdate, -6), DATE '2008-08-08')
  4          OVERLAPS
  5          (sysdate - 180, interval '2' YEAR);

D
-
X

Indeed, like David, I searched the Oracle docs and I could not find anything describing OVERLAPS. I extended my search to the OTN Forums and I got these hits:

So, what does OVERLAPS really do? according to Mimer SQL Reference Manual:

The OVERLAPS predicate tests whether two “events” cover a common point in time or not, and has the form:

(expression, expression) OVERLAPS (expression, expression)

Each of the two “events” specified on either side of the OVERLAPS keyword is a period of time between two specified points on the timeline. The two points can be specified as a pair of datetime values or as one datetime value and an INTERVAL offset.

Each “event” is defined by a two expressions constituting a row value expression having two columns.

The first column in each row value expression must be a DATE, TIME or TIMESTAMP and the value in the first column of the first “event” must be comparable.

The second column in each row value expression may be either a DATE, TIME or TIMESTAMP that is comparable with the value in the first column or an INTERVAL with a precision that allows it to be added to the value in the first column.

The value in the first column of each row value expression defines one of the points on the timeline for the event.

If the value in the second column of the row value expression is a datetime, it defines the other point on the timeline for the event.

If the value in the second column of the row value expression is an INTERVAL, the other point on the timeline for the event is defined by adding the values in the two column of the row value to expression together.

The NULL value is assumed to be a point that is infinitely late in time.

Either of the two points may be the earlier point in time.

If the value in the first column of the row value expression is the NULL value, then this is assumed to be the later point in time.

I wonder if or when a similar documentation will be added to the Oracle SQL Reference.

Warning: Undocumented features should not be used in production systems.

3 Comments | Filed in Oracle | Tags: ,


Previously Undocumented LNNVL SQL Function Buggy

LNNVL is a SQL function that takes a condition as an argument. It returns TRUE if the condition is FALSE or NULL. It returns FALSE if the condition is TRUE. It is available but not documented in Oracle database versions prior to 10gR1.

In his comment to my previous post about this function, Laurent demonstrated how LNNVL could generate the error ORA-03113: end-of-file on communication channel (tested on Oracle XE):

SQL> select * from dual where not lnnvl(1=1);
select * from dual where not lnnvl(1=1)
                                    *
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> select * from dual;
ERROR:
ORA-03114: not connected to ORACLE

It looks like a bug to me.

4 Comments | Filed in Oracle, Tips | Tags: , , ,


Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

In Oracle, NULL does not equal NULL:

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE NULL = NULL;

  no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

  EDDIE@XE> CREATE TABLE t (
    2  col1  VARCHAR2(10),
    3  col2  VARCHAR2(10));

  Table created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', NULL);

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'chicken');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, NULL);

  1 row created.

  EDDIE@XE> COMMIT;

  Commit complete.

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t;

  COL1       COL2
  ---------- ----------
  monkey     I am null
  monkey     chicken
  monkey     monkey
  I am null  monkey
  I am null  I am null

Now, I want to select all the records from table t where col1 equals col2:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE col1 = col2;

  COL1       COL2
  ---------- ----------
  monkey     monkey

As expected, the row that has NULL in both col1 and col2 was not returned. You want this record returned. Here is how you can do it:

Using the undocumented function SYS_OP_MAP_NONNULL:

This function has been around for a while, but is still undocumented as of the latest Oracle database release (10gR2). Being undocumented means that it should not be used in production code, but it does not hurt to know that it exists and explore how it works. This function makes it possible to have NULL = NULL:

  EDDIE@XE> SELECT sys_op_map_nonnull (NULL)
    2    FROM DUAL;

  SY
  --
  FF

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);

  'HITHERE
  --------
  hi there

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2);

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using simple logical operators:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE (col1 = col2 OR (col1 IS NULL AND col2 IS NULL));

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using DECODE:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE DECODE (col1, col2, 'match', 'no match') = 'match';

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using NVL:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE NVL (col1, 'impossible value') = NVL (col2, 'impossible value');

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Of course, in this case, you have to be absolutely, positively 100 % sure that col1 and col2 will never have a value of ‘impossible value’ in them. Moreover, instead of NVL, you can also use similar functions.

Sources and Resources:

8 Comments | Filed in Oracle, Tips | Tags: , , ,


Cool Undocumented SQL Function: REVERSE

Let’s say that you want to check whether a word or a sentence reads the same backward or forward, how do you do it in SQL? you use the REVERSE function. We’ll see an example of that below, but first, a warning. Unlike LNNVL, REVERSE is still an undocumented SQL function, which means that it should be used with caution because “undocumented” also means “not supported”. With that warning out of the way, let’s explore this undocumented function and find out what other documented alternatives exist that have the same functionality as REVERSE.

REVERSE has been around since 8i, maybe even prior to 8i. I have tested it on 8.1.7 and it worked. Since REVERSE is undocumented, the best way to know what it does is to try it out. So, let’s go through a few examples: Continue reading…

6 Comments | Filed in Oracle | Tags: , ,


About The BUILTIN, FIPSFLAG and INTERFACE Pragmas in Oracle

A few weeks ago I blogged about PRAGMAs in PL/SQL. As you all know, as of the current release of PL/SQL, we have 4 documented pragma directives: AUTONOMOUS_TRANSACTION, RESTRICT_REFERENCES, EXCEPTION_INIT and SERIALLY_REUSABLE.

Karl posted a comment to draw my attention to the fact that in the SYS.STANDARD PL/SQL package, Oracle uses three additional undocumented pragma directives: BUILTIN, FIPSFLAG and INTERFACE. Just to satisfy my curiosity, I tried to find more information about them. Here is what I found:

PRAGMA BUILTIN:

An example of its usage in the SYS.STANDARD PL/SQL package:

function SQLCODE return PLS_INTEGER;
  pragma BUILTIN('SQLCODE',45, 10, 0);

This is what Pete Finnigan wrote about it on Nov 22 2003:

I don’t know the exact mechanism for the pragma builtin keyword but the function is almost certainly implemented in C and would be called in the SQL engine directly so should be reasonably efficient.

PRAGMA FIPSFLAG:

An example of its usage in the SYS.STANDARD PL/SQL package:

function SQLERRM return varchar2;
 pragma FIPSFLAG('SQLERRM', 1452);

I could not find any meaningful information about this directive.

PRAGMA INTERFACE:

An example of its usage in the SYS.STANDARD PL/SQL package:

function pesacos(n NUMBER) return NUMBER;
 pragma INTERFACE (c,pesacos);

I found three sources of information about this INTERFACE directive:

  • The STANDARD Package description from chapter 1 of Oracle Built-in Packages book by Steven Feuerstein. Here is what he wrote:

    Here is the implementation of LIKE:

      function 'LIKE' (str varchar2, pat varchar2) return boolean is
      begin
        return peslik(str, pat);
      end;
    

    What is this peslik function? Ah, that is where, when, and how Oracle “cheats” (or, at least, makes the rest of us PL/SQL developers jealous):

      function peslik(str varchar2, pat varchar2) return boolean;
          pragma interface (c,peslik);
    

    The peslik function is a stub program for a callout to C.

  • PL/SQL wrap utility from Exploiting and Protecting Oracle paper by Pete Finnigan. Here is what he wrote:

Yes, that’s right most of PL/SQL and Oracle’s built in packages are written in ‘C’ and these ‘C’ functions are called through a different mechanism than the one used by user programs from Oracle 8. The syntax is as follows:

procedure do_something(a_var binary_integer, another_var binary_integer);
    pragma interface (C, do_a_c_function);  

PLS-00129: Pragma INTERFACE only supports C as its first argument

Cause: The first parameter passed to pragma INTERFACE specified a host language other than C. Currently, C is the only host language supported. The parameter might be misspelled, or the pragma syntax might be faulty (for example, a comma might be missing between two parameters).

Action: Check the spelling of the first parameter, which should be C, and make sure the proper syntax was used.

Now that you know about these undocumented pragma directives, what next? nothing. It’s just interesting to know that they exist and to see how Oracle implements calls to its built-in functions behind the scenes. There is however some mystery surrounding the use of PRAGMA FIPSFLAG. If you have any additional information, please share with the rest of us. Thanks.

4 Comments | Filed in Interesting, Oracle | Tags: , ,


Undocumented LNNVL SQL function now safe to use

Connect to your Oracle database and try the following:

SELECT 'hi there' AS mycol
 FROM DUAL
 WHERE lnnvl (1 = 2) 

You should get :

 MYCOL 
 ---------- 
 hi there 

Now, you ask, what is LNNVL? You search the Oracle documentation, but you cannot find any mention of it in releases prior to 10gR1, yet you successfully execute the above query in your Oracle 9.2 (or even 8.1.7) database!

LNNVL had been an undocumented SQL function until Oracle database version 10gR1. Let’s go back in time a bit and read what Jonathan Lewis and Lex de Haan wrote about it:

From: Jonathan Lewis
Date: Sun, Nov 2 2003 10:18 am
To: comp.databases.oracle.server
Message:

lnnvl(predicate) is true if predicate is false or null. I can’t say "for sure", but I believe it appeared somewhere around 7.3, or maybe 7.2 to handle problems arising from the SQL generated by a query co-ordinator for its parallel query slaves when Oracle was trying to get partition elimination on partition views. (And if it really is there for PQ problems, I’d second Richard’s advice – don’t use it, you never know when it’s going to disappear).

 

From: Lex de Haan
Date: Sat, 29 May 2004 21:27:53
To: oracle-l
Message:

The ANSI/ISO SQL standard offers the three operators you need: "IS TRUE", "IS FALSE", and "IS UNKNOWN". But as far as I know, no vendor has implemented these. Note the power of these operators; they accept a Boolean argument. The "IS NULL" becomes very clumsy as soon as multiple predicates are involved. One nice Oracle function (that has been around for quite a while, but was undocumented until 10g) is the LNNVL function.

The Oracle’s LNNVL function:

  • Is similar to the SQL/Foundation:2003 standard’s IS NOT TRUE (or IS FALSE).
  • Is not documented in Oracle database versions prior to 10gR1.
  • Provides a concise way to evaluate a condition when one or both operands of the condition may be null.
  • Can be used only in the WHERE clause of a query.
  • Takes a condition as an argument. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.
  • Returns TRUE if the condition is FALSE or UNKNOWN (NULL).
  • Returns FALSE if the condition is TRUE.
  • Is sometimes used internally by the Oracle Database to rewrite queries.

So, if you’re stuck with Oracle 8i or 9i, you can safely use the undocumented LNNVL function, knowing that it has become documented and supported in 10g and above.

Related Oracle documentation.

6 Comments | Filed in Oracle | Tags: , ,