Oracle Database Free

Hi All,

I'm trying to migrate an existing schema to 23ai and I'm getting some issues with boolean.

In former schema all booleans were declared as number(1), a user packaged defined constants as follows

CREATE OR REPLACE PACKAGE ck
AS
  FUNCTION True
    RETURN NUMBER;

  FUNCTION False
    RETURN NUMBER;
END ck;
/
CREATE OR REPLACE PACKAGE BODY ck
AS
  FUNCTION True
    RETURN NUMBER
  IS
    wVal                      CONSTANT SMALLINT := 1;
  BEGIN -- True
    RETURN wVal;
  END True;

  FUNCTION False
    RETURN NUMBER
  IS
    wVal                      CONSTANT SMALLINT := 0;
  BEGIN -- False
    RETURN wVal;
  END False;
BEGIN -- ck
  NULL;
END ck;
/

CREATE TABLE t1(id NUMBER, text VARCHAR2(255) ,expiration_date DATE ,archived NUMBER(1));
INSERT INTO t1 VALUES(1 ,'Entry 1' ,NULL ,0);
INSERT INTO t1 VALUES(2 ,'Entry 2' ,CURRENT_DATE ,0);
INSERT INTO t1 VALUES(3 ,'Entry 3' ,NULL ,1);

CREATE OR REPLACE FORCE VIEW v1
  (id
  ,text
  ,inactive
  )
AS
  SELECT t1.id
        ,t1.text
        ,CASE WHEN t1.expiration_date <= CURRENT_DATE THEN ck.true ELSE t1.archived END
    FROM t1
/
show error view v1
SQL> show error view v1
Errors for VIEW V1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-00932: expression ("T1"."ARCHIVED") is of data type NUMBER,
         which is incompatible with expected data type BOOLEAN

Which suggests that the DB engine is converting number(1) to boolean…

(in this specific case a TO_NUMBER(ck.true) solves the issue, but there's a number of view and, in addition, packages/procedure which migration would require code changes)

Is there any options to prevent this behaviour?

Thanks you in advance