No question at this time
DBA Top 10
1 M. Cadot 17000
2 A. Kavsek 14300
3 B. Vroman 10300
4 M. Hidayathullah ... 8400
5 T. Boles 4900
6 P. Wisse 4400
7 J. Schnackenberg 3500
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 800
9 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48097
Total active users1702
Act. users last 24h1
Act. users last hour0
Registered user hits last week237
Registered user hits last month1339
Go up

Please help - how to replace wmsys.wm_concat in 12c
Next thread: Oracle streams multiway configuration
Prev thread: golden gate recovery using archive log

Message Score Author Date
Hi all, after upgrading to 12c customer complai...... Stephan Klimke Sep 24, 2015, 10:47
>>> <i> Using LISTAGG doesn't solve the customer...... Michel Cadot Sep 24, 2015, 11:05
Hi Michel, sorry - to less Details. Customer to...... Stephan Klimke Sep 24, 2015, 11:53
Have you checked the presence of the wm_concat fun...... Glenn Van Cauter Sep 24, 2015, 11:53
Hi Glenn, thank you. I did find ./owmr1120.plb ...... Stephan Klimke Sep 24, 2015, 11:56
Hi, WMSYS.WM_CONCAT Should Not Be Used For Cust...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Richard Tesar Sep 24, 2015, 13:21
Thank you Richard, I will try this out. Custome...... Stephan Klimke Sep 24, 2015, 13:31
Now we see that you are able to copy and paste T...... Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts Michel Cadot Sep 24, 2015, 13:33
Thank you Michel - understood... Stephan Klimke Sep 24, 2015, 13:34
Sorry, but you are disgusting. I did not read that...... Richard Tesar Sep 24, 2015, 14:06
I doubt this is true. This is a strict copy and...... Michel Cadot Sep 24, 2015, 15:25
Hi Stephan, > Is anybody aware of any alternati...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek Sep 25, 2015, 12:50
I did it as I described it before, in previous pos...... Richard Tesar Sep 29, 2015, 12:32
Yes, you did it, you copied and pasted someone e...... Michel Cadot Sep 29, 2015, 14:12
Thank you all. The original question is answered. ...... Geert De Paep Sep 29, 2015, 14:53

Follow up by mail Click here


Subject: Please help - how to replace wmsys.wm_concat in 12c
Author: Stephan Klimke, Germany
Date: Sep 24, 2015, 10:47, 815 days ago
Os info: Oracle EE 12c
Oracle info: 12.1.0.2.4
Message: Hi all,

after upgrading to 12c customer complain about non existing wmsys.wm_concat function. I know that this shouldn't use and I know that this is an internal function.

Using LISTAGG doesn't solve the customers need.

Is anybody aware of any alternative solution???

Regards
Stephan
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Michel Cadot, France
Date: Sep 24, 2015, 11:05, 815 days ago
Message:
>>> Using LISTAGG doesn't solve the customers need.

Which help in no way to know why it does not solve the problem and what alternative could solve it.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Stephan Klimke, Germany
Date: Sep 24, 2015, 11:53, 815 days ago
Message: Hi Michel,

sorry - to less Details. Customer told us the main issue with LISTAGG is that it Limits to 4000 characters.

Regards
Stephan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Glenn Van Cauter, Belgium
Date: Sep 24, 2015, 11:53, 815 days ago
Message: Have you checked the presence of the wm_concat function in the database (dba_objects)?

If not present you can find the creation scripts in $ORACLE_HOME/rdbms/admin for wmsys you'll have to look at scripts starting with owm...

A quick find in files (find . | xargs grep 'wm_concat ' -sl) brings you to owmaggrs.plb

For the full wmsys schema: owminst.plb
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Stephan Klimke, Germany
Date: Sep 24, 2015, 11:56, 815 days ago
Message: Hi Glenn,

thank you. I did find ./owmr1120.plb as the Container for.

I will try it out.

Regards
Stephan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Richard Tesar, Czech Republic
Date: Sep 24, 2015, 13:21, 815 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi,

WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (Doc ID 1336219.1)

So that you can rewrite it. Some think like this:


[Content removed due to copyright violation. See https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402]


br,
Richard
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Stephan Klimke, Germany
Date: Sep 24, 2015, 13:31, 815 days ago
Message: Thank you Richard, I will try this out.

Customer knows that he is the bad guy using this :-)
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Michel Cadot, France
Date: Sep 24, 2015, 13:33, 815 days ago
Score:   Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts
Message:
Now we see that you are able to copy and paste T. Kyte's function and post it as it were from you instead of posting the url from which you took it:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

This is disgusting.

In addition, but maybe you are not able to understand it, this function has the same limit than LISTAGG: 4000 bytes.

Here's a function that I derived from T. Kyte's one and support CLOB and so no limitation.
Of course, CLOB has far slower than VARCHAR2 and so you should use it only when you know you will exceed the VARCHAR2 limit.

create or replace type stragg_type4 as object 

(
result CLOB,

static function ODCIAggregateInitialize (sctx IN OUT stragg_type4)
return number,

member function ODCIAggregateIterate (self IN OUT stragg_type4,
value IN varchar2)
return number,

member function ODCIAggregateTerminate (self IN stragg_type4,
returnValue OUT CLOB,
flags IN number)
return number,

member function ODCIAggregateMerge (self IN OUT stragg_type4,
ctx2 IN stragg_type4)
return number
);
/

create or replace type body stragg_type4
is

static function ODCIAggregateInitialize (sctx IN OUT stragg_type4)
return number
is
begin
sctx := stragg_type4 (null);
dbms_lob.createtemporary (lob_loc => sctx.result,
cache => TRUE,
dur => dbms_lob.call);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate (self IN OUT stragg_type4,
value IN varchar2)
return number
is
begin
self.result := self.result || ',' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate (self IN stragg_type4,
returnValue OUT CLOB,
flags IN number)
return number
is
begin
returnValue := ltrim (self.result, ',');
return ODCIConst.Success;
end;

member function ODCIAggregateMerge (self IN OUT stragg_type4,
ctx2 IN stragg_type4)
return number
is
begin
self.result := self.result || ctx2.result;
return ODCIConst.Success;
end;

end;
/
sho err

CREATE or replace FUNCTION stragg4 (input varchar2)
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING stragg_type4;
/


Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Stephan Klimke, Germany
Date: Sep 24, 2015, 13:34, 815 days ago
Message: Thank you Michel - understood
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Richard Tesar, Czech Republic
Date: Sep 24, 2015, 14:06, 815 days ago
Message: Sorry, but you are disgusting. I did not read that Kyte's article... It is from our SVN.
I do not really like bullies like you.

br,
Richard
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Michel Cadot, France
Date: Sep 24, 2015, 15:25, 815 days ago
Message:
I doubt this is true.
This is a strict copy and paste of T. Kyte's stragg function, everything are identical, including the number of spaces and indentation, you just change the name of the final function name from stragg to wm_concat (note your type is named "string_agg_type", if it was "wm_concat_type" I had some doubts).
In addition, the link I gave is inside the MOS note you pointed OP to.

So 2 options:
1/ You got the code from your SVN
2/ You read the MOS note you gave, you saw T. Kyte's article and function, you copied and pasted it
... what is the more likely?

Note if you only gave the MOS note (with or without T. Kyte's link) understood that it has the same limit than LISTAGG but mentioned it can be improved to support CLOB, I'd say "wow this is a valuable answer!".
There, it is just a blatant plagiarism that does even not address the problem.
Enjoy the points you stole!

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Ales Kavsek, Slovenia
Date: Sep 25, 2015, 12:50, 814 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Stephan,

> Is anybody aware of any alternative solution???

Check this blog:

Managing overflows in LISTAGG
https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg

Since you're on 12c you can directly use MATCH_RECOGNIZE + LISTAGG trick mentioned in the first part, for version 10g/11g, I'm afraid the workaround includes more freaky MODAL clause.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Richard Tesar, Czech Republic
Date: Sep 29, 2015, 12:32, 810 days ago
Message: I did it as I described it before, in previous post. I did not do anything as you describing it. It is just your mind construction. Now I see that you are weak-minded and poor person.
R.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Michel Cadot, France
Date: Sep 29, 2015, 14:12, 810 days ago
Message:
Yes, you did it, you copied and pasted someone else code without telling that it is not from you.
Correct or not?

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Please help - how to replace wmsys.wm_concat in 12c
Author: Geert De Paep, Belgium
Date: Sep 29, 2015, 14:53, 810 days ago
Message: Thank you all. The original question is answered. Please let's close this forum thread.
Geert De Paep
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here