Custom Oracle clustering function, similar to the wm_concat Function

-------------------------------------------
-- Export file for user boss1214 --
-- Created by user on, 9:34:30 --
-------------------------------------------

Spool str_sum_ SQL .log

Prompt
Prompt creating type str_sum_obj
Prompt =
Prompt
Create or replace type str_sum_obj as object -- the essence of an aggregate function is an object.
(
Sum_string varchar2 (4000 ),
Static function odciaggresponinitialize (v_self in out str_sum_obj)
Return number, -- object initialization

-- Iterative Method of Aggregate functions (this is the most important method)
Member function odciaggregateiterate (self in out str_sum_obj,
Value in varchar2) return number,

-- This method is used only when the query statement runs in parallel. You can aggregate the query results that run in parallel.
Member function odciaggregatemerge (self in out str_sum_obj,
V_next in str_sum_obj)
Return number,

-- Terminate the processing of the aggregate function and return the processing result of the aggregate function.
Member function odciaggresponterminate (self in str_sum_obj,
Return_value out varchar2,
V_flags in number)
Return number

)
/

Prompt
Prompt creating function str_sum
Prompt =
Prompt
Create or replace function str_sum (value varchar2) return varchar2
Parallel_enable aggregate using str_sum_obj;
/

Prompt
Prompt creating type body str_sum_obj
Prompt =
Prompt
Create or replace type body str_sum_obj is
Static function odciaggresponinitialize (v_self in out str_sum_obj)
Return number is
Begin
V_self: = str_sum_obj (null );
Return odciconst. success;
End;

Member function odciaggregateiterate (self in out str_sum_obj,
Value in varchar2) return number is
Begin
Self. sum_string: = self. sum_string | value;
Return odciconst. success;
End odciaggregateiterate;

Member function odciaggregatemerge (self in out str_sum_obj,
V_next in str_sum_obj)
Return number is
Begin
Self. sum_string: = self. sum_string | v_next.sum_string;
Return odciconst. success;
End odciaggregatemerge;

Member function odciaggresponterminate (self in str_sum_obj,
Return_value out varchar2,
V_flags in number)
Return number is
Begin
Return_value: = self. sum_string;
Return odciconst. success;
End odciaggresponterminate;
End;
/

Spool off

 

 

The example is as follows:

Select str_sum (PI. productcode | ',')
From t_productinfos pi
Where pi. producttypecode = '00 ';

In addition, the wm_concat function may return the clob type in some Oracle versions, resulting in program errors.

Therefore

Wm_concat is

Something in wmsys
,
Oracle is used internally. It is not recommended to be used in general programs.

 

 

**************************************** **************************************** *

Today, I am running a process using the wmsys. wm_concat function in the test environment. The logging error is

1	ORA-00932: inconsistent datatypes: expected - got CLOB

However, no error is reported if the process runs normally in the production environment. Google on the Internet, this function is unauthenticated ented and is not officially supported by Oracle. It will be modified as the version changes, but the behavior is not guaranteed to be consistent.

The test environment is

1	Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 compatible string 10.2.0.3.0

Restart and modify compatible string to 10.2.0.5.0. test whether the returned value of wmsys. wm_concat is clob.

Production Environment is

1	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 compatible string 11.2.0.0.0

The final solution is to use to_char for conversion in the outer layer of wm_concat.
PS: wm_concat was used once in the 11g production environment. It is found that the function cannot be used more than twice in the same select clause, so the listparts and regular table are used instead.
It solves the problem. It seems that wmsys. wm_concat should be avoided in the future, which is not convenient for transplantation!