目录
1.问题
- 添加权限的时候,权限过多,超过listagg函数默认的varchar长度(4000byte)
- 新建函数修改1以后,视图被重新创建,视图之上的触发器找不到了
2.解决
- 使用listagg进行分组拼接时,常常会报 ora-01489 错误,造成该报错的主要原因是:listagg 默认返回的数值类型是varchar oracle对字符变量的长度限制,正常情况下,oracle定义的varchar2类型变量的长度不应超过4000字节,如有必要可转换为long 或clob类型。链接 https://www.cnblogs.com/iupoint/p/10974864.html
-- 定义 tab_varchar2 数据类型
CREATE TYPE tab_varchar2AS TABLE OF VARCHAR2(4000);
-- 新建 concat_array 函数
CREATE OR REPLACE FUNCTION concat_array(p tab_varchar2)RETURN CLOBIS
l_result CLOB;
BEGIN
FOR ccIN (SELECT column_valueFROM TABLE(p)ORDER BY column_value) LOOP
l_result := l_result ||' '|| cc.column_value;
END LOOP;
return l_result;
END;
-- 分组拼接
SELECT
item,
concat_array(CAST(COLLECT(attribute)AS tab_varchar2)) attributes
FROM
tb
GROUP BY
item;
- 注意:视图是单表的时候,可以直接更新数据(insert,updata,delete)会作用到那张表上面,但是如果视图是多个表关联的话。需要创建触发器,才能更新数据。而当视图被修改前一点要先看看有什么触发器,提前保存下来。
-- 使用新的函数concat_array 代替oracle 视图 "APEX_190200"."APEX_APPL_ACL_USERS"中的函数 listagg,修改视图oracle apex的视图被修改以后,上面的触发器找不到了,需要重新创建触发器。
-- 修改视图
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "APEX_190200"."APEX_APPL_ACL_USERS" ("ID", "WORKSPACE_ID", "WORKSPACE", "WORKSPACE_DISPLAY_NAME", "APPLICATION_ID", "APPLICATION_NAME", "USER_NAME", "USER_NAME_LC", "ROLE_IDS", "ROLE_NAMES") AS
select
min( u.id ) id,
--
f.workspace_id,
f.workspace,
f.workspace_display_name,
f.application_id,
f.application_name,
--
u.user_name,
lower(u.user_name) user_name_lc,
listagg( u.group_id, ':' ) within group ( order by u.group_id ) as role_ids
--, listagg( g.group_name, ', ' ) within group ( order by g.group_name ) as role_names
,concat_array(CAST(COLLECT(g.group_name)AS tab_varchar2)) as role_names
from wwv_flow_authorized_for_dml f,
wwv_flow_fnd_user_groups g,
wwv_flow_acl_group_users u
where g.flow_id = f.application_id
and u.group_id = g.id
and u.security_group_id = g.security_group_id
group by f.workspace_id,
f.workspace,
f.workspace_display_name,
f.application_id,
f.application_name,
u.user_name
-- 重新创建触发器
create or replace trigger apex_appl_acl_users_iot
instead of insert or update or delete
on apex_appl_acl_users
for each row
begin
if inserting or updating then
wwv_flow_acl_api.replace_user_roles (
p_application_id => coalesce( :old.application_id, :new.application_id ), -- don't support updates
p_user_name => coalesce( :old.user_name, :new.user_name ), -- don't support updates
p_role_ids => wwv_flow_string.split_numbers( :new.role_ids, ':' ) );
elsif deleting then
wwv_flow_acl_api.remove_all_user_roles (
p_application_id => :old.application_id, -- don't support updates
p_user_name => :old.user_name ); -- don't support updates
end if;
end;
- 前端验证
- 其他问题
- 当创建视图的语句是 CREATE OR REPLACE FORCE NONEDITIONABLE VIEW。repalce 是替换,force是强制。(当没有视图用到的表不存在时,也会创建,但是相当于是个空视图。)
NONEDITIONABLE 是不可更新。个人认为这个语句一般代表上面有触发器,看到这样的语句要小心一点。 - 官网很重要,当看了官网以后,才有了是不是视图上面的触发器没了的思路
- 执行语句前要check好(清楚语句是干嘛的,参数代表什么意思),不要找到了语句就执行。避免二次伤害。
- apex插入用户权限的流程是。使用 APEX_APPL_ACL_USERS 视图前端会展示出所有用户的权限,当有更新操作的时候会通过这个触发器apex_appl_acl_users_iot,更新表wwv_flow_acl_group_users 的用户权限,一条权限是对应一条记录
- 查询触发器
- 当创建视图的语句是 CREATE OR REPLACE FORCE NONEDITIONABLE VIEW。repalce 是替换,force是强制。(当没有视图用到的表不存在时,也会创建,但是相当于是个空视图。)
查出视图对应的触发器:
select trigger_name from all_triggers where table_name='APEX_APPL_ACL_USERS';
查出触发器的创建语句:
select text from all_source where type='TRIGGER' AND name='APEX_APPL_ACL_USERS_IOT';