Stack Overflow
Stack Overflow

Questions tagged [wm-concat]

function to aggregate data from a number of rows into a single row in Oracle PL/SQL

The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output as WM_CONCAT is both documented and supported by Oracle.

14 questions
5
votes
6 answers

ORACLE/SQL: wm_concat & order by

I'm using oracle 11 (not sure about the exact version, but since LISTAGG doesn't work, I suppose it's not release 2) through ODBC and crystal reports 2008. Here is the problem I have: Here's a table: TABLE ODB.TASK_CARD_CONTROL …
Christian M
  • 235
  • 4
  • 11
  • 18
5
votes
4 answers

Why does the wm_concat not work here?

I have this query : (SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN (SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',',')))) that returns : But when I do : SELECT wm_concat(object_id) FROM (SELECT OBJECT_ID…
sprocket12
  • 5,368
  • 18
  • 64
  • 133
4
votes
3 answers

Convert WM_CONCAT to Listagg

My DBA is upgrading my oracle db from v10 to v12. I have some old SP's that uses wm_concat and I need to change it to listagg. The problematic code is this: Select registration_id,package_set_id, REPLACE(REPLACE(WM_CONCAT(REPLACE( (case…
Alan Mil
  • 143
  • 1
  • 2
  • 10
4
votes
2 answers

Oracle Function: Replicate wm_concat

I currently am working on a project within Crystal Reports that refuses to use the undocumented function WM_CONCAT, which is allowable within Oracle 10g. Here is the WM_CONCAT header information WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2 To use…
contactmatt
  • 18,116
  • 40
  • 128
  • 186
3
votes
3 answers

Using WMSYS.WM_CONCAT with Oracle XE 10g

When I try to use WMSYS.WM_CONCAT with Oracle XE 10g, I receive a compilation error: ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier. Can anyone verify that this is indeed due to XE lacking this (undocumented) feature? If so, is there anyway to…
Stephen Swensen
  • 22,107
  • 9
  • 81
  • 136
2
votes
1 answer

Define "WM_CONCAT(col)" as "LISTAGG(col, ',')" for Oracle Migration

We are in the process of migrating from Oracle 10g to 18c for our environment databases. To complicate things, not all environments are planned to be migrated at once and so the app must support both for a time. One of the incompatibilities…
Briguy37
  • 8,342
  • 3
  • 33
  • 53
2
votes
2 answers

pl/sql : extract after concatenated?

select wm_concat(COLUMN_NAME) A FROM ALL_TAB_COLUMNS where table_name like 'T_EMPLOYEE'; How to extract the data out from A and assign it to another varchar2 variable?
kmy
  • 55
  • 6
2
votes
1 answer

How to concatenate multiple rows order by sequence in Oracle10g

If I have a data like this: GROUP | SEQUENCE | COMMAND ------------------------------ ONE | 3 | MESSAGE ONE | 1 | ONE | 2 | …
jitd
  • 45
  • 1
  • 10
2
votes
0 answers

Getting SQL Error: ORA-00904: "WM_CONCAT": invalid identifier

Why do i get this error in a clone db from the origins? Getting SQL Error: ORA-00904: "WM_CONCAT": invalid identifier I am using Oracle 11gR2. The original database still run successfully. BANNER …
Liem Do
  • 933
  • 1
  • 7
  • 13
2
votes
2 answers

WM_CONCAT duplicates removal

I have a table like below. colA colB 12345 NHS,CDE,BCD 12345 NHS,ABC,DEF Need to display the data in below format colA colB 12345 NHS,ABC,BCD,CDE,DEF I need generalized solution to remove the duplicate…
Raghava Ch
  • 77
  • 2
  • 10
1
vote
1 answer

Getting ORA-22922 (nonexistent LOB value) or no result at all with wm_concat()

(Using Oracle 11.2) I have a rather complicated SQL with something like wm_concat( distinct abc ) that is expected to return some varchar2(4000) compatible result. It causes ORA-00932: inconsistent datatypes in my select used in some coalesce(…
Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96
0
votes
1 answer

Where to download these three files, "owmctab.plb","owmaggrs.plb","owmaggrb.plb"

I need use WM_CONCAT() function,but the oracle 11g haven't. Some articles say the problem can be fixed through these three files "owmctab.plb,owmaggrs.plb,owmaggrb.plb". So i want to download that files,where i can download? plase get me some…
Curious Peng
  • 27
  • 8
0
votes
0 answers

Changing wm_concat to other solution

I have this code, using wm_concat: SELECT ... wm_concat(distinct pori.inma_symbol ||' '|| pori.inma_nazwa|| '-' || pori.ilosc ||pori.JDMR_BAZOWA_NAZWA) as INDEKS,... FROM ... pord INNER JOIN ... maga ON ... inner join ... pori on ... where…
Kordian Zadrożny
  • 45
  • 7
0
votes
1 answer

How to displaying multiple rows values in a one line of output

i have this query: SELECT table_1.user_code AS user, table_1.charge_code, table_1.unit_code, table_3.roles_code, table_2.apps_code FROM table_1 INNER JOIN table_3 ON table_1.user_code = table_3.Cod_Usr INNER JOIN table_2 ON …
spikeTJ
  • 73
  • 1
  • 13