'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)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
