Shulou(Shulou.com)06/01 Report--

Today, the customer site business dumped me with an error concession, ora-00904: "WM_CONCAT": invalid identifier.

A preliminary analysis of the following scenarios:

Because the previous set of libraries had to be deleted the day before yesterday, and their business user A used the set of libraries that they planned to delete, user A was exported and imported into the new environment, and no errors were reported in expdp/impdp import and export. It was successfully restored in the new environment yesterday, but an error was reported in the business report today. There is really no such function in the new environment, while there is the function in the old environment, but it belongs to user B. it is guessed that the old environment established a B synonym for A before.

Solution:

1. Get the DDL statement that creates the function, and create the function directly in the new environment

SQL > set long 999SQL > select dbms_metadata.get_ddl ('FUNCTION','WM_CONCAT','TBCS') from dual DBMS_METADATA.GET_DDL ('FUNCTION','WM_CONCAT','TBCS')-CREATE OR REPLACE EDITIONABLE FUNCTION "TBCS". "WM_CONCAT" (P1 VARCHAR2) RETURN clob AGGREGATE USING WM_CONCAT_IMPL

Execute DDL statement

SQL > CREATE OR REPLACE EDITIONABLE FUNCTION "AOPEN". "WM_CONCAT" (P1 VARCHAR2) RETURN clob AGGREGATE USING WM_CONCAT_IMPL; 2 / Warning: Function created with compilation errors.SQL >

After this operation, obviously reported an error, let the business test, certainly can not be tested, the view function is invalid

SQL > select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME from dba_objects where object_name=upper ('wm_concat') OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE LAST_DDL_TIME -A WM_CONCAT FUNCTION 20191121 172355SQL >

2. Export function in importing new environment

Since the first method is not good, consider the second method to migrate the wm_concat function of the old environment B user to the new environment to test whether it is feasible or not.

Export function

Expdp\'/ as sysdba\ 'directory=expdp_2019 dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_expdp_20191121.log schemas=B include=function:\ "IN\' WM_CONCAT\'\" Export: Release 12.1.0.2.0-Production on Thu Nov 21 17:46:38 2019Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP Advanced Analyticsand Real Application Testing optionsStarting "SYS". "SYS_EXPORT_SCHEMA_02": "/ * AS SYSDBA" directory=expdp_2019 dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_expdp_20191121.log schemas=B include=function: "IN 'WM_CONCAT'" Estimate in progress using BLOCKS method...Total estimation using BLOCKS method: 0 KBProcessing object type SCHEMA_EXPORT/FUNCTION/FUNCTIONProcessing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTIONMaster table "SYS". "SYS_EXPORT_SCHEMA_02" successfully Loaded/unloaded****Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is: / ora12c/oracle/dir_dump/function_wm_concat_20191121.dmpJob "SYS". SYS_EXPORT_SCHEMA_02 "successfully completed at Thu Nov 21 17:47:27 2019 elapsed 0 00:00:47

Import a new environment to convert users from B to A

Impdp\'/ as sysdba\ 'directory=DIR_DUMP dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_impdp_20191121.log remap_schema=B:AImport: Release 12.1.0.2.0-Production on Thu Nov 21 17:48:53 2019Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP Advanced Analyticsand Real Application Testing optionsMaster table "SYS". "SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYS". "SYS_IMPORT_FULL_01": "/ * AS SYSDBA" directory=DIR_DUMP dumpfile=function_wm_concat_20191121.dmp logfile=function_wm_concat_impdp_20191121.log remap_schema=B:AProcessing object type SCHEMA_EXPORT/FUNCTION/FUNCTIONProcessing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTIONORA-39082: Object type FUNCTION: "AOPEN". "WM_CONCAT" created with compilation warningsJob "SYS". "SYS_IMPORT_FULL_01" completed with 1 error (s) at Thu Nov 21 17:49:00 2019 elapsed 0 00:00:05

Check the function status, still invalid

SQL > select owner,object_name,object_type, status from dba_objects where owner not in ('SYS',' SYSTEM') AND status! = 'VALID' OWNER OBJECT_NAME OBJECT_TYPE STATUS -A WM_CONCAT FUNCTION INVALID

3. Create the function manually

Considering that the first method only creates function, but the package body and other contents are not created, package, body and function are created manually.

A. Create packages, package bodies, and functions with sys users

CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT (CURR_STR VARCHAR2 (32767), STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT WM_CONCAT_IMPL,P1 IN VARCHAR2) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN WM_CONCAT_IMPL,RETURNVALUE OUT VARCHAR2,FLAGS IN NUMBER) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT WM_CONCAT_IMPL,SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER)

B. Create type body

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPLISSTATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBERISBEGINSCTX: = WM_CONCAT_IMPL (NULL); RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT WM_CONCAT_IMPL,P1 IN VARCHAR2) RETURN NUMBERISBEGINIF (CURR_STR IS NOT NULL) THENCURR_STR: = CURR_STR |','| | P1X ELSECURRist STR: = P1utEND IF;RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN WM_CONCAT_IMPL,RETURNVALUE OUT VARCHAR2,FLAGS IN NUMBER) RETURN NUMBERISBEGINRETURNVALUE: = CURR_STR RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT WM_CONCAT_IMPL,SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBERISBEGINIF (SCTX2.CURR_STR IS NOT NULL) THENSELF.CURR_STR: = SELF.CURR_STR | |','| | SCTX2.CURR_STR; END IF;RETURN ODCICONST.SUCCESS;END;END

C. Create a function

CREATE OR REPLACE FUNCTION wm_concat (P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL

D. Create synonyms and authorize

Create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPLcreate public synonym wm_concat for sys.wm_concatgrant execute on WM_CONCAT_IMPL to publicgrant execute on wm_concat to public

Then check the function status, normal status. At this point, the problem is solved.

Summary

1. From the information found on the Internet, we know that the wm_concat function has been abandoned on 11gr2 and 12C, and the application does use this function in the program, which leads to errors in the program. In order to reduce the programmer's workload of modifying the program, we can only create a wm_concat function by hand to solve the problem temporarily, but note that if the function is created in time, we also need to use to_char (wm_concat ()) in the process of use. To completely replace the previous application.

2. It is also found that there is this function under the VMSYS user, but the user is locked by default. I also unlocked the VMSYS user this time, but there is no function.

SQL > alter user WMSYS account unlock;User altered.SQL > select owner,object_name,object_type from dba_objects where owner='WMSYS' and object_type = 'FUNCTION';no rows selected

3. Rebuild manually. If there are similar problems in the script, you can rebuild it quickly.

More related content:

ORA-00911: invalid character problem case and solution

The method of starting 1067 error handling by oracle listener

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.