由于一个视图的数据量太大，想通过创建动态视图的办法来分成若干个数据量小些的视图，写了个包如下：
CREATE OR REPLACE PACKAGE BODY SP_DLKC
IS
PROCEDURE SP_DLKC
(P_DWBM VARCHAR2,
P_DLBM VARCHAR2,
C OUT CUR)
AS
V_VIEWNAMEVARCHAR2(300);
BEGIN
V_VIEWNAME:='CREATE OR REPLACE VIEW PEDIS20WZ.GDWZ_KC_'||P_DLBM||' AS SELECT WZBM,WZMC, XHGG,CZZZ,QCTH,JLDW FROM dwzbm_v WHERE substr(wzbm,1,2)='''||P_DLBM||'''';
execute immediate V_VIEWNAME;
V_VIEWNAME:='CREATE OR REPLACE VIEW PEDIS20WZ.GDWZ_KC AS SELECT A.WZBM AS 物资编码, B.WZMC AS 物资名称, B.XHGG AS 型号规格, '||
'B.CZZZ AS 材质机型, B.QCTH AS 器材图号, B.JLDW AS 计量单位,A.DJ AS 单价 '||
'FROM GDWZ329_KC1 A,GDWZ_KC_'||P_DLBM||' B WHERE A.TBDWBM='''||P_DWBM||''' AND A.WZBM like '''||P_DLBM||'%'' AND A.WZBM=B.WZBM';
execute immediate V_VIEWNAME;
OPEN C FOR
SELECT * FROM GDWZ_KC ;
END SP_DLKC;
END;
在TEST时到第二个execute immediate 时报权限不足，怎么回事？
|
