ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier (文档 ID 2215183.1)

APPLIES TO:

Oracle Transportation Management Cloud Service - Version 6.4.1 and later
Oracle Transportation Management - Version 6.4.1 and later
Oracle Global Trade Management Cloud Service - Version 6.4.1 and later
Oracle Global Trade Management - Version 6.4.1 and later
Information in this document applies to any platform.

SYMPTOMS

You have an agent which is failing on a Direct SQL in call with error

java.sql.SQLSyntaxErrorException: ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier

 

CAUSE

The database function WM_CONCAT is being used.
 
WM_CONCAT is undocumented and unsupported by Oracle meaning it should not be used in production systems.

This function has been removed from Oracle 12c.

Since version 6.4.1 includes a database update to 12c this function can no longer be used.
 

SOLUTION

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. 

WM_CONCAT is gone in 12c.

Use the LISTAGG function instead, which can produce the same output as WM_CONCAT is both documented and supported by Oracle.

Basic Syntax:

LISTAGG(col_name_to_be_aggregated, ',') WITHIN GROUP (ORDER BY col)

Def LISTAGG http://psoug.org/definition/LISTAGG.htm

 

http://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values

http://dba.stackexchange.com/questions/696/eliminate-duplicates-in-listagg-oracle