插入视图时收到'ORA-01031:权限不足'错误
在用户名'MY_ADMIN'下,我成功创建了一个名为'NOTIFICATIONS'的表和一个名为'V_NOTIFICATIONS'的视图。在'V_NOTIFICATIONS'视图中,我已成功创建了一个触发器和一个包,该包接受用户尝试插入视图并将其插入表中的内容。当用户尝试在视图上执行更新和删除功能时,“V_NOTIFICATIONS”触发器和包还会在表上执行更新和删除功能。
我在目前正在处理的项目中已经完成了许多视图,因为许多视图位于许多不同表的顶部,但是当尝试在此视图中插入记录时,我收到“ORA-01031:权限不足”错误。
我可以使用包中的相同代码直接插入表中,但不能直接插入到视图中。任何有关这方面的帮助将不胜感激。
这是请求的代码:
视图:(当下面的UNION被注释掉时,包按预期运行)
CREATE OR REPLACE FORCE VIEW "MY_ADMIN"."V_NOTIFICATIONS" AS
SELECT N_ID,
NOTIFICATION_TYPE,
CASE WHEN NOTIFICATION_DESC = 'C' THEN 'Copy' ELSE 'Send to' END NOTIFICATION_DESC,
CASE WHEN CONTACT_TYPE = 'D' THEN 'Department' ELSE 'Contact' END CONTACT_TYPE,
A.AU_USER_ID,
A.CONTACT_NAME,
D.DEPARTMENT_ID,
D.DEPT_DESC
FROM NOTIFICATIONS AN,
(SELECT A1.AU_USER_ID,
AU.FIRST_NAME || ' ' || AU.LAST_NAME CONTACT_NAME
FROM APP_USERS_CONTACT_INFO A1,
APPLICATION_USERS AU
WHERE A1.AU_USER_ID = AU.USER_ID
/*UNION
SELECT 0,
NULL
FROM DUAL*/) A,
(SELECT DEPARTMENT_ID,
DESCRIPTION DEPT_DESC
FROM DEPARTMENTS
UNION
SELECT 0 DEPARTMENT_ID,
NULL DEPT_DESC
FROM DUAL) D
WHERE NVL(AN.AU_USER_ID,0) = A.AU_USER_ID
AND NVL(AN.D_DEPARTMENT_ID,0) = D.DEPARTMENT_ID;
包:
CREATE OR REPLACE PACKAGE NOTIFICATIONS_PKG AS
PROCEDURE INSERT_NOTIFICATION(P_N_ROW V_NOTIFICATIONS%ROWTYPE);
END NOTIFICATIONS_PKG;
/
CREATE OR REPLACE PACKAGE BODY NOTIFICATIONS_PKG AS
PROCEDURE INSERT_NOTIFICATION(P_N_ROW V_NOTIFICATIONS%ROWTYPE) IS
L_NOTIFICATION_DESC VARCHAR2(1);
L_CONTACT_TYPE VARCHAR2(1);
BEGIN
CASE P_N_ROW.NOTIFICATION_DESC
WHEN 'Copy' THEN
L_NOTIFICATION_DESC := 'C';
ELSE
L_NOTIFICATION_DESC := 'S';
END CASE;
CASE P_N_ROW.CONTACT_TYPE
WHEN 'Department' THEN
L_CONTACT_TYPE := 'D';
ELSE
L_CONTACT_TYPE := 'C';
END CASE;
INSERT INTO NOTIFICATIONS VALUES (
P_N_ROW.N_ID,
P_N_ROW.NOTIFICATION_TYPE,
L_NOTIFICATION_DESC,
L_CONTACT_TYPE,
NVL(P_N_ROW.AU_USER_ID, 0),
NVL(P_N_ROW.DEPARTMENT_ID, 0),
APP_GLOBAL_PKG.GET_AUDIT);
END INSERT_AGREEMENT_NOTIFICATION;
END AGREEMENT_NOTIFICATIONS_PKG;
设置触发器只是为了将信息传递给此包以插入行。在尝试运行以下代码行时,我收到ORA-01031错误:
INSERT INTO V_AGREEMENT_NOTIFICATIONS VALUES (5781, 'Collateral Request', 'Send to', 'Contact', 797, '797T', 0, null);
没有找到相关结果
已邀请:
2 个回复
泪琉踞檄
看看会发生什么。 分享和享受。
伞腹