'Why can I cast a multiset and not a collection as a odcivarchar2list in a query

This query works

WITH
    FUNCTION f (i_tab IN SYS.odcivarchar2list)
        RETURN INTEGER
    IS
    BEGIN
        RETURN 22;
    END;

    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (SELECT s1.a,
                CAST (MULTISET (SELECT s2.b
                                  FROM s s2
                                 WHERE s2.a = s1.a) AS SYS.odcivarchar2list)
           FROM s s1)
SELECT *
  FROM t;

But this query doesn't.

WITH
    FUNCTION f (i_tab IN SYS.odcivarchar2list)
        RETURN INTEGER
    IS
    BEGIN
        RETURN 2;
    END;

    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, CAST(COLLECT (s.b) as sys.odcivarchar2list)
             FROM s
         GROUP BY s.a)
SELECT *
  FROM t

ORA-00932: inconsistent datatypes: expected - got ANYDATA

Why can't I cast a collection as sys.odcivarchar2list. The problem is the cast function. Because the following query works.

WITH
    FUNCTION f (i_tab IN SYS.odcivarchar2list)
        RETURN INTEGER
    IS
    BEGIN
        RETURN 2;
    END;

    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a)
SELECT *
  FROM t;

I want to cast the collection in order to use function f. Like that:

see in code (stackoverflow message: It looks like your post is mostly code; please add some more details)

code



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source