Home » SQL & PL/SQL » SQL & PL/SQL » is it possible to use collect function in 9i
is it possible to use collect function in 9i [message #290993] Wed, 02 January 2008 09:39 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi all,

SQL> select banner from v$version ;

BANNER
-----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

In 10g we can do the following:

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
SELECT deptno
, CAST(COLLECT(ename) AS varchar2_ntt) AS emps
FROM emp
GROUP BY
deptno;
o/p:

DEPTNO EMPS
---------- ---------------------------------------------------------------------
10 VARCHAR2_NTT('CLARK', 'KING')
20 VARCHAR2_NTT('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
30 VARCHAR2_NTT('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
40 VARCHAR2_NTT('MILLER')

IS it possible to do the same thing as above in 9i bcz when i am trying to do same as above in 9i getting an error

In 9i :

SQL> SELECT deptno,CAST(COLLECT(empname) AS varchar2_ntt) AS emps
2 FROM emp
3 GROUP BY
4 deptno;
SELECT deptno,CAST(COLLECT(empname) AS varchar2_ntt) AS emps
*
ERROR at line 1:
ORA-00904: "COLLECT": invalid identifier


Please help.

Thanks in advance.

Re: is it possible to use collect function in 9i [message #290995 is a reply to message #290993] Wed, 02 January 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
9.2 is 9i, do you mean 9.0?
In this case, if the same syntax gives you an error this is because it is not supported in this version.
I suggest you to search for COLLECT in your 9.0 documentation and in 9.2 new features book to see if it was not introduced in this version.

Regards
Michel

[Updated on: Wed, 02 January 2008 10:15]

Report message to a moderator

Re: is it possible to use collect function in 9i [message #290996 is a reply to message #290993] Wed, 02 January 2008 10:22 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks Michel,

it was not intraduced in the earlier versions(9i).
But i want to get the same type of reasults as we get the collect function in 10g.
Please let me know how to achieve this.


Thanks
Re: is it possible to use collect function in 9i [message #290997 is a reply to message #290996] Wed, 02 January 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry as you mixed what comes fro m10g and what comes from 9i I thought your succeed query was from 9i.

COLLECT in SQL was introduced in 10g.

Regards
Michel
Re: is it possible to use collect function in 9i [message #290998 is a reply to message #290993] Wed, 02 January 2008 10:39 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Yes Michel,

My database is 9i, But i want the output in the same way as we get in the 10g by using collect function.

i.e., when i quired from the emp table to 9i database i should get the result as below.

DEPTNO EMPS
---------- ---------------------------------------------------------------------
10 VARCHAR2_NTT('CLARK', 'KING')
20 VARCHAR2_NTT('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
30 VARCHAR2_NTT('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
40 VARCHAR2_NTT('MILLER')
Re: is it possible to use collect function in 9i [message #291000 is a reply to message #290998] Wed, 02 January 2008 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use stragg, concat_all or any other way you'll find in "pivot" topic here.

Regards
Michel
Re: is it possible to use collect function in 9i [message #291001 is a reply to message #290998] Wed, 02 January 2008 11:02 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check this

By
Vamsi
Re: is it possible to use collect function in 9i [message #291003 is a reply to message #290993] Wed, 02 January 2008 11:09 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
But vamsi,

in that way i can get a string , but not the result in sql objects.
My requirement is that by using the 9i database i want to select the data into an sql object , exactly similar to COLLECTION function in 10g.

Thanks,

Re: is it possible to use collect function in 9i [message #291005 is a reply to message #291003] Wed, 02 January 2008 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create type, create table and insert statements.

Regards
Michel
Re: is it possible to use collect function in 9i [message #291028 is a reply to message #291003] Wed, 02 January 2008 15:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Here are three different methods. The first method is very specific to the situation, the second method is more generic, and the third method is totally generic.

-- method specific to situation:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_emps
  2    (p_deptno IN emp.deptno%TYPE)
  3    RETURN varchar2_ntt
  4  AS
  5    v_emps varchar2_ntt := varchar2_ntt();
  6  BEGIN
  7    FOR r IN
  8  	 (SELECT ename
  9  	  FROM	 emp
 10  	  WHERE  deptno = p_deptno
 11  	  ORDER  BY ename)
 12    LOOP
 13  	 v_emps.EXTEND;
 14  	 v_emps(v_emps.COUNT) := r.ename;
 15    END LOOP;
 16    RETURN v_emps;
 17  END get_emps;
 18  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> COLUMN emps FORMAT A70
SCOTT@orcl_11g> SELECT deptno,
  2  	    CAST(get_emps(deptno) AS varchar2_ntt) AS emps
  3  FROM   (SELECT DISTINCT deptno
  4  	     FROM   emp
  5  	     ORDER  BY deptno)
  6  /

    DEPTNO EMPS
---------- ----------------------------------------------------------------------
        10 VARCHAR2_NTT('CLARK', 'KING', 'MILLER')
        20 VARCHAR2_NTT('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
        30 VARCHAR2_NTT('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')


-- more generic method combining two other generic methods
-- to convert to string, then collection:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION my_collect
  2    (p_string IN VARCHAR2,
  3  	p_delim  IN VARCHAR2 DEFAULT ',')
  4    RETURN varchar2_ntt
  5  AS
  6    v_string     LONG	 := p_string || p_delim;
  7    v_data	    varchar2_ntt := varchar2_ntt();
  8    v_pos	    NUMBER;
  9  BEGIN
 10    LOOP
 11  	 EXIT WHEN v_string IS NULL;
 12  	 v_pos := INSTR (v_string, p_delim);
 13  	 v_data.EXTEND;
 14  	 v_data(v_data.COUNT) := LTRIM (RTRIM (SUBSTR (v_string, 1, v_pos - 1)));
 15  	 v_string := SUBSTR (v_string, v_pos + LENGTH (p_delim));
 16    END LOOP;
 17    RETURN v_data;
 18  END my_collect;
 19  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT deptno, CAST (my_collect (enames) AS varchar2_ntt) AS emps
  2  FROM   (SELECT  deptno, MAX (LTRIM (SYS_CONNECT_BY_PATH (ename, ','), ',')) AS enames
  3  	     FROM    (SELECT deptno, ename,
  4  			     ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS rn
  5  		      FROM   emp)
  6  	     GROUP   BY deptno
  7  	     START   WITH rn = 1
  8  	     CONNECT BY PRIOR rn = rn - 1 AND deptno = PRIOR deptno)
  9  /

    DEPTNO EMPS
---------- ----------------------------------------------------------------------
        30 VARCHAR2_NTT('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')
        20 VARCHAR2_NTT('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
        10 VARCHAR2_NTT('CLARK', 'KING', 'MILLER')


-- totally generic method using stragg or other aggregate function of your choice:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION my_collect
  2    (p_string IN VARCHAR2,
  3  	p_delim  IN VARCHAR2 DEFAULT ',')
  4    RETURN varchar2_ntt
  5  AS
  6    v_string     LONG	 := p_string || p_delim;
  7    v_data	    varchar2_ntt := varchar2_ntt();
  8    v_pos	    NUMBER;
  9  BEGIN
 10    LOOP
 11  	 EXIT WHEN v_string IS NULL;
 12  	 v_pos := INSTR (v_string, p_delim);
 13  	 v_data.EXTEND;
 14  	 v_data(v_data.COUNT) := LTRIM (RTRIM (SUBSTR (v_string, 1, v_pos - 1)));
 15  	 v_string := SUBSTR (v_string, v_pos + LENGTH (p_delim));
 16    END LOOP;
 17    RETURN v_data;
 18  END my_collect;
 19  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT deptno, CAST (my_collect (enames) AS varchar2_ntt) AS emps
  2  FROM   (SELECT deptno, stragg (ename) AS enames
  3  	     FROM   emp
  4  	     GROUP  BY deptno)
  5  /

    DEPTNO EMPS
---------- ----------------------------------------------------------------------
        10 VARCHAR2_NTT('CLARK', 'KING', 'MILLER')
        20 VARCHAR2_NTT('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
        30 VARCHAR2_NTT('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')

SCOTT@orcl_11g> 

Re: is it possible to use collect function in 9i [message #291033 is a reply to message #291003] Wed, 02 January 2008 16:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
I think it is time for some more caffeine. I seem to be thinking slowly. Here is the simplest method (at least I think so, unless I am still missing the forest for the tress and somebody comes up with something better). It assumes, as did the other methods, that you only have the emp table and may not necessarily have a dept table.


SCOTT@orcl_11g> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
  2  /

Type created.
SCOTT@orcl_11g> SELECT d.deptno,
  2  	    CAST
  3  	      (MULTISET
  4  		(SELECT e.ename
  5  		 FROM	emp e
  6  		 WHERE	e.deptno = d.deptno
  7  		 ORDER	BY e.ename)
  8  		AS varchar2_ntt)
  9  	      AS emps
 10  FROM   (SELECT DISTINCT deptno FROM emp) d
 11  ORDER  BY d.deptno
 12  /

    DEPTNO EMPS
---------- ----------------------------------------------------------------------
        10 VARCHAR2_NTT('CLARK', 'KING', 'MILLER')
        20 VARCHAR2_NTT('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
        30 VARCHAR2_NTT('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')

SCOTT@orcl_11g> 

[Updated on: Wed, 02 January 2008 16:15]

Report message to a moderator

Re: is it possible to use collect function in 9i [message #291172 is a reply to message #291033] Thu, 03 January 2008 04:45 Go to previous message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thank You very much Barbara,
superb explanation.



Thanks
Previous Topic: how to convert string to date
Next Topic: Oracle external tables
Goto Forum:
  


Current Time: Tue Dec 06 14:04:49 CST 2016

Total time taken to generate the page: 0.09088 seconds