In the test below I'm trying to cast a subquery to a package defined type vs. a database defined type. As it stands I can't get it to work. I end up getting an ORA-00902: invalid datatype error.
Could you explain why this is and whether or not there is a work around for this (besides creating the type in the database)? I have some limitations as a developer to where I'm not allowed to create database level types.
Setup CREATE OR REPLACE TYPE t_string_ary2 AS TABLE OF VARCHAR2(200);
/
CREATE OR REPLACE PACKAGE FOO_PKG
AS
TYPE t_string_ary IS TABLE OF VARCHAR2(200);
--Takes a collection of varchars and returns it as a
-- single string.
FUNCTION implode ( p_ary t_string_ary, p_delim VARCHAR2 := ', ')
RETURN VARCHAR2;
FUNCTION implode2 ( p_ary t_string_ary2, p_delim VARCHAR2 := ', ')
RETURN VARCHAR2;
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY FOO_PKG
AS
FUNCTION implode
( p_ary t_string_ary, p_delim VARCHAR2 := ', ')
RETURN VARCHAR2
IS
l_rtn_str VARCHAR2(32000);
l_element VARCHAR2(80);
l_index NUMBER;
l_length PLS_INTEGER := 0;
l_del_len PLS_INTEGER := LENGTH(p_delim);
BEGIN
IF p_ary.FIRST IS NOT NULL THEN
FOR l_index IN p_ary.FIRST .. p_ary.LAST
LOOP
l_element := p_ary(l_index);
l_length := l_length + l_del_len + LENGTH(NVL(l_element, ' '));
IF l_length < 32000 THEN
l_rtn_str := l_rtn_str || p_delim || l_element;
ELSE
EXIT;
END IF;
END LOOP;
END IF;
IF ( l_rtn_str IS NOT NULL) THEN
l_rtn_str := SUBSTR(l_rtn_str, LENGTH(p_delim)+1);
END IF;
RETURN l_rtn_str;
END;
FUNCTION implode2
( p_ary t_string_ary2, p_delim VARCHAR2 := ', ')
RETURN VARCHAR2
IS
l_rtn_str VARCHAR2(32000);
l_element VARCHAR2(80);
l_index NUMBER;
l_length PLS_INTEGER := 0;
l_del_len PLS_INTEGER := LENGTH(p_delim);
BEGIN
IF p_ary.FIRST IS NOT NULL THEN
FOR l_index IN p_ary.FIRST .. p_ary.LAST
LOOP
l_element := p_ary(l_index);
l_length := l_length + l_del_len + LENGTH(NVL(l_element, ' '));
IF l_length < 32000 THEN
l_rtn_str := l_rtn_str || p_delim || l_element;
ELSE
EXIT;
END IF;
END LOOP;
END IF;
IF ( l_rtn_str IS NOT NULL) THEN
l_rtn_str := SUBSTR(l_rtn_str, LENGTH(p_delim)+1);
END IF;
RETURN l_rtn_str;
END;
END;
/
show errors
CREATE TABLE dept(
dept_id NUMBER,
name VARCHAR2(50)
);
CREATE TABLE emp(
emp_id NUMBER,
fname VARCHAR2(50),
dept_id NUMBER
);
INSERT INTO dept VALUES (1, 'Office 1');
INSERT INTO dept VALUES (2, 'Office 2');
INSERT INTO dept VALUES (3, 'Office 3');
INSERT INTO emp VALUES (1, 'Joe', 1);
INSERT INTO emp VALUES (2, 'Amy', 1);
INSERT INTO emp VALUES (3, 'Sue', 2);
INSERT INTO emp VALUES (4, 'Allen', 2);
INSERT INTO emp VALUES (5, 'Barbara', 3);
Tests*********** TEST *********** Using database type
SELECT name,
foo_pkg.implode2(
CAST(
MULTISET(
SELECT fname
FROM emp
WHERE dept_id = d.dept_id
) AS t_string_ary2
)
) "Employees"
FROM dept d;
NAME
--------------------------------------------------
Employees
------------------------------------------------------------
Office 1
Joe, Amy
Office 2
Sue, Allen
Office 3
Barbara
*********** TEST *********** Using package type
SELECT name,
foo_pkg.implode(
CAST(
MULTISET(
SELECT fname
FROM emp
WHERE dept_id = d.dept_id
) AS foo_pkg.t_string_ary
)
) "Employees"
FROM dept d;
) AS foo_pkg.t_string_ary
*
ERROR at line 8:
ORA-00902: invalid datatype
*********** TEST *********** PL/SQL Test
DECLARE
l_ary foo_pkg.t_string_ary;
l_index NUMBER;
BEGIN
SELECT fname
BULK COLLECT INTO l_ary
FROM emp;
dbms_output.put_line('Implode - '|| foo_pkg.implode(l_ary));
END;
/
Implode - Joe, Amy, Sue, Allen, Barbara
Thanks,
Marcus