select p.id as projectID,to_char(WMSYS.WM_CONCAT(u.username)) as managerNames
from bizproject p,sysuser u
where ',' || p.managers || ',' like '%,' || u.id || ',%'
group by p.id;这个语句正常执行 WMSYS.WM_CONCAT 正常调用
但把查询结果创建视图时 会报 ora-01031:权限不足 错误还有就是我存储过程中使用到WM_CONCAT的也同样出现一样的错误 求大牛来看看ora-01031 WM_CONCAT
from bizproject p,sysuser u
where ',' || p.managers || ',' like '%,' || u.id || ',%'
group by p.id;这个语句正常执行 WMSYS.WM_CONCAT 正常调用
但把查询结果创建视图时 会报 ora-01031:权限不足 错误还有就是我存储过程中使用到WM_CONCAT的也同样出现一样的错误 求大牛来看看ora-01031 WM_CONCAT
bizproject p,sysuser u
这两张表有一张不是 wmsys这个用户的吧?
如果是这样、把不是 wmsys这个用户的那张表的select权限授给他就是了
已连接。
SQL> create table a(id varchar2(2));表已创建。SQL> insert into a values('1');已创建 1 行。SQL> insert into a values('2');已创建 1 行。SQL> insert into a values('3');已创建 1 行。SQL> commit;提交完成。SQL> select wmsys.wm_concat(id) from a;WMSYS.WM_CONCAT(ID)
--------------------------------------------------------------------------------1,2,3SQL> create or replace view va as select wmsys.wm_concat(id) cola from a;
create or replace view va as select wmsys.wm_concat(id) cola from a
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> conn / as sysdba
已连接。SQL> grant create any view to t;授权成功。SQL> conn t/t@testdb
已连接。
SQL> create or replace view va as select wmsys.wm_concat(id) cola from a;视图已创建。SQL>
谢谢大家了 由于没有DBA账户权限 果断想了另外一个办法 自定义了wmsys.wm_concat 下楼帖上
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR clob,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
SCTX2 IN zh_concat_im) RETURN NUMBER
);
/create or replace TYPE BODY zh_concat_im
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
RETURN NUMBER
IS
BEGIN
SCTX := zh_concat_im(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
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 zh_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
SCTX2 IN zh_concat_im)
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 zh_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;
create view my_view as select id,wm_concat(nn) tt from test group by id
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> conn /as sysdba
已连接。
SQL> grant create any view to u2;授权成功。
SQL> conn u2/u2
已连接。
SQL> create view my_view as select id,wmsys.wm_concat(nn) tt from test group by id;视图已创建。SQL> select * from my_view; ID
----------
TT
------------------------------------------------------------------------------------
1
aa,bb