Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
6 août 2013 2 06 /08 /août /2013 11:18

Last week, i was quite confused when i read on a forum that Oracle SQL does not support boolean type. Posted question looked like this : 

 

CREATE OR REPLACE FUNCTION test_bool(i number) RETURN boolean AS

BEGIN

return true;

END;

 

Function created.

 

SELECT *

FROM dual

WHERE test_bool(1) = true

 

ERROR at line 3:

ORA-00904: "TRUE": invalid identifier

 

I was extremely confident that it could be working this way : 


SELECT *

FROM dual

WHERE test_bool(1);         

 

... or also like this :  

 

 

SELECT CASE WHEN test_bool(1) THEN 'hello world' END

FROM dual;

 

... but it ends up with the same error message : 

 

ORA-00920: invalid relational operator

 

Why would i expect such a syntax to work ?


SELECT *

FROM dual

WHERE regexp_like('a', 'a');

 

D

-

X

 

SELECT CASE WHEN regexp_like('a', 'a') THEN 'hello world' END

FROM dual;

 

CASEWHENREG

-----------

hello world

 

I often read that regexp_like returns a boolean. While it clearly returns a boolean in PL/SQL, it does not look like it does in SQL...

 

DECLARE

b boolean;

BEGIN

b:=regexp_like('a', 'a');

if b then

dbms_output.put_line('I love UMP !');

end if;

END;

 

I love UMP !

 

By the way, just have a look into the standard package : 

 

SELECT *

FROM dba_source

WHERE line BETWEEN 2312 AND 2318

  AND owner = 'SYS'

  AND name = 'STANDARD'

  AND type = 'PACKAGE'

ORDER BY line

 

TEXT

-----------------------------------------------------------------------

  -- REGEXP_LIKE --

  function REGEXP_LIKE (srcstr   VARCHAR2 CHARACTER SET ANY_CS,

                        pattern  VARCHAR2 CHARACTER SET srcstr%CHARSET,

                        modifier VARCHAR2 DEFAULT NULL)

    return BOOLEAN;

    pragma FIPSFLAG('REGEXP_LIKE', 1452);


 

I presume there is a specific implementation of these functions for SQL use, probably through another object wrapping the basic regexp_like function, while the standard function only works in PL/SQL : 


DECLARE

b boolean;

BEGIN

b:=sys.standard.regexp_like('a', 'a');

if b then

dbms_output.put_line('I love JF Copé !');

end if;

END;

 

I love JF Copé !

 

... but it does not in SQL !


SELECT *

FROM dual

WHERE sys.standard.regexp_like('a', 'a');

 

ERROR at line 3:

ORA-00920: invalid relational operator

 

Unlike for REGEXP_SUBSTR, Oracle documentation actually does not describe REGEXP_LIKE as a function that returns a boolean, but rather as a condition that evaluates to true or false.

 

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

 

http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions001.htm

A condition could be said to be of a logical datatype, although Oracle Database does not formally support such a datatype.

 

Oracle SQL implements boolean algebra without booleans (by replacing them with condition evaluations !)... kind of creepy :)

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires

laurent schneider 06/08/2013 14:53

indeed, plsql and sql do not use the same syntax.

A well known example is DECODE

BEGIN :x := decode(1,1,1); END;
Error at line 1
ORA-06550: line 1, column 13:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Have fun with :

select * from dual where lnnvl(1=0) and lnnvl(1=0) is null;

Pacman 07/08/2013 09:13



Nice one Laurent !


Actually, lnnvl should not "return" anything, or more precisely it should raise an exception like regexp_like does : 


select * from dual where regexp_like('a', 'a') is not null


                                               *


ERROR at line 1:


ORA-00933: SQL command not properly ended


 


Instead, since it cannot return a boolean, it always returns null :


SELECT lnnvl(1=1), lnnvl(1=0), lnnvl(1=null), lnnvl(null is null) from dual;


 


LNNVL(1=1) LNNVL(1=0) LNNVL(1=NULL) LNNVL(NULLISNULL)


---------- ---------- ------------- -----------------


But anyway, what puzzles me, is that somehow the base element of the Oracle SQL logical algebra is not the boolean, but instead kind of the equivalence classes of the conditions evaluating to
true, false and unknown...