Incompatibility with Oracle 23ai

[olijaun]

Hi

I want to let you know that we had a problem with Oracle 23ai.

We want to switch from Oracle 19 to Oracle 23 ai. The first step was to update the Oracle JDBC driver (Implementation-Version: 23.8.0.25.04) but still using Oracle 19 database, so that the database can be switched later at any time. Unfortunately this does not work.

The problem seems to be that with Oracle 23 there is a new boolean datatype (after only 40+ years ;-) ).

This leads to the following error:

Caused by: Error : 12899, Position : 232, SQL = insert into "EVENT_JOURNAL" ("DELETED","PERSISTENCE_ID","SEQUENCE_NUMBER","WRITER","WRITE_TIMESTAMP","ADAPTER_MANIFEST","EVENT_PAYLOAD","EVENT_SER_ID","EVENT_SER_MANIFEST","META_PAYLOAD","META_SER_
ID","META_SER_MANIFEST")  values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ) RETURNING ORDERING INTO :13  [SQL INCLUDES EXPRESSIONS ADDED BY THE ORACLE JDBC DRIVER], Original SQL = insert into "EVENT_JOURNAL" ("DELETED","PERSISTENCE_ID","SEQUENCE_NUMBER","
WRITER","WRITE_TIMESTAMP","ADAPTER_MANIFEST","EVENT_PAYLOAD","EVENT_SER_ID","EVENT_SER_MANIFEST","META_PAYLOAD","META_SER_ID","META_SER_MANIFEST")  values (?,?,?,?,?,?,?,?,?,?,?,?) RETURNING ORDERING INTO ?, Error Message = ORA-12899: value too large for column "AOO_TESTS"."EVENT_JOURNAL"."DELETED" (actual: 5, maximum: 1)

The reasons seems to be that the 'DELETED' column has the datatype CHAR(1). In the code there's (see :

    val deleted: Rep[Boolean] = column[Boolean](journalTableCfg.columnNames.deleted, O.Default(false))

Somehow with the new Oracle Driver it seems that it tries to write "false" to the CHAR column which fails, because the column is specified as one byte.

One solution would be to change the column type to "boolean". However this would make the sql-Script only usable with Oracle 23.

We solved it by using NUMBER(1,0) which works with the old and with the new driver.

Regards
Oliver

[pjfanning]

@olijaun that CHAR(1) for boolean seems like an odd choice alright. I created #323 but the question is whether we can just make the change and include documentation highlighting the change or if we need to also support configuration to let users choose whether CHAR(1) or NUMBER(1) is used.

[olijaun]

Thanks a lot @pjfanning ! This was extremely quick :-) I don't know what your policies are regarding schema changes. I think the problem is especially for existing users that already have the CHAR columns. They would have to migrate.

[pjfanning]

Once the schema is installed, it is installed.
If users upgrade Oracle db and/or Oracle JDBC driver, they have to test the new setup. They can easily upgrade their schema if they hit issues.
The real issue here is that Oracle have changed the JDBC driver interaction with their DB so that char(1) is not valid for boolean - that is their regression.

All we can really do at a Pekko level is document this.

#323 helps new users

[olijaun]

Sure. Sounds good.