Hi Priyan,

May be running the query in a lower version.
COLLECT Operator is available in ORACLE 10g Version.

Check Your Version

SELECT * FROM V$VERSION;

Try the below in ORACLE-10g

SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

CREATE OR REPLACE TYPE Cnvt_Type IS TABLE OF VARCHAR2(100);

CREATE TABLE TEST_VALUE(ID NUMBER,NAME VARCHAR(100));

INSERT INTO TEST_VALUE VALUES(1,'TEST');
INSERT INTO TEST_VALUE VALUES(2,'TEST1');
INSERT INTO TEST_VALUE VALUES(3,'TEST2');
INSERT INTO TEST_VALUE VALUES(4,'TEST3');

SELECT CAST(COLLECT(NAME) AS Cnvt_Type)AS Records FROM TEST_VALUE;

Records
--------
TEST
TEST1
TEST2
TEST3

Prasath,

By using Collection Method as per your idea, it will return the result in different rows but using the GROUP_CONCAT() function in MYSQL will return the record set in a single row with comma seperator.

__________________
-Murali..