[Question] lowering the COMPATIBLE version to 21.0.0

[mprins]

We have legacy integration tests (and probably code) failing on the new BOOLEAN type so for the time being I would like to revert to setting COMPATIBLE to 21.0.0 instead of 23.0.0.

I've naively tried forcing by running ALTER SYSTEM SET COMPATIBLE='21.0.0' SCOPE = SPFILE; and restarting the database, which fails of-course.

I've looked at the createDatabase script but that doesn't seem to have an option to do this eg. setting as an env variable or similar; I've found the GH-action as well, but that has no obvious option to set the compatible level either.

[gvenzl]

Hi @mprins,

There might be a misunderstanding of what the COMPATIBLE parameter does, it can only be increased but never decreased. For more information, see the Oracle Database documentation: https://docs.oracle.com/en/database/oracle/oracle-database/23/upgrd/what-is-oracle-database-compatibility.html#GUID-4711E0D1-9FCF-4F35-85B5-52EBB437C00E

If you were to run an Oracle Database Free with COMPATIBLE set to 21.0.0, it would not allow the use of the BOOLEAN data type altogether. It would not auto-convert these columns into VARCHAR2 or NUMBER, in case you were thinking that.

[mprins]

If you were to run an Oracle Database Free with COMPATIBLE set to 21.0.0, it would not allow the use of the BOOLEAN data type altogether.

This is what I would expect; we have legacy ddl that uses 1/0 and TRUE/FALSE values that are now returning boolean in the Java code thereby auto-converting the string values to boolean values, breaking the application

[gvenzl]

The database would still return a number for a NUMBER column and a string for a VARCHAR2 column (note the Typ number for the respective columns):

SQL> create table bool_test (col1 number, col2 varchar2(5), col3 bool);

Table BOOL_TEST created.

SQL> insert into bool_test values (1, 'TRUE', true), (0, 'FALSE', false), (1, 'true', true), (0, 'false', false);

4 rows inserted.

SQL> commit;

Commit complete.

SQL> select col1, dump(col1), col2, dump(col2), col3, dump(col3) from bool_test;

   COL1 DUMP(COL1)            COL2     DUMP(COL2)                            COL3 DUMP(COL3)
_______ _____________________ ________ __________________________________ _______ ___________________
      1 Typ=2 Len=2: 193,2    TRUE     Typ=1 Len=4: 84,82,85,69                 1 Typ=252 Len=1: 1
      0 Typ=2 Len=1: 128      FALSE    Typ=1 Len=5: 70,65,76,83,69              0 Typ=252 Len=1: 0
      1 Typ=2 Len=2: 193,2    true     Typ=1 Len=4: 116,114,117,101             1 Typ=252 Len=1: 1
      0 Typ=2 Len=1: 128      false    Typ=1 Len=5: 102,97,108,115,101          0 Typ=252 Len=1: 0

Would you, by chance, have the SQL statements that return BOOLEAN now instead of NUMBER/VARCHAR2?

[mprins]

turns out we've got a bug in our code that this helped uncover.

I't not actually the select that is the problem but the insert. We were inserting 'false' into a varchar2(5) field using a prepared statement with boolean type set, in 21 and lower this resulted in '0', in 23 this results in 'FALSE' (or something along those lines, too much debugging today... so it could be the other way around) Anyway, we will fix the bug first and see if this is still a valid question for us, I will assume not for now ;-)

[gvenzl]

Awesome, glad to hear it!

Hm, we do implicit data type conversions for case-insensitive 'FALSE', 'TRUE', 'YES', 'NO', 't', 'f'. I know because I put the sheet together what to convert :D

You can find the whole list here: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Data-Types.html#GUID-285FFCA8-390D-4FA9-9A51-47B84EF5F83A

Even an INSERT with a boolean value into a VARCHAR2(5) or vice versa (varchar2 value into boolean col) should work.

SQL> create table foo (b bool, v varchar2(5));

Table FOO created.

SQL> insert into foo (b, v) values ('false', false);

1 row inserted.

SQL> insert into foo (b, v) values ('FALSE', FALSE);

1 row inserted.

SQL> select * from foo;

   B V
____ ________
   0 FALSE
   0 FALSE

But in any case, I'm happy that you solved the issue! And thanks again for using these images!