2019-02-01

Errors With Depth First Hierarchical Query and Table Of Types

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
;

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.