PL/SQL: ORA-00942: table or view does not exist

来源：互联网发布：sql set rowcount 编辑：程序博客网时间：2024/04/27 14:07

今天在写Oracle存储过程时一个用户对另一个用户的表进行insert操作时，报“PL/SQL: ORA-00942: table or view does not exist”错误，存储过程语句如下：

CREATE OR REPLACE PROCEDURE HTPAD.PROC_ZTWD_VIPINTEGRAL_SYNC AS /******************************************************************************     NAME:     PROC_ZTWD_DATA_SYNC     PURPOSE:  积分余额表数据同步     PARAMETERS:     Name        Description     ---------       ------------------------------------------------------------------     REVISIONS:     Ver        Date        Author           Description     ---------  ----------  ---------------  ------------------------------------     1.0        2016-10-18     jay     1. Created this procedure.  ******************************************************************************/  V_ERRMSG VARCHAR2(255);BEGIN  --ZTWD_VIPINTEGRAL  INSERT INTO HTVIP.ZTWD_VIPINTEGRAL  (         VIP_ID, TOTAL_EXPEND, POINT_BALANCE, TOTAL_REDEEM, TOTAL_EXPIRED,         ZCRE_DATE, ZCRE_TIME, ZCRE_USER, ZUP_DATE, ZUP_TIME, ZUP_USER  )  SELECT /*+CHOOSE+*/ TO_NUMBER(VIP_ID) VIP_ID, TOTAL_EXPEND, POINT_BALANCE, TOTAL_REDEEM, TOTAL_EXPIRED,         TO_CHAR(TO_DATE(ZCRE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') ZCRE_DATE, TO_CHAR(TO_DATE(ZCRE_TIME, 'hh24:mi:ss'),'hh24:mi:ss') ZCRE_TIME,         CASE WHEN ZCRE_USER = ' ' THEN '' ELSE ZCRE_USER END ZCRE_USER,         TO_CHAR(TO_DATE(ZUP_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') ZUP_DATE, TO_CHAR(TO_DATE(ZUP_TIME, 'hh24:mi:ss'),'hh24:mi:ss') ZUP_TIME,         CASE WHEN ZUP_USER = ' ' THEN '' ELSE ZUP_USER END ZUP_USER  FROM HTPAD.ZTWD_VIPINTEGRAL;  COMMIT;EXCEPTION  WHEN OTHERS THEN    V_ERRMSG := TRIM(SQLERRM);    ROLLBACK;    RAISE;END;

报错截图如下：

原因是当前用户没有HTVIP.ZTWD_VIPINTEGRAL表的插入权限导致的,如果A用户在存储过程中调用B用户的对象，则必须B显示的把该对象的相应权限赋予给用户A。

解决办法：
使用HTVIP用户名登录授权给需要执行存储过程的用户;

grant insert on htvip.ztwd_vipintegral to htpad;

赋完权限就再次编译，则编译成功。

1 0