【LISTAGG】 ORA-00904: "WM_CONCAT": invalid identifier (Doc
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 18.5.0.0.0 [Release 10.1 to 18]
Information in this document applies to any platform.
GOAL
From Oracle 12.1.0.1 onwards WM_CONCAT function is disabled. We need to use LISTAGG Function.
EXAMPLE:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SELECT WM_CONCAT(DISTINCT TYPE) from v$parameter; WM_CONCAT(DISTINCTTYPE)
SQL>
Connected to:
SQL> SELECT WM_CONCAT(DISTINCT TYPE) from v$parameter;
SQL> |
SOLUTION
Why LISTAGG?
LISTAGG is superior to WM_CONCAT. It is fewer latches than wm_concat, latches are lightweight locks that impact the scalability, the more latches we do, the less the scalability is.
LISTAGG does not support the "DISTINCT" option. But it does exclude null values. So, you need to take care of the distinct values yourself before applying LISTAGG.
EXAMPLE:
SQL> drop table t purge; Table dropped. SQL>
Table created. --
SQL>
1 row created. SQL> insert into t values (1,'Name2'); 1 row created. SQL> insert into t values (1,'Name3'); 1 row created. SQL>
SQL> insert into t values (2,'Name1'); 1 row created. SQL> insert into t values (2,'Name2'); 1 row created. SQL> insert into t values (2,'Name1');
SQL> insert into t values (2,'Name2'); 1 row created. SQL> insert into t values (2,'Name4'); 1 row created. SQL>
1 row selected. SQL>
1 row selected.
|
More Information:
From the doc: Oracle Database 12.2 New Features
"Enhanced LISTAGG Functionality
LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality has been added for managing situations where the length of the concatenated string is too long.
Developers can now control the process for managing overflowing LISTAGG aggregates. This increases the productivity and flexibility of this aggregation function."
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29487349/viewspace-2775435/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORA-00904: "wm_concat":invalid identifier錯誤如何解決?IDE
- ORA-00904: invalid identifierIDE
- ORA-00904: "DROP_SEGMENTS": invalid identifierIDE
- exp匯出出現:ORA-00904: : invalid identifierIDE
- exp匯出出現:ORA-00904: "POLTYP": invalid identifierIDE
- 關於merge時,出現的ORA-00904 invalid identifierIDE
- [20200312]ORA-00904 POLTYP invalid identifier.txtIDE
- [20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txtIDEOBJ
- Python 錯誤 SyntaxError: invalid character in identifierPythonErrorIDE
- [20211022]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts(補充).txtIDEOBJ
- 列轉行聚合的簡單實現ORACLE WM_CONCAT LISTAGG函式Oracle函式
- 11g升級到Oracle 12c碰到的問題 - ORA-00904 WM_CONCATOracle
- LISTAGG 函式函式
- wm_concat函式函式
- wm_concat函式與oracle版本函式Oracle
- sqlserver docSQLServer
- doc指令
- 【函式】wm_concat包的訂製函式
- WM_CONCAT這函式,別再用了!!!!函式
- oracle wm_concat(column)函式的使用Oracle函式
- wm_concat函式的排序問題函式排序
- java se docJava
- PostgreSQL DBA(80) - Object Identifier TypesSQLObjectIDE
- Oracle Custom Support Identifier(CSI)OracleIDE
- Oracle11.2新特性之listagg函式Oracle函式
- 【SQL 分析函式】wm_concat 行列轉換SQL函式
- ORA-00904: : 識別符號無效符號
- oracle中listagg()和wmsys.wm_concat()基本用法Oracle
- 用listagg函式分組實現列轉行函式
- Oracle identifiers :SID (System Identifier)OracleIDE
- JavaScript invalid 事件JavaScript事件
- Doc網路命令
- exp匯出出現:ORA-00904::invalididentifierIDE
- 使用errorstack解決ORA-00904一例Error
- iOS 上架報錯 This bundle is invalid 或 Invalid Image PathiOS
- 使用listagg函式完成行列轉換一例函式
- ORA-00904故障分析與解決一例
- oracle ORA-00904 poltyp 識別符號無效Oracle符號