OraFAQ Forum: SQL & PL/SQL » WM

Home » SQL & PL/SQL » SQL & PL/SQL » WM_CONCAT NOT WORKING (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

WM_CONCAT NOT WORKING [message #516422]

Mon, 18 July 2011 01:24

queeny
Messages: 20
Registered: July 2011
Location: india

Junior Member

Hi,
Can anybody help me out regarding the string concatenation.

When iam using wm_concat function in a query in our DB it gave me expected output but when i tried the same thing in another DB of ours it gave me error saying ORA-00904: "WM_CONCAT" : invalid identifier

Please help me why am I getting this error and please let me know how can I resolve this. or is there any way to concatenate the ouput of a query (seperated by ',')when it is returing unexpected no. of rows.

Report message to a moderator

Re: WM_CONCAT NOT WORKING [message #516427 is a reply to message #516422]

Mon, 18 July 2011 01:42

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

WM_CONCAT exists in "our" database, but doesn't in "another" (or, if it exists, you don't have privileges to use it).

Report message to a moderator

Re: WM_CONCAT NOT WORKING [message #516428 is a reply to message #516427]

Mon, 18 July 2011 01:44

queeny
Messages: 20
Registered: July 2011
Location: india

Junior Member

How can I create this WM_CONCAT function in the DB ?

Report message to a moderator

Re: WM_CONCAT NOT WORKING [message #516429 is a reply to message #516428]

Mon, 18 July 2011 01:48

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you post both database versions? Run

select * from v$version

and post the results here.

[EDIT] Perhaps you'll be interested in reading String Aggregation Techniques. Also, take a look at stragg by Tom Kyte.

[Updated on: Mon, 18 July 2011 01:51]

Report message to a moderator

Re: WM_CONCAT NOT WORKING [message #516430 is a reply to message #516422]

Mon, 18 July 2011 01:52

flyboy
Messages: 1903
Registered: November 2006

Senior Member

sowjanyavalaparlabtcom wrote on Mon, 18 July 2011 08:24

or is there any way to concatenate the ouput of a query (seperated by ',')when it is returing unexpected no. of rows.

Yes, pick any method available in your version from list published here: http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Forget WM_CONCAT, it is undocumented and unsupported.

Report message to a moderator

Re: WM_CONCAT NOT WORKING [message #516431 is a reply to message #516429]

Mon, 18 July 2011 01:54

queeny
Messages: 20
Registered: July 2011
Location: india

Junior Member

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production this is the version of the DB where wm_concat is working
and below is the version details of the DB where it is not working

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

Report message to a moderator

Re: WM_CONCAT NOT WORKING [message #516432 is a reply to message #516422]

Mon, 18 July 2011 02:04

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Just played a little with simple SQL; you could do the same on the working database.

SQL> select owner, object_type
  2  from dba_objects
  3  where object_name = 'WM_CONCAT';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
PUBLIC                         SYNONYM
WMSYS                          FUNCTION

So, WM_CONCAT is part of WMSYS schema. It is possible that it is not installed on the another DB.

But, installing the whole schema just for one function (and so relying on/requiring it) is really weird. The best way would be writing your own function or use SYS_CONNECT_BY_PATH or COLLECT or anything else described in the link I posted before.

Report message to a moderator

Re: WM_CONCAT NOT WORKING [message #516433 is a reply to message #516431]

Mon, 18 July 2011 02:32

queeny
Messages: 20
Registered: July 2011
Location: india

Junior Member

Okay.. Tried creating user defined function and got the desired output. Thanks a lot flyboy .. Smile

Report message to a moderator

Previous Topic:	Function not calculating(2 Merged)
Next Topic:	Query help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 07 00:46:23 CDT 2025