This is going to be longer post. We had an application done in JDeveloper (VO SQL below) that worked fine against Oracle DB 12c, but after upgrading our backend to 19c we are getting the error:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
To reproduce this problem we have 4 tables:
CREATE TABLE "TSS"."SCH_AVAILABILITY"
( "AVAILABILITY_ID" NUMBER NOT NULL ENABLE,
"RESOURCE_ID" VARCHAR2(6 BYTE) NOT NULL ENABLE,
"WORKING_START_TIME" DATE NOT NULL ENABLE,
"WORKING_END_TIME" DATE NOT NULL ENABLE,
"STUDY_ID" VARCHAR2(2 BYTE),
"DATE_TIME_ADDED" DATE NOT NULL ENABLE,
"STAFF_ID" VARCHAR2(4 BYTE) NOT NULL ENABLE,
PRIMARY KEY ("AVAILABILITY_ID")
);
CREATE TABLE "TSS"."SCH_TASK"
( "TASK_ID" NUMBER NOT NULL ENABLE,
"TASK_WORK_TYPE_ID" VARCHAR2(2 BYTE) NOT NULL ENABLE,
"TASK_START_TIME" DATE NOT NULL ENABLE,
"TASK_END_TIME" DATE NOT NULL ENABLE,
"TASK_TYPE_ID" VARCHAR2(3 BYTE),
"STUDY_ID" VARCHAR2(2 BYTE) NOT NULL ENABLE,
"STUDY_PHASE_ID" VARCHAR2(2 BYTE) NOT NULL ENABLE,
"RESOURCE_ID" VARCHAR2(6 BYTE) NOT NULL ENABLE,
"TASK_STATUS_ID" VARCHAR2(2 BYTE),
"TASK_NOTE" VARCHAR2(500 BYTE),
"INDIVIDUAL_ID" NUMBER,
"GROUP_ID" NUMBER,
"RELATIONSHIP_ID" VARCHAR2(3 BYTE),
"NOTIFIED" VARCHAR2(1 BYTE),
"DATE_TIME_ADDED" DATE NOT NULL ENABLE,
"STAFF_ID" VARCHAR2(4 BYTE) NOT NULL ENABLE,
"SCHEDULE_ID_OLD" NUMBER,
"STUDY_TEST_ID_OLD" NUMBER,
PRIMARY KEY ("TASK_ID", "RESOURCE_ID")
);
CREATE TABLE "TSS"."SCH_RESOURCE"
( "RESOURCE_ID" VARCHAR2(6 BYTE) NOT NULL ENABLE,
"RESOURCE_TYPE" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"RESOURCE_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"STAFF_ID" VARCHAR2(4 BYTE),
"EMAIL" VARCHAR2(50 BYTE),
PRIMARY KEY ("RESOURCE_ID")
);
CREATE TABLE "TSS"."SCH_RESOURCE_STUDY"
( "RESOURCE_ID" VARCHAR2(6 BYTE) NOT NULL ENABLE,
"STUDY_ID" VARCHAR2(2 BYTE) NOT NULL ENABLE,
"ORDER_NO" NUMBER NOT NULL ENABLE,
"CURRENTLY_ACTIVE" VARCHAR2(1 BYTE) NOT NULL ENABLE,
"SHOW_SCHEDULE" VARCHAR2(1 BYTE) NOT NULL ENABLE,
PRIMARY KEY ("RESOURCE_ID", "STUDY_ID")
);
The following query is View Object (VO) in our JDev app. The query itself works when tested in BOTH 12c and 19c databases:
WITH union_data AS (
SELECT resource_id, study_id, change_time, change_val
FROM sch_availability
UNPIVOT ( change_time
FOR change_val IN ( working_start_time AS 1
, working_end_time AS -1)
)
UNION ALL
SELECT resource_id, study_id, change_time, change_val
FROM sch_task
UNPIVOT ( change_time
FOR change_val IN ( task_start_time AS 1
, task_end_time AS -1)
)
WHERE task_status_id='AS'
)
, got_change_sum AS
(
SELECT resource_id,study_id
, change_time
, SUM (SUM(change_val)) OVER ( PARTITION BY resource_id, study_id
ORDER BY change_time
) AS change_sum
, LEAD (change_time) OVER ( PARTITION BY resource_id, study_id
ORDER BY change_time
) - change_time AS duration
FROM union_data
GROUP BY resource_id, study_id, change_time
)
SELECT r.resource_name
, g.resource_id, g.study_id
, g.change_time AS avial_start
, g.change_time + g.duration AS avail_end
, TO_CHAR (g.change_time, 'fmDay') AS available_day
, trunc(g.change_time) AS available_date
, to_char(g.change_time,'HH:MI AM') AS start_time
, to_char(g.change_time + g.duration,'HH:MI AM') AS end_time
, g.duration * 24 AS available_hours
FROM got_change_sum g, sch_resource r, sch_resource_study rs
WHERE g.change_sum = 1 and g.resource_id=rs.resource_id
and g.study_id=rs.study_id
and rs.resource_id=r.resource_id
and trunc(g.change_time)>=sysdate-1
order by available_date,resource_name,start_time;
However in runtime, Oracle ADF wraps the above query like this:
SELECT count(1) FROM (SELECT * FROM (
WITH union_data AS (
SELECT resource_id, study_id, change_time, change_val
FROM sch_availability
UNPIVOT ( change_time
FOR change_val IN ( working_start_time AS 1
, working_end_time AS -1)
)
UNION ALL
SELECT resource_id, study_id, change_time, change_val
FROM sch_task
UNPIVOT ( change_time
FOR change_val IN ( task_start_time AS 1
, task_end_time AS -1)
)
WHERE task_status_id='AS'
)
, got_change_sum AS
(
SELECT resource_id,study_id
, change_time
, SUM (SUM(change_val)) OVER ( PARTITION BY resource_id, study_id
ORDER BY change_time
) AS change_sum
, LEAD (change_time) OVER ( PARTITION BY resource_id, study_id
ORDER BY change_time
) - change_time AS duration
FROM union_data
GROUP BY resource_id, study_id, change_time
)
SELECT r.resource_name
, g.resource_id, g.study_id
, g.change_time AS avial_start
, g.change_time + g.duration AS avail_end
, TO_CHAR (g.change_time, 'fmDay') AS available_day
, trunc(g.change_time) AS available_date
, to_char(g.change_time,'HH:MI AM') AS start_time
, to_char(g.change_time + g.duration,'HH:MI AM') AS end_time
, g.duration * 24 AS available_hours
FROM got_change_sum g, sch_resource r, sch_resource_study rs
WHERE g.change_sum = 1 and g.resource_id=rs.resource_id
and g.study_id=rs.study_id
and rs.resource_id=r.resource_id
and trunc(g.change_time)>=sysdate-1
order by available_date,resource_name,start_time) QRLST where STUDY_ID='68');
** I hardcoded the passing parameter STUDY_ID**
Now the above query executes fine (tested via SQL developer also) in Oracle 12c but fails with the above error in 19c (pluggable)
I don't even know where to start to debug this? Any help is appreciated.