Struggling with a hierarchical query and table of types. Getting ORA-00600: internal error code, arguments: [koxsi2sz1] and [rworupo.1] errors with 12.1.0.2 version.
WITH Factorial (operand,total_so_far,foo) AS (
SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1), foo
FROM Factorial
WHERE operand > 1)
SEARCH breadth FIRST BY operand SET order1
SELECT * FROM Factorial
;
Breadth first and everything is fine. But changing to depth first the problems occur.
WITH Factorial (operand,total_so_far,foo) AS (
SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1), foo
FROM Factorial
WHERE operand > 1)
SEARCH depth FIRST BY operand SET order1
SELECT * FROM Factorial
;
With 18.4 version the error message is more tolerable ORA-00932: inconsistent datatypes: expected UDT got SYS.ODCINUMBERLIST. But no success with the results.
Getting around the problem is to create a NumberListWrapper wrapper type for the array type.
The workaround seems to work at least with 12.2 and 18.4 versions.
CREATE OR REPLACE TYPE NumberListWrapper AS OBJECT (
numbertable sys.odciNumberlist,
MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC
);
/
CREATE OR REPLACE TYPE BODY NumberListWrapper AS
MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN 1;
END;
END;
/
WITH Factorial (operand,total_so_far,foo) AS (
SELECT 5 operand, 5 total_so_far, NumberListWrapper(sys.odcinumberlist(1,2)) foo FROM dual -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1), foo
FROM Factorial
WHERE operand > 1)
SEARCH DEPTH FIRST BY operand SET order1
SELECT * FROM Factorial
;