引用说明: 5. WM_CONCAT标识符无效解决办法引用自
https://blog.csdn.net/wang_459914856/article/details/82419447
1. 安装时取消勾选
[创建为容器数据库
], 否则创建用户时报错: 公用用户名或角色无效,
用户名前需要加 C
2. 启动Tomcat连接数据库报错: ORA-28040 没有匹配的验证协议
$ORACLE_HOME/network/admin/sqlnet.ora
(Oracle压缩包解压目录
)
加入如下:
SQLNET.ALLOWED_LOGON_VERSION
=8
3. 启动Tomcat查询数据库报错: ORA-01017 用户名口令无效
; 登录被拒绝
修改用户登录密码
(密码可以和之前一样, 执行一遍
)
a. 重新打开cmd窗口
b. sqlplus sys/sys as sysdba
;
c. alter user 用户名 identified by 密码
d. ALTER USER username ACCOUNT UNLOCK
;
e. 重启tomcat连接成功
5. 登录页面正常加载, 点击登录按钮, 控制台报错:
"WM_CONCAT": 标识符无效 或 ORA-01775: 同义词的循环链
原因: 11gr2和12C上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误
解决办法
(复制到sqlplus, 点击enter不执行, 我实在Toad
for oracle执行的, 其他工具没试过
)：
使用Toad
for oracle连接数据库, 依次执行SQL:
1. 解锁sys用户
alter user sys account unlock
;
2. 创建包，包体，函数
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
);
3. 定义类型body：
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE
(SCTX IN OUT WM_CONCAT_IMPL
)
RETURN NUMBER
IS
BEGIN
SCTX :
= WM_CONCAT_IMPL
(NULL
) ;
RETURN ODCICONST.SUCCESS
;
END
;
MEMBER FUNCTION ODCIAGGREGATEITERATE
(SELF IN OUT WM_CONCAT_IMPL,
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,
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,
SCTX2 IN WM_CONCAT_IMPL
)
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
;
4. 自定义函数：自定义行变列函数
(这两句我复制执行报错, 手动敲执行成功的
):
CREATE OR REPLACE FUNCTION wm_concat
(P1 VARCHAR2
)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL
;
5. 创建同义词并授权
(逐行复制执行
)
create public synonym WM_CONCAT_IMPL
for sys.WM_CONCAT_IMPL
;
create public synonym wm_concat
for sys.wm_concat
;
grant execute on WM_CONCAT_IMPL to public
;
grant execute on wm_concat to public
;
