We're experiencing this when testing upgrade from Ora 12.1 to 19.
What's wrong with the code?
This looks like BUG 27653957 which appeared in 12.2. There's currently no fix for it.
Luckily the workaround is simple: remove the constructor function from the object type!
CREATE OR REPLACE TYPE xx_note_typ force AS OBJECT
(
note_id NUMBER,
note_scope NUMBER,
note_value VARCHAR2(4000),
note_date VARCHAR2(100)
) ;
/
CREATE OR REPLACE TYPE xx_note_arr_typ FORCE AS TABLE OF xx_note_typ;
/
create table xx_CUSTOMER_NOTE_ALL
(
id NUMBER not null,
agent_id NUMBER,
debtor_id VARCHAR2(10),
serving_id NUMBER,
note_value VARCHAR2(4000),
note_scope NUMBER,
reg_date TIMESTAMP(6) WITH TIME ZONE not null,
system_id NUMBER not null,
ts TIMESTAMP(6) WITH TIME ZONE not null
) ;
DECLARE
l_result xx_note_arr_typ;
BEGIN
SELECT xx_note_typ(note_id => NULL, note_scope => NULL, note_value => NULL, note_date => NULL)
BULK COLLECT
INTO l_result
FROM xx_CUSTOMER_NOTE_ALL icn
WHERE (icn.note_scope = 0 AND icn.serving_id = 42)
OR (icn.note_scope = 1 AND icn.debtor_id = '42');
END;
/
PL/SQL procedure successfully completed.