Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Let us discussion at wmsys.wm_concat VS ListAgg

Aketi JyuuzouJun 18 2010 — edited Jun 17 2011
This thread is to discussion what is difference between wmsys.wm_concat and ListAgg. B-)

*************************************************************************
difference1 :-)

wmsys.wm_concat allows distinct option.
ListAgg does not allows it.
create table diffT(sortKey,Val) as
select 1,'aa' from dual union all
select 2,'bb' from dual union all
select 3,'aa' from dual union all
select 4,'dd' from dual;

col concatV for a20

select wmsys.wm_concat(distinct Val) as concatV from diffT;

concatV 
--------
aa,bb,dd
*************************************************************************
difference2 :-)

ListAgg allows to decide string concat order.
wmsys.wm_concat does not allows it.
select ListAgg(Val,',')
       within group(order by sortKey desc) as concatV
from diffT;

CONCATV
------------
dd,aa,bb,aa 
*************************************************************************
difference3 :-)

ListAgg allows to decide delimiter.
wmsys.wm_concat does not allows it.
select ListAgg(Val,'***')
       within group(order by sortKey desc) as concatV
from diffT;

CONCATV
-----------------
dd***aa***bb***aa
*************************************************************************
difference4 :-)

wmsys.wm_concat allows to be used OLAP function with order by
ListAgg does not allows it.
ListAgg allows only OLAP function without order by.
select sortKey,wmsys.wm_concat(Val)
               over(order by sortKey) as concatV
  from diffT;

SORTKEY  CONCATV
-------  -----------
      1  aa
      2  aa,bb
      3  aa,bb,aa
      4  aa,bb,aa,dd
*************************************************************************
difference5 :-)

wmsys.wm_concat allows to be used KEEP
ListAgg does not allows it.
select wmsys.wm_concat(Val) 
       Keep(Dense_Rank First order by Val) as concatV 
  from diffT;

CONCATV
-------
aa,aa

Comments

Paul M.

What's your Operating System ?

SDCraig

Windows 7 Enterprise

Paul M.

Didn't you have any problems with the installation ? The situation you  describe is quite strange...

Please execute following commands, and post results :

C:\>lsnrctl stat

C:\>set ORACLE_SID=XE

C:\>sqlplus / as sysdba

Of course you downloaded and installed the correct version (32/64 bit)...

SDCraig

c\> isnrctl stat

"isnrctl" is not recognized as an internal or external command, operable program or batch file".

c\> set oracle_sid = xe

"c\>"

c\> sqlplus / as sysdba

"SQL*Plus:  Release 11.2.0.2.0 Projection on Mon Sep 8 10:26:17 2014

Copyright <c> 1982, 2014, Oracle.  All rights researved

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>"

Paul M.

c\> isnrctl stat

It's lsnrctl, not isnrctl.

SDCraig

c\> lsnrctl stat

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 08-SEP-2014 12:28:18

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CFXXXXXXX.xxxxxxxxxxxx.com)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

Paul M.
Answer

Your listener is not running : is OracleXETNSListener service started ?

Marked as Answer by SDCraig · Sep 27 2020
SDCraig

Ok, that worked.  Thanks.

Quick question:  To get started (importing data and utilizing the oracle text functions - ie, contains, fuzzy, etc.), is the next step to access "get started" from the start button and do all of this through the web GUI?  Can all of this be done through SQL developer?

Or should I create another help ticket for this question?

Paul M.

should I create another help ticket for this question?

Please do.

1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 15 2011
Added on Jun 18 2010
18 comments
34,777 views