oceanbase
[Bug]: ORACLE模式中程序包显示bug
ODC version
4.2.0 4.2.1 4.2.2
OB version
5.7.25-OceanBase-v4.2.1.1' OceanBase 4.2.1.1 (r101000062023103122-34e6ed68f3db97700b665a618fc3801f6b109124) (Built Oct 31 2023 22:22:10)
What happened?
当我全部实现了包头中定义的4个存储过程后进行保存和编译。
此时在左侧的对象列表中，我点击我的程序包，选择包头-子程序，显示4个。
选择包体-子程序，此时发现只能显示2个。
What did you expect to happen?
包体中所有子程序需要在对象列表中正常显示。
How can we reproduce it (as minimally and precisely as possible)?
该程序包如此，其他方式的测试暂时无法重现。附上该用例DDL语句。 依赖表对象DDL： CREATE TABLE "TS_WEB_CONFIG" ( "CONFIGCODE" VARCHAR2(50 CHAR), "CONFIGNAME" VARCHAR2(255 CHAR), "CONFIGVALUE" VARCHAR2(255 CHAR), "CONFIGTYPE" VARCHAR2(255 CHAR), "REMARK" VARCHAR2(255 CHAR), PRIMARY KEY ("CONFIGCODE") )
CREATE TABLE "TS_WEB_ROLE" ( "ROLECODE" VARCHAR2(50 CHAR), "ROLENAME" VARCHAR2(255 CHAR), "ISADMIN" NUMBER(1), "HOSPCHANGEABLE" NUMBER(1), "ISVALID" NUMBER(1), PRIMARY KEY ("ROLECODE") )
CREATE TABLE "TS_WEB_USER" ( "USERCODE" VARCHAR2(50 CHAR), "USERNAME" VARCHAR2(255 CHAR), "HOSPCODE" VARCHAR2(50 CHAR), "WEBROLE" VARCHAR2(50 CHAR), "HOSPAREAROLE" VARCHAR2(4000 CHAR), "HOSPCLASSROLE" VARCHAR2(4000 CHAR), "HOSPGRADEROLE" VARCHAR2(4000 CHAR), "GROUPNAME" VARCHAR2(255 CHAR), "LXYX" VARCHAR2(255 CHAR), "LXDH" VARCHAR2(255 CHAR), "BEGDATE" DATE, "ENDDATE" DATE, "PWD" VARCHAR2(500 CHAR), "PWDSALT" VARCHAR2(500 CHAR), "SETPWDDATE" DATE, "FAILCNT" NUMBER, "ISVALID" NUMBER(1), PRIMARY KEY ("USERCODE") )
CREATE TABLE "T_WEB_LOG_PWD" ( "USERCODE" VARCHAR2(50 CHAR), "USERNAME" VARCHAR2(255 CHAR), "SRCPWD" VARCHAR2(500 CHAR), "NEWPWD" VARCHAR2(500 CHAR), "OPTYPE" VARCHAR2(50 CHAR), "OPCODE" VARCHAR2(50 CHAR), "OPDATE" DATE, PRIMARY KEY ("USERCODE", "OPDATE") )
程序包DDL语句： create or replace PACKAGE pkg_user_test AS PROCEDURE pkg_user_info_query(p_usercode VARCHAR2,p_opcode VARCHAR2,P_CUR1 OUT SYS_REFCURSOR); PROCEDURE pkg_user_info_add(p_json CLOB,p_opcode VARCHAR2); PROCEDURE pkg_user_info_modify(p_json1 CLOB,p_opcode VARCHAR2); PROCEDURE pkg_user_pwd_modify(p_srcpwd VARCHAR2,p_newpwd VARCHAR2,p_opcode VARCHAR2); END pkg_user_test
create or replace PACKAGE BODY pkg_user_test AS v_UserCode VARCHAR2(50 CHAR); v_UserName VARCHAR2(255 CHAR); v_HospCode VARCHAR2(50 CHAR); v_WebRole VARCHAR2(50 CHAR); v_HospAreaRole VARCHAR2(4000 CHAR); v_HospClassRole VARCHAR2(4000 CHAR); v_HospGradeRole VARCHAR2(4000 CHAR); v_GroupName VARCHAR2(255 CHAR); v_lxyx VARCHAR2(255 CHAR); v_lxdh VARCHAR2(255 CHAR); v_BegDate DATE; v_EndDate DATE; v_IsValid NUMBER(1);
v_DefaultPwd VARCHAR2(500 CHAR);
v_rowCount number;
v_AllowPwdFailCnt number;
PROCEDURE pkg_user_info_query(p_usercode VARCHAR2,p_opcode VARCHAR2,P_CUR1 OUT SYS_REFCURSOR) AS
BEGIN
select COUNT(*) into v_rowCount from ts_web_user a,TS_WEB_ROLE b where a.UserCode=p_usercode and a.WEBROLE=b.ROLECODE and a.ISVALID=1 and b.ISVALID=1 and b.ISADMIN=1;
if v_rowCount=0 then
RAISE_APPLICATION_ERROR(-20001, '当前用户无权限');
end if;
select COUNT(*) into v_rowCount from ts_web_user where UserCode=p_usercode;
if v_rowCount=0 then
RAISE_APPLICATION_ERROR(-20001, '未检索到用户信息');
end if;
select cast(ConfigValue as number) into v_AllowPwdFailCnt from ts_web_config where lower(ConfigCode)='allowpwdfailcnt';
open P_CUR1 for
select UserCode,UserName,HospCode,WebRole,HospAreaRole,HospClassRole,HospGradeRole,GroupName,lxyx,lxdh,BegDate,EndDate
,(case when FailCnt>v_AllowPwdFailCnt then 1 else 0 end) IsLock,IsValid
from ts_web_user
where UserCode=p_usercode;
END pkg_user_info_query;
PROCEDURE pkg_user_info_add(p_json CLOB,p_opcode VARCHAR2) AS
BEGIN
select COUNT(*) into v_rowCount from ts_web_user a,TS_WEB_ROLE b where a.UserCode=p_opcode and a.WEBROLE=b.ROLECODE and a.ISVALID=1 and b.ISVALID=1 and b.ISADMIN=1;
if v_rowCount=0 then
RAISE_APPLICATION_ERROR(-20001, '当前用户无权限');
end if;
SELECT UserCode,UserName,HospCode,WebRole,HospAreaRole,HospClassRole,HospGradeRole,GroupName,lxyx,lxdh,BegDate,EndDate,IsValid
into v_UserCode,v_UserName,v_HospCode,v_WebRole,v_HospAreaRole,v_HospClassRole,v_HospGradeRole,v_GroupName,v_lxyx,v_lxdh,v_BegDate,v_EndDate,v_IsValid
FROM JSON_TABLE(p_json
,'$[*]' COLUMNS (
UserCode VARCHAR2(50 CHAR) PATH '$.usercode',
UserName VARCHAR2(255 CHAR) PATH '$.username',
HospCode VARCHAR2(50 CHAR) PATH '$.hospcode',
WebRole VARCHAR2(50 CHAR) PATH '$.webrole',
HospAreaRole VARCHAR2(4000 CHAR) PATH '$.hosparearole',
HospClassRole VARCHAR2(4000 CHAR) PATH '$.hospclassrole',
HospGradeRole VARCHAR2(4000 CHAR) PATH '$.hospgraderole',
GroupName VARCHAR2(255 CHAR) PATH '$.groupname',
lxyx VARCHAR2(255 CHAR) PATH '$.lxyx',
lxdh VARCHAR2(255 CHAR) PATH '$.lxdh',
BegDate DATE PATH '$.begdate',
EndDate DATE PATH '$.enddate',
Pwd VARCHAR2(500 CHAR) PATH '$.pwd',
IsValid NUMBER(1) PATH '$.isvalid'
)
);
if NVL(v_UserCode,'')='' then
RAISE_APPLICATION_ERROR(-20001, '用户代码不能为空');
end if;
select COUNT(*) into v_rowCount from ts_web_user where UserCode=v_UserCode;
if v_rowCount>0 then
RAISE_APPLICATION_ERROR(-20001, '用户'||NVL(v_UserCode,'')||'已存在');
end if;
select ConfigValue into v_DefaultPwd from ts_web_config where LOWER(ConfigCode)='defaultpwd';
if NVL(v_DefaultPwd,'')='' then
RAISE_APPLICATION_ERROR(-20001, '未设置默认密码,请联系管理员');
end if;
insert into ts_web_user
(UserCode,UserName,HospCode,WebRole,HospAreaRole,HospClassRole,HospGradeRole,GroupName,lxyx,lxdh,BegDate,EndDate,Pwd,IsValid)
values(v_UserCode,v_UserName,v_HospCode,v_WebRole,v_HospAreaRole,v_HospClassRole,v_HospGradeRole,v_GroupName,v_lxyx,v_lxdh,v_BegDate,v_EndDate,v_DefaultPwd,v_IsValid);
END pkg_user_info_add;
PROCEDURE pkg_user_info_modify(p_json1 CLOB,p_opcode VARCHAR2) AS
BEGIN
select COUNT(*) into v_rowCount from ts_web_user a,TS_WEB_ROLE b where a.UserCode=p_opcode and a.WEBROLE=b.ROLECODE and a.ISVALID=1 and b.ISVALID=1 and b.ISADMIN=1;
if v_rowCount=0 then
RAISE_APPLICATION_ERROR(-20001, '当前用户无权限');
end if;
SELECT UserCode,UserName,HospCode,WebRole,HospAreaRole,HospClassRole,HospGradeRole,GroupName,lxyx,lxdh,BegDate,EndDate,IsValid
into v_UserCode,v_UserName,v_HospCode,v_WebRole,v_HospAreaRole,v_HospClassRole,v_HospGradeRole,v_GroupName,v_lxyx,v_lxdh,v_BegDate,v_EndDate,v_IsValid
FROM JSON_TABLE(p_json1
,'$[*]' COLUMNS (
UserCode VARCHAR2(50 CHAR) PATH '$.usercode',
UserName VARCHAR2(255 CHAR) PATH '$.username',
HospCode VARCHAR2(50 CHAR) PATH '$.hospcode',
WebRole VARCHAR2(50 CHAR) PATH '$.webrole',
HospAreaRole VARCHAR2(4000 CHAR) PATH '$.hosparearole',
HospClassRole VARCHAR2(4000 CHAR) PATH '$.hospclassrole',
HospGradeRole VARCHAR2(4000 CHAR) PATH '$.hospgraderole',
GroupName VARCHAR2(255 CHAR) PATH '$.groupname',
lxyx VARCHAR2(255 CHAR) PATH '$.lxyx',
lxdh VARCHAR2(255 CHAR) PATH '$.lxdh',
BegDate DATE PATH '$.begdate',
EndDate DATE PATH '$.enddate',
Pwd VARCHAR2(500 CHAR) PATH '$.pwd',
IsValid NUMBER(1) PATH '$.isvalid'
)
);
if NVL(v_UserCode,'')='' then
RAISE_APPLICATION_ERROR(-20001, '用户代码不能为空');
end if;
select COUNT(*) into v_rowCount from ts_web_user where UserCode=v_UserCode;
if v_rowCount=0 then
RAISE_APPLICATION_ERROR(-20001, '用户'||NVL(v_UserCode,'')||'不存在');
end if;
update ts_web_user
set
UserName=v_UserName,
HospCode=v_HospCode,
WebRole=v_WebRole,
HospAreaRole=v_HospAreaRole,
HospClassRole=v_HospClassRole,
HospGradeRole=v_HospGradeRole,
GroupName=v_GroupName,
lxyx=v_lxyx,
lxdh=v_lxdh,
BegDate=v_BegDate,
EndDate=v_EndDate,
IsValid=v_IsValid
where UserCode=v_UserCode;
END pkg_user_info_modify;
PROCEDURE pkg_user_pwd_modify(p_srcpwd VARCHAR2,p_newpwd VARCHAR2,p_opcode VARCHAR2) AS
BEGIN
select COUNT(*) into v_rowCount from ts_web_user WHERE USERCODE=p_opcode AND PWD=p_srcpwd;
if v_rowCount=0 then
RAISE_APPLICATION_ERROR(-20001, '原密码输入错误');
end if;
update ts_web_user
set pwd=p_newpwd,setpwddate=SYSDATE
where usercode=p_opcode;
insert into t_web_log_pwd
(UserCode,UserName,SrcPwd,NewPwd,OpType,OpCode)
select UserCode,UserName,p_srcpwd,p_newpwd,'修改个人密码',p_opcode
from ts_web_user
where usercode=p_opcode;
END pkg_user_pwd_modify;
PROCEDURE pkg_user_pwd_reset(p_usercode VARCHAR2,p_opcode VARCHAR2) AS
BEGIN
select COUNT(*) into v_rowCount from ts_web_user a,TS_WEB_ROLE b where a.UserCode=p_opcode and a.WEBROLE=b.ROLECODE and a.ISVALID=1 and b.ISVALID=1 and b.ISADMIN=1;
if v_rowCount=0 then
RAISE_APPLICATION_ERROR(-20001, '当前用户无权限');
end if;
select ConfigValue into v_DefaultPwd from ts_web_config where LOWER(ConfigCode)='defaultpwd';
if NVL(v_DefaultPwd,'')='' then
RAISE_APPLICATION_ERROR(-20001, '未设置默认密码,请联系管理员');
end if;
update ts_web_user
set pwd=v_DefaultPwd,setpwddate=NULL
where usercode=p_usercode;
insert into t_web_log_pwd
(UserCode,UserName,SrcPwd,NewPwd,OpType,OpCode)
select UserCode,UserName,NULL,v_DefaultPwd,'重置用户密码',p_opcode
from ts_web_user
where usercode=p_usercode;
END pkg_user_pwd_reset;
END pkg_user_test
Anything else we need to know?
No response
Cloud
No response
