ok here is my procedure based on the site you posted. Type ONEDNUMBERARRAY is type ONDNUMBERARRAY as table of number.
PROCEDURE GetTabAccess(ATabID IN NUMBER,
DATA_CURSOR OUT Types.CursorType)
IS
BEGIN
OPEN DATA_CURSOR FOR
select id,
cast ( multiset( select user_id
from caw_tabsaccess
where caw_tabsaccess.tab_id = caw_tabs.id and caw_tabsaccess.user_id IS NOT null
group by user_id ) as ONEDNUMBERARRAY ) USER_ID
from caw_tabs;
END GetTabAccess;
which compiles and seems to work but the script output is
Perhaps a user-defined string aggregate is more helpful here.
Code:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> CREATE TABLE tabs (
2 id NUMBER,
3 name VARCHAR2 (10));
Table created.
SQL> INSERT INTO tabs VALUES (1, 'myTab');
1 row created.
SQL> INSERT INTO tabs VALUES (2, 'yourTab');
1 row created.
SQL> CREATE TABLE tabsaccess (
2 tab_id NUMBER,
3 user_id NUMBER,
4 group_id NUMBER);
Table created.
SQL> INSERT INTO tabsaccess VALUES (1, 1700, NULL);
1 row created.
SQL> INSERT INTO tabsaccess VALUES (1, 8, NULL);
1 row created.
SQL> INSERT INTO tabsaccess VALUES (1, NULL, 5000);
1 row created.
SQL> INSERT INTO tabsaccess VALUES (1, NULL, 5001);
1 row created.
SQL> CREATE OR REPLACE TYPE string_ot AS OBJECT (
2 total VARCHAR2 (4000),
3
4 STATIC FUNCTION odciaggregateinitialize (
5 sctx IN OUT string_ot)
6 RETURN NUMBER,
7
8 MEMBER FUNCTION odciaggregateiterate (
9 SELF IN OUT string_ot,
10 VALUE IN VARCHAR2)
11 RETURN NUMBER,
12
13 MEMBER FUNCTION odciaggregateterminate (
14 SELF IN string_ot,
15 returnvalue OUT VARCHAR2,
16 flags IN NUMBER)
17 RETURN NUMBER,
18
19 MEMBER FUNCTION odciaggregatemerge (
20 SELF IN OUT string_ot,
21 ctx2 IN string_ot)
22 RETURN NUMBER
23 );
24 /
Type created.
SQL> CREATE OR REPLACE TYPE BODY string_ot
2 AS
3 STATIC FUNCTION odciaggregateinitialize (
4 sctx IN OUT string_ot)
5 RETURN NUMBER
6 IS
7 BEGIN
8 sctx := string_ot (NULL);
9 RETURN odciconst.success;
10 END;
11
12 MEMBER FUNCTION odciaggregateiterate (
13 SELF IN OUT string_ot,
14 VALUE IN VARCHAR2)
15 RETURN NUMBER
16 IS
17 BEGIN
18 SELF.total := SELF.total || ', ' || VALUE;
19 RETURN odciconst.success;
20 END;
21
22 MEMBER FUNCTION odciaggregateterminate (
23 SELF IN string_ot,
24 returnvalue OUT VARCHAR2,
25 flags IN NUMBER)
26 RETURN NUMBER
27 IS
28 BEGIN
29 returnvalue := SUBSTR (SELF.total, 3);
30 RETURN odciconst.success;
31 END;
32
33 MEMBER FUNCTION odciaggregatemerge (
34 SELF IN OUT string_ot,
35 ctx2 IN string_ot)
36 RETURN NUMBER
37 IS
38 BEGIN
39 SELF.total := SELF.total || ctx2.total;
40 RETURN odciconst.success;
41 END;
42 END;
43 /
Type body created.
SQL> CREATE OR REPLACE FUNCTION string_agg (
2 input IN VARCHAR2)
3 RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
4 AGGREGATE USING string_ot;
5 /
Function created.
SQL> COLUMN user_id FORMAT A20
SQL> COLUMN group_id FORMAT A20
SQL> SELECT id, name,
2 (SELECT '[' || string_agg (user_id) || ']'
3 FROM tabsaccess
4 WHERE user_id IS NOT NULL
5 AND tab_id = id) user_id,
6 (SELECT '[' || string_agg (group_id) || ']'
7 FROM tabsaccess
8 WHERE group_id IS NOT NULL
9 AND tab_id = id) group_id
10 FROM tabs
11 WHERE id = 1;
ID NAME USER_ID GROUP_ID
---------- ---------- -------------------- --------------------
1 myTab [1700, 8] [5000, 5001]
SQL>