[rt-devel] Who would have guessed that VARCHAR2 and CLOB are so different (Was: SearchBuilder/Oracle inconsistencies)

[Brook Schofield]

Brook Schofield wrote on 14/05/2003 12:38:39 AM:
> 
> Well - I now know why the [crit] lines appear in the Oracle 
> initialize-database output. This is because the SQL SELECT to load 
> columns from the Groups table doesn't work.
> 
> Oracle SQL:
> ===========
> SimpleQuery: SELECT  * FROM Groups WHERE lower(Domain) = ? AND 
> lower(Name) = ? AND lower(Type) = ? AND lower(Instance) = ?^M
>          systeminternal^M
>          ^M
>          everyone^M
>          ^M

I modified the Oracle SQL query to force either = '' or IS NULL - but I 
still ran into strange problems. The issue appears to be with the 
difference between VARCHAR2 and CLOB types in Oracle 9i. From the Oracle 
9i manual:

IS NULL Semantic Discrepancy 
In the SQL 92 standard, a character string of length zero is distinct from 
a null string. 
For an initialized LOB of length 0, you should expect `IS NULL' to return 
zero (FALSE), since it is the correct and standard compliant behavior. In 
contrast, a VARCHAR2 of length 0 returns TRUE on 'IS NULL`. 
In addition, for the LENGTH() function:
If the input is a character string of zero length, LENGTH() returns NULL.
For a CLOB of zero length, an EMPTY_CLOB(), zero is returned by LENGTH and 
DBMS_LOB.GETLENGTH() in SQL and PL/SQL.
This can be misleading. Note this semantic discrepancy.
This last line is actually from the Oracle 9i manual - not my own addition 
;-)
So I'll be left with changing the query from:
SimpleQuery: SELECT  * FROM Groups WHERE lower(Domain) = ? AND lower(Name) 
= ? AND lower(Type) = ? AND lower(Instance) = ?^M
          systeminternal^M
          ^M
          everyone^M
          ^M
to:

SELECT  * FROM Groups WHERE lower(Domain) = ? AND (Name = '' OR Name IS 
NULL) AND lower(Type) = ? AND (Instance = '' OR Instance IS NULL)
          systeminternal^M
          everyone^M

to satisfy the issue with VARCHAR2 and CLOB. My DBAs tell me that Oracle 
recommends the use of VARCHAR2 over VARCHAR because the behaviour of 
VARCHAR may change over time - hopefully they'll change VARCHAR to have 
the same behaviour as their CLOB type in some future edition - which will 
allow SearchBuilder to be simplified.

Now the only issue is whether the change should be within LoadByCols or 
whether it should be abstracted out to an Oracle specific change?

> sub LoadByCols  {
>      my $self = shift;
>      my %hash  = (@_);
>      my (@bind, @phrases);
>      foreach my $key (keys %hash) {
-          if (defined $hash{$key}) {
+          if (defined $hash{$key} && $hash{$key} ne '') {
>                  push @phrases, "$key = ?";
>                  push @bind, $hash{$key};
>          }
>          else {
-                  push @phrases, "$key is null";
+                  push @phrases, "($key IS NULL or $key = '')";
>          }
>      }
> 
>      my $QueryString = "SELECT  * FROM ".$self->Table." WHERE ".
>      join(' AND ', @phrases) ;
>      return ($self->_LoadFromSQL($QueryString, @bind));
> }

NB:- The change for adding/removing the lower() tag should be made in 
SearchBuilder.pm.

I know I always say this, but "Oracle support for RT is one step closer" 
;-)

-Brook