时间戳

首页 > 解决方案 > 我们能否找到导致 ORA-00942 表或视图不存在的表：

oracle - 我们能否找到导致 ORA-00942 表或视图不存在的表：

问题描述

我有一个带有 CTAS 查询的普通 PLSQL 块。

BEGIN
EXECUTE IMMEDIATE'
CREATE TABLE ZZZ_TEMP NOLOGGING PARALLEL AS
SELECT /*+ PARALLEL(a,4) */
        *
    FROM
        HI0XXX001.HI_TABLE_NAME a
    LEFT JOIN
        HR_001_GROUP B
    ON
        a.EMPLOYERGROUP_UD =B.ACC_NUM
    LEFT JOIN
        HR_002_GROUP l
    ON
        a.EMPLOYERGROUP_UD =l.EMPLOYERGROUP_UD
    AND
        a.GRP_CON_UD_COV_CONTRACT_NAME=l.GRP_CON_UD_COV_CONTRACT_NAME
    ';
EXCEPTION WHEN OTHERS THEN Dbms_Output.put_line(SQLERRM);
END;

假设 HR_002_GROUP 表在我运行此块的架构中不存在。有没有办法显示 HR_002_GROUP 表不存在的错误消息？

我发布的查询只是一个示例场景。我们在 ETL 结构中工作，我们有一大行查询脚本，将数百万个数据从许多 HI 表中的一个插入到另一个带有前缀 VH 的小表中。我在左连接中有大约 15-30 个表。选择每个左连接以了解哪个表实际导致错误非常耗时。

标签： oracleplsqlerror-handlingdynamic-sql

解决方案

使用DBMS_SQL而不是原生动态 SQL，然后使用DBMS_SQL.LAST_ERROR_POSITION()来标识语句中出现错误的部分。

DBMS_SQL不如原生动态 SQL 方便，但也更强大。有几个需要注意的问题。第一个是程序DBMS_SQL.PARSE不仅会解析命令，还会自动运行 DDL 命令。

下面的代码尝试使用另外两个表创建一个表，其中一个显然不存在。正则表达式用于输出从错误位置到第一个空格的每个字符。（此代码基于此答案。）

declare
    v_cursor_id integer := dbms_sql.open_cursor ();
    v_sql varchar2(32767) := q'[
        create table zzz_temp as
        select *
        from dual
        join some_schema.fake_table
            on dual.dummy = fake_table.dummy
    ]';
begin
    dbms_sql.parse(v_cursor_id, v_sql, dbms_sql.native);
    dbms_sql.close_cursor(v_cursor_id);
exception
    when others then
        dbms_output.put_line
        (
            sqlerrm||chr(10)||
            'Error occurred here: '||
            regexp_substr
            (
                srcstr   => v_sql,
                pattern  => '\w+',
                position => dbms_sql.last_error_position()
            )
        );
        dbms_sql.close_cursor(v_cursor_id);
end;
/

DBMS_OUTPUT：

ORA-00942: table or view does not exist
Error occurred here: fake_table

其他一些随机代码建议：

  1. 使用PARALLEL(4)而不是PARALLEL(A, 4). 语句级并行性几乎总是优于对象级并行性。如果您并行运行一个表，您可能希望并行运行整个查询。
  2. 尽量避免WHEN OTHERS。如果可能，请使用更具体的错误处理程序。

推荐阅读