ORA-00904: "wm_concat":invalid identifier错误如何解决？

2023-05-23 09:18:22 浏览数 (2)
♣

题目部分

ORA-00904: "wm_concat":invalid identifier错误如何解决？

♣

答案部分

若在创建数据库的时候没有创建WMSYS用户，则在SQL或PL/SQL中有用到WM_CONCAT函数的时候就会报ORA-00904的错误。其实，WMSYS用户下的WM_CONCAT函数有很重要的用途，比如行转列，但是该函数不稳定。例如，在Oracle 10g上返回的是字符串类型，但是在Oracle 11gR2上返回的是CLOB类型。很多数据库开发人员在程序中都使用了该函数，若是系统升级，则会导致程序出现错误。为了减轻程序员修改程序的工作量，只有重建函数WM_CONCAT来解决该问题。

若没有创建WMSYS用户的话，则在查询DBA_OBJECTS视图的时候就不能查询到WM_CONCAT的相关信息。在正常情况下查询DBA_OBJECTS视图，会有如下的信息：

代码语言：javascript复制
 SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'WM_CONCAT%';

解决办法有两种，一种是采用Oracle本身的脚本来创建WM_CONCAT函数，一种是采用自己创建的函数来解决这个问题。

1、用Oracle自带脚本重建WMSYS用户的WMSYS.WM_CONCAT函数

运行如下脚本卸载WMSYS用户的数据：

代码语言：javascript复制
@$ORACLE_HOME/rdbms/admin/owmuinst.plb

运行如下脚本安装WMSYS用户即可创建WMSYS.WM_CONCAT函数：

代码语言：javascript复制
@$ORACLE_HOME/rdbms/admin/owminst.plb

解锁WMSYS用户：

代码语言：javascript复制
ALTER USER WMSYS ACCOUNT UNLOCK;

2、自己订制脚本

如果只是单个用户使用，那么不用刻意去创建WMSYS用户，可以在所需的用户下运行订制脚本，生成WM_CONCAT函数。另外，为了和系统的函数名区别开来，也可以修改函数名称。如果是多个用户使用，也可以运行自己定制的脚本，然后创建同义词，这样多个用户都可以使用。

下面按照返回值的不同分为几种情况来订制不同的脚本。

①　无分隔符，返回CLOB类型

创建函数的脚本如下所示：

代码语言：javascript复制
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_NULL_LHR AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR CLOB,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                       P1   IN CLOB) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
    RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_NULL_LHR IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL_CLOB_NULL_LHR(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                       P1   IN CLOB) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR ||  P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)
    RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR ||  SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/
CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_NULL_LHR(P1 VARCHAR2) RETURN CLOB
  AGGREGATE USING WM_CONCAT_IMPL_CLOB_NULL_LHR;
/
CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_NULL_LHR FOR WM_CONCAT_CLOB_NULL_LHR;
GRANT EXECUTE ON WM_CONCAT_CLOB_NULL_LHR TO PUBLIC;

以上函数的测试示例如下所示，函数的返回值是无分隔符的CLOB，在PL/SQL中要使用TO_CHAR进行转换：

代码语言：javascript复制
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
   USER_ID
----------
         0
         5
SYS@lhrdb21> SELECT WM_CONCAT_CLOB_NULL_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_CLOB_LHR_NULL(D.USER_ID)
--------------------------------------------------------------------------------
05

②　逗号分隔符，返回CLOB

创建函数的脚本如下所示：

代码语言：javascript复制
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_LHR AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR CLOB,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                       P1   IN CLOB) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
    RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_LHR IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL_CLOB_LHR(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                       P1   IN CLOB) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,
                                         RETURNVALUE OUT CLOB,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)
    RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/
CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_LHR(P1 VARCHAR2) RETURN CLOB
  AGGREGATE USING WM_CONCAT_IMPL_CLOB_LHR;
/
CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_LHR FOR WM_CONCAT_CLOB_LHR;
GRANT EXECUTE ON WM_CONCAT_CLOB_LHR TO PUBLIC;

以上函数的测试示例如下所示，函数的返回值是以逗号为分隔符的CLOB，在PL/SQL中需要使用TO_CHAR进行转换：

代码语言：javascript复制
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
   USER_ID
----------
         0
         5
SYS@lhrdb21> SELECT WM_CONCAT_CLOB_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_LHR(D.USER_ID)
--------------------------------------------------------------------------------
0,5

③　逗号分隔符，返回字符串类型

创建函数的脚本如下所示：

代码语言：javascript复制
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_STRINGS_LHR AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
                                       P1   IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
    RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_STRINGS_LHR IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL_STRINGS_LHR(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
                                       P1   IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,
                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)
    RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/
CREATE OR REPLACE FUNCTION WM_CONCAT_STRINGS_LHR(P1 VARCHAR2) RETURN VARCHAR2
  AGGREGATE USING WM_CONCAT_IMPL_STRINGS_LHR;
/
CREATE PUBLIC SYNONYM WM_CONCAT_STRINGS_LHR FOR WM_CONCAT_STRINGS_LHR;
GRANT EXECUTE ON WM_CONCAT_STRINGS_LHR TO PUBLIC;

以上函数的测试示例如下所示，函数的返回值是以逗号为分隔符的字符串：

代码语言：javascript复制
SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
   USER_ID
----------
         0
         5
SYS@lhrdb21> SELECT WM_CONCAT_STRINGS_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);
WM_CONCAT_STRINGS_LHR(D.USER_ID)
---------------------------------------------------
0,5

其实，与WM_CONCAT相似的还有一个函数是LISTAGG。这是一个Oracle的列转行函数，使用示例如下所示：

代码语言：javascript复制
WITH TEMP AS(
  SELECT 'China' NATION ,'Guangzhou' CITY FROM DUAL UNION ALL
  SELECT 'China' NATION ,'Shanghai' CITY FROM DUAL UNION ALL
  SELECT 'China' NATION ,'Beijing' CITY FROM DUAL UNION ALL
  SELECT 'USA' NATION ,'New York' CITY FROM DUAL UNION ALL
  SELECT 'USA' NATION ,'Bostom' CITY FROM DUAL UNION ALL
  SELECT 'USA' NATION ,'Bostom' CITY FROM DUAL UNION ALL
  SELECT 'Japan' NATION ,'Tokyo' CITY FROM DUAL
)
SELECT NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY)
FROM TEMP
GROUP BY NATION;

输出结果如下所示：

对于LISTAGG函数，如果聚合的内容太多就会报“ORA-01489: result of string concatenation is too long”的错误，那么这个时候可以从业务的角度去修改SQL，也可以使用WM_CONCAT函数返回CLOB类型来解决这个问题。

clob identifier return 函数 脚本

0 人点赞