创建PL/SQL存储过程时遇ORA-00942错误：表或视图不存在

一、为什么创建存储过程时就触发“表不存在”错误？ #

这是因为DB2的PL/SQL编译器会静态解析SQL语句：你在INSERT语句里用变量v_QUERY_SCHEMA作为Schema名称，编译阶段数据库会尝试寻找名为v_QUERY_SCHEMA的Schema及其下的DATA_COPY_STATUS表，显然这个Schema不存在，所以直接抛出错误，和你是否调用存储过程无关。

二、解决方案：使用动态SQL #

由于Schema名称是动态传入的参数，必须用动态SQL来构造并执行插入语句，修改后的存储过程代码如下：

CREATE OR REPLACE PROCEDURE REF_COPY_DB(
v_QUERY_SCHEMA in VARCHAR2
)
AS
    select_columns  VARCHAR(10000);
    v_sql_stmt      VARCHAR(2000);
BEGIN
  -- 构造动态SQL语句
  v_sql_stmt := 'INSERT INTO ' || v_QUERY_SCHEMA || '.DATA_COPY_STATUS (DO_NAME, TABLE_NAME, STATUS) VALUES (''test'',''test'',''test'')';
  -- 执行动态SQL
  EXECUTE IMMEDIATE v_sql_stmt;
END;
/

三、权限问题说明 #

你当前的报错不是权限导致，但要确保Schema2创建的存储过程能向Schema1的表插入数据，需要注意：

• 权限需要直接授予给Schema2（因为存储过程默认以创建者身份执行），而不是授予给调用者DB1。执行以下语句（在Schema1下执行）：

  GRANT INSERT, UPDATE, DELETE ON DATA_COPY_STATUS TO Schema2;
  

• 如果希望存储过程以调用者（DB1）的身份执行，可以在存储过程定义时加上AUTHID CURRENT_USER，此时需要确保调用者DB1拥有Schema1表的插入权限（你已尝试过授予，这部分是生效的）。修改后的存储过程开头：

  CREATE OR REPLACE PROCEDURE REF_COPY_DB(
  v_QUERY_SCHEMA in VARCHAR2
  ) AUTHID CURRENT_USER
  AS
  ...
  

内容的提问来源于stack exchange，提问作者nick