Learning is not a spectator sport

I know … I know … I probably sound like a broken record. As I scroll back through my blog archives, I see myself continuously bleating on about using data types carefully in the database.

But rest assured – I ain’t done! Yes, I’m back with another warning about taking care when mixing and matching data types in your applications. This one comes from our just released 26ai flagship.

First I’ll create some simple sample data

SQL> create table person ( person_id number, name varchar2(10));

Table created.

SQL> insert into person values (100,'Connor');

1 row created.

SQL> insert into person values (200,'Suzy');

1 row created.

We’ve got some people, each associated with an ID. Let’s assume that when they login to our application we populate a variable “CURRENTLY_LOGGED_IN_PERSON” with their ID from the person table.

Thus when it comes to querying the person table, we might have a report based on a query something like this:

SQL> select
  2    person_id,
  3    case
  4      when person_id = :currently_logged_in_person then 'Logged In'
  5    end status
  6  from
  7    person;

 PERSON_ID STATUS
---------- ---------
       100 Logged In
       200

So far so good. Savvy developers will also probably know that we have searched CASE expressions as well, which means we can rewrite:

case when person_id = :currently_logged_in_person then ‘Logged In’ end status

in the following way, known as a searched CASE expression:

case person_id when :currently_logged_in_person then ‘Logged In’ end status

This can be particularly useful when you have a list of WHEN conditions that all apply to the same input expression.

The two CASE expressions seems to synonymous, but look what happens when I substitute the alternate expression into my query:

SQL> select
  2    person_id,
  3    case person_id
  4      when :currently_logged_in_person then 'Logged In'
  5    end status
  6  from
  7    person;
    when :currently_logged_in_person then 'Logged In'
          *
ERROR at line 4:
ORA-00932: expression (:1) is of data type CHAR, which is incompatible with expected data type NUMBER

There is nothing really wrong with my CASE expression. Where I really made the mistake was how I defined my variable

SQL> variable currently_logged_in_person varchar2(100);

I defined it as a VARCHAR2 but I then used it in a CASE expression against a numeric datatype. As I keep banging the drum – just relying on automatic datatype conversion by the database is a bad idea.

I can easily correct this by either defining the variable as a NUMBER datatype, or by converting it to a number before using it in my CASE expression.

SQL> select
  2    person_id,
  3    case person_id
  4      when to_number(:currently_logged_in_person) then 'Logged In'
  5    end status
  6  from
  7    person;

 PERSON_ID STATUS
---------- ---------
       100 Logged In
       200

So how does this all relate to 26ai? There are two important implications here

• In 26ai we’ve extended and improved the CASE function, and in doing so, we brought it into alignment with the SQL standard on how it should handle datatypes, so we are more strict with datatypes
• Also in 26ai, it is possible that optimizer may transform your simple CASE expression into a searched CASE expression

So even if you did not write a searched CASE expression, your query may be ultimately be presented to the database having one, once transformations have occurred.

This is of particular importance to APEX developers, because we often simply refer to page and application level items in our queries without really taking the due diligence on datatype conversions.

If your APEX items are being compared to numeric or date expressions, then make sure you explicitly cast those items to the correct datatypes, otherwise you might get a shock on 26ai.