Friday, January 10, 2014

WM_CONCAT Removed From 12c Workspace Manager?

It appears that wm_concat function has been removed from 12c workspace manager. Oracle always insisted that this is an internal function to be used with Oracle products and not for end user consumption (refer 1336219.1 and 1300595.1). But it was available for use even with 11gR2 but it seem it's been removed or more likley access to end user is completely removed.
On a 11gR2 system querying dba_objects show the wm_concat as a function in wmsys schema.
SQL> select owner, object_type from dba_objects where object_name = 'WM_CONCAT';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
PUBLIC                         SYNONYM
WMSYS                          FUNCTION
But on 12cR1 there's no such function at all and same query returns no rows. The 12cR1 database has the workspace manager components installed and valid.
SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- ----------
JServer JAVA Virtual Machine             VALID
OLAP Analytic Workspace                  VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Database Vault                    VALID
Oracle Label Security                    VALID
Oracle Multimedia                        VALID
Oracle OLAP API                          VALID
Oracle Real Application Clusters         OPTION OFF
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID
Spatial                                  VALID
Another way to verify the installation of workspace manager component is
SQL> select dbms_wm.getWorkspace from dual;

GETWORKS
--------
LIVE


Querying all the objects owned by wmsys user in a 12cR1 DB also does not show the wm_concat function anymore (output has been truncated to show only objects starting with wm_)
SQL> select object_name from dba_objects where owner='WMSYS' order by 1;
WM_COMPRESSIBLE_TABLES
WM_COMPRESS_BATCH_SIZES
WM_CONTAINS
WM_DDL_UTIL
WM_DDL_UTIL
WM_EQUALS
WM_ERROR
WM_ERROR
WM_EVENTS_INFO
WM_GREATERTHAN
WM_INSTALLATION
WM_INTERSECTION
WM_LDIFF
WM_LESSTHAN
WM_MEETS
WM_OVERLAPS
WM_PERIOD
WM_PERIOD
WM_RDIFF
WM_REPLICATION_INFO
So it seem any application has been relying on wm_concat function will not work once upgraded to 12c and alternatives to wm_concat must be found and tested before the upgrade.

Related Post
Use of WM_CONCAT Can Exhaust Temporary Tablespace in 11gR2 & 10gR2

Useful Metalink Notes
WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function [ID 1336219.1]
Problem with WMSYS.WM_CONCAT Function after Upgrading [ID 1300595.1]
How do you manually install/deinstall Oracle Workspace Manager [ID 731576.1]