Oracle子查询关联私有临时表执行插入/建表报ORA-00942错误

使用临时表的初衷：C#应用中存在数千个数字ID，同时有多张源表与一张目标表，需要按照「每个ID对应每条源表记录生成一条目标记录」的规则向目标表插入数据，全外连接是适配该场景的最优方案。

目前存在无需临时表的替代方案：使用SYS.ODCINUMBERLIST类型传参，但该类型单次最多支持999个条目，需要分多轮执行，性能极差。因此原本计划先将ID批量写入临时表，后续所有插入操作仅需关联一次临时表即可完成，简化流程同时提升性能；另外如果处理的是字符串类型而非数字类型，也需要先写入表才能正常使用预编译语句参数。

并非必须采用该方案，但该异常现象十分反常，因此希望探明根本原因。实际上，「先填充临时表再做关联查询」的方案在应用其他场景已经正常运行，那些场景仅用临时表做SELECT查询不做更新操作，可以正常关联临时表返回结果。

执行INSERT和CREATE语句时遇到了一个奇怪的限制，更反常的是该异常仅在C#应用中出现，在SQL Developer中执行相同语句无任何报错。

Oracle数据库报私有临时表不存在错误（ORA-00942: 表或视图不存在），仅当同时满足两个条件时触发：

• 私有临时表与普通表在语句中做关联
• 关联结果用于INSERT写入其他表、或CREATE新建表

环境说明：数据库为Oracle 19c，测试过Oracle.ManagedDataAccess包19.14.0和19.15.1两个版本，异常复现结果一致。

以下为简化后的复现用例，仅展示失败和成功的最简场景，不代表实际业务逻辑，以下测试用例会触发前述ORA-00942错误：

[TestMethod]
public void InsertTest() {
    var noParams = new List<OracleParameter>(0);

    // 开启事务保证使用同一会话，确保可以访问临时表
    using (var scope = new TransactionScope()) {
        var sql = "CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (temp_id number) ON COMMIT DROP DEFINITION";
        ExecuteNoReturnValueSql(sql, noParams);

        // 此处可插入数据，即使不插入数据后续语句也会报错

        sql = @"
insert into target_table (col1, temp_id)
  select et.col1, temp.temp_id
  from ora$ptt_temp temp
  full outer join example_table et on 1=1";

        ExecuteNoReturnValueSql(sql, noParams);
        scope.Complete();
    }
}

反常点在于：上述语句在SQL Developer中执行无任何报错。ExecuteNoReturnValueSql是经过充分验证的方法，后续会附上其核心源码。手动开启事务依次执行建表（成功）、关联SELECT查询（成功）、关联INSERT（失败）操作的执行日志已做隐私脱敏。

• 首先排除连接池影响：即使禁用连接池、两次数据库调用复用同一个OracleConnection对象，错误仍会复现；且只要不执行INSERT操作，同会话下临时表关联查询是可以正常运行的。
• 其次排除临时表不支持关联的可能：去掉INSERT部分仅执行SELECT关联查询可以正常返回结果，也在应用其他场景正常使用过临时表关联查询，这也是选择该方案的原因。

单独执行关联SELECT的语句如下，可以正常运行：

select et.col1, temp.temp_id
from ora$ptt_temp temp
full outer join example_table et on 1=1

测试CTE（WITH）语法改写，仍无法解决问题：

insert into target_table (col1, temp_id)
with temp as (
  select *
  from ora$ptt_temp
  full outer join example_table on 1=1)
  select '', 0 from temp

最初猜测私有临时表不支持INSERT场景，于是测试用CTAS语法基于关联结果新建私有临时表，同样触发相同错误：

CREATE PRIVATE TEMPORARY TABLE other ON COMMIT DROP DEFINITION AS (
  select et.col1, temp.temp_id
  from ora$ptt_temp temp
  full outer join example_table et on 1=1)

后续测试发现私有临时表并非完全不能用于DML操作，以下仅查询临时表不关联普通表的INSERT语句可以正常执行：

insert into target_table (col1, temp_id)
  select '', temp.temp_id
  from ora$ptt_temp temp

仅查询普通表不关联临时表的INSERT语句也可正常执行，确认example_table确实存在：

insert into target_table (col1, temp_id)
  select et.col1, 0
  from example_table et

无法将建表和INSERT合并为单条语句执行，原因有二：

1. 语句编译阶段无法引用尚未创建的表名
2. PL/SQL块内直接执行DDL会触发语法错误，报错“PLS-00103: Encountered the symbol "CREATE" when expecting one of the following”：

BEGIN
  CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (temp_id number) ON COMMIT DROP DEFINITION;
  insert into target_table (col1, temp_id)
    select et.col1, temp.temp_id
    from ora$ptt_temp temp
    full outer join example_table et on 1=1;
END;

目前可以选择其他方案绕开临时表，但非常希望探明该异常的根本原因。

该通用方法已经过充分测试，以下为核心源码（省略错误处理逻辑）：

public static int ExecuteNoReturnValueSql(string sql, List<OracleParameter> parameters) {
    using (var connection = new OracleConnection(_defaultConnectionString.ConnectionString))
    using (var command = new OracleCommand(sql, connection)) {
        command.CommandType = CommandType.Text;
        foreach (var oracleParameter in parameters) {
            command.Parameters.Add(oracleParameter);
        }

        command.Connection.Open();
        return command.ExecuteNonQuery();
    }
}

以下为简化后的补充测试用例：使用单个连接、手动开启事务、SQL语句无换行，验证了关联SELECT可以正常执行、仅关联INSERT会失败，错误发生在第三次RunCommand调用时，而非第二次：

[TestMethod]
public void Test2() {
    var connectionString = _defaultConnectionString.ConnectionString.Replace("Pooling=true", "Pooling=false");
    using (var connection = new OracleConnection(connectionString)) {
        connection.Open();
        using (var transaction = connection.BeginTransaction()) {

            var sql = "CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (temp_id number) ON COMMIT DROP DEFINITION";
            RunCommand(connection, sql); // 执行成功

            sql = "select 1 from PROGRAM_PLUGIN full outer join ora$ptt_temp on 1=1";
            RunCommand(connection, sql); // 执行成功

            sql = "insert into PLUGIN_STATISTICS (PLUGIN_ID, CUST_CODE, CURRENT_STATUS) select '', 0, 2 from PROGRAM_PLUGIN full outer join ora$ptt_temp on 1=1";
            RunCommand(connection, sql); // 执行失败

            transaction.Commit();
        }
    }
}

private int RunCommand(OracleConnection connection, string sql) {
    using (var command = new OracleCommand(sql, connection)) {
        command.CommandType = CommandType.Text;
        return command.ExecuteNonQuery();
    }
}

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