2

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                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
PL/SQL Release 11.2.0.2.0 - Production                                           
CORE    11.2.0.2.0  Production                                                         
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
NLSRTL Version 11.2.0.2.0 - Production
Liem Do
  • 933
  • 1
  • 7
  • 13
  • You are on 11gR2. Why are you using the undocumented (and hence unsupported) WM_CONCAT function rather than [LISTAGG()](http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030) ??? – APC Jun 17 '15 at 04:20
  • The whole script is large and difficult to replace with listagg :( – Liem Do Jun 17 '15 at 04:36
  • Please post the result of `select banner from v$version`. Are you sure you are on `11g`? `WM_CONCAT` has been removed from `12c` though. See http://stackoverflow.com/a/28758117/3989608 and [Why not use WM_CONCAT function in Oracle?](http://lalitkumarb.wordpress.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/) – Lalit Kumar B Jun 17 '15 at 04:49
  • What process did you use to clone the database? – APC Jun 17 '15 at 04:54
  • My Oracle: `BANNER -------------------------------------------------------------------------------- Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for 32-bit Windows: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production` – Liem Do Jun 17 '15 at 05:30
  • Can you login as `SYSTEM` and check if the following `SELECT object_name FROM dba_objects WHERE owner='WMSYS' AND object_name LIKE 'WM\_%' ESCAPE '\';` – Lalit Kumar B Jun 17 '15 at 05:46
  • Yes, it result **no rows selected**. I think i found the problem because im using Oracle XE – Liem Do Jun 17 '15 at 05:47
  • 2
    It means whoever installed Oracle, opted not to install it. WMSYS account is used for Oracle Workspace Manager which is an optional feature. – Lalit Kumar B Jun 17 '15 at 05:51
  • What happens if you log in as `sys` and then run `grant execute on wmsys.wm_concat to public`? –  Jun 17 '15 at 06:02
  • I try run `grant execute on wmsys.wm_concat to public` in sqlplus system but a only a blank line shown – Liem Do Jun 17 '15 at 06:10

0 Answers0