Why does Oracle say this temporary table does not exist when it's used in an insert subquery?

Question

Reason

First, I will explain why I want to use a temporary table. To oversimplify a bit: I have a list of thousands of numeric IDs in my C# application, and I have several source tables and a target table. I want to insert one record per ID per source table record into the target table. Naturally, this is a good case for a full outer join.

There are ways for me to work around the need for a temporary table, by using SYS.ODCINUMBERLIST (since it can only contain 999 items, each such statement would need to be executed multiple times, ew), but I wanted to use SYS.ODCINUMBERLIST to insert into a temporary table and then join on that only once, for each of the inserts, to simplify this process. Doing them one at a time is absurdly slow. Besides, if I were dealing with strings and not numbers, I'm pretty sure I'd have to insert into a table first anyway to make use of prepared statement parameters.

I don't have to do it this way, but this problem is now intriguing enough that I must ask why it's happening.

This technique of populating a temporary table then using it in a join has worked in other places in my application, where I'm using the temporary tables only in SELECT statements and not trying to update anything. I can join on a temporary table and read the results.

Problem

I've come across a strange limitation regarding insert and create statements. Stranger still, it only occurs in my C# application and not through SQL Developer.

The Oracle database claims that the private temporary table does not exist (ORA-00942: table or view does not exist), but only when 1) in a join statement with a normal table and 2) being used to insert into or create another table.

In case it matters, we have Oracle Database version 19c. I tried it with Oracle.ManagedDataAccess package versions 19.14.0 and 19.15.1, with the same result.

Now for examples. I'm greatly simplifying the code here to show degenerate cases that fail and succeed; these are not representative of what I actually want to do in my application.

This test fails, with the aforementioned error, ORA-00942: table or view does not exist.

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

            // A transaction is used to ensure we use a single session, ensuring access to the temporary table.
            using (var scope = new TransactionScope()) {
                var sql = "CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (temp_id number) ON COMMIT DROP DEFINITION";
                ExecuteNoReturnValueSql(sql, noParams);

                // [...] some data could be inserted here, but it fails even if not [...]

                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();
            }
        }

Here's the weird part. I get no error when running these statements in SQL Developer. ExecuteNoReturnValueSql is a properly-working function, and I'll include most of its source later in the question.

Here's a log where I opened a manual transaction (just to be sure!), ran the CREATE (succeeded), a SELECT (succeeded), and an INSERT (failed), the latter two using the same join. Personal data redacted. https://gist.github.com/Corrodias/d8a877d77a5a98f7d47cfa44b40a85b3

What I've tried

You might think it's because of connection pooling, but even if I disable pooling and re-use the same OracleConnection object for both calls, this error still happens. Besides, as long as I'm not trying to insert, this technique does work.

You might think temporary tables can't be joined on, but they can. It succeeds if you remove the "insert" line and just do the select on its own. I've also used this technique elsewhere in the application, which is why I wanted to use it here.

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

I tried the "with" syntax, just in case, to no avail.

  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

I thought maybe you just couldn't use them in inserts, so I tried to create another table using it as the source, and that failed as well. This fails with the same error.

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)

I thought maybe they couldn't be used at all in any update operation... but they can! This works.

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

And of course this works, too. My example_table does exist, after all.

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

I cannot execute them in a single statement, for two reasons. 1) You can't refer to a table name that doesn't exist yet when the command is compiled, and 2) Oracle doesn't seem to like my syntax. "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;

At this point, I'm willing to accept that I've run into a situation where I might just have to avoid using the temporary table, but now I have to know: what is going on, here?

ExecuteNoReturnValueSql

This simple function has been thoroughly tested, but here's some of its source (error handling omitted).

        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();
            }
        }

An additional test

Here is the latest version of an alternate, simplified test that uses a single connection, a manual transaction, and no line breaks, and demonstrates that you can join the tables in a select, just not in an insert. Indeed, the test fails on the third call to RunCommand, not the second.

        [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); // success

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

                    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); // failure

                    transaction.Commit();
                }
            }
        }

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

Answer 1

Minimal example that reproduces the exception ORA-00942: table or view does not exist

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (temp_id number) ON COMMIT DROP DEFINITION;

-- check the current used session
select SID, SERIAL# from v$session where sid in (select sid from v$mystat);

       SID    SERIAL#
---------- ----------
       279      10541

select count(*) from ora$ptt_temp;

  COUNT(*)
----------
         0

--  reconnect the session (sid or serial will change)
select SID, SERIAL# from v$session where sid in (select sid from v$mystat);

       SID    SERIAL#
---------- ----------
       279      28141

select * from ora$ptt_temp;

ORA-00942: table or view does not exist

Note that this would explain your first setup where you gets for every execution a new connection with new OracleConnection.

Your second setup uses a same connection so the most probable explanation is there was a commit issued.

You may verify it by switching to ON COMMIT PRESERVE DEFINITION or checking the 10046 trace or querying the V$TRANSACTION as in the example below.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (temp_id number) ON COMMIT DROP DEFINITION;

-- check the transaction start time
select t.START_TIME from   v$transaction t join v$session s on t.addr = s.taddr
where s.sid in (select sid from v$mystat);

START_TIME          
--------------------
06/19/22 19:09:08


-- somethething is triggering a (auto)commit...
commit;

select * from ora$ptt_temp;

ORA-00942: table or view does not exist

select t.START_TIME from   v$transaction t join v$session s on t.addr = s.taddr
where s.sid in (select sid from v$mystat);

no rows selected 

And yes, this is not an answer, but it should provide you hints how to resolve the problem.

Answer 2

Too long for a comment... the hex dump shows the parse sending CRLF (0D 0A) as part of the SQL; whether that is being preserved or added is unclear.

2022-06-17 19:04:20.422222 TID:12  (NET) (SND) 20 20 66 72 6F 6D 20 6F   |  from o|
2022-06-17 19:04:20.422222 TID:12  (NET) (SND) 72 61 24 70 74 74 5F 74   |ra$ptt_t|
2022-06-17 19:04:20.422222 TID:12  (NET) (SND) 65 6D 70 0D 0A 20 20 20   |emp..   |
2022-06-17 19:04:20.422222 TID:12  (NET) (SND) 20 66 75 6C 6C 20 6F 75   | full ou|
2022-06-17 19:04:20.422222 TID:12  (NET) (SND) 74 65 72 20 6A 6F 69 6E   |ter join|

which isn't happening for the select.

You get the same error if you force those characters into a dynamic command:

begin
  execute immediate 'select * from dual' || char(13)||chr(10)
    || ' where 1=1';
end;
/

db<>fiddle

I don't know why but you could experiment with adding space after the name. Might also be interesting to repeat that with a non-PTT with the same spacing (or lack of) between a table name and a join.

Answer 3

Change your PL/SQL block to:

BEGIN
  EXECUTE IMMEDIATE 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (temp_id number) ON COMMIT DROP DEFINITION';

  EXECUTE IMMEDIATE '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;

As was mentioned in a comment, DDL statements cannot be executed in a PL/SQL block unless they're part of an EXECUTE IMMEDIATE statement. Another thing to think about is that all objects mentioned in SQL statements that are part of a PL/SQL block must exist when the PL/SQL block is compiled (just before it's executed, in this case) and in this case the table ORA$PTT_TEMP doesn't exist before the CREATE PRIVATE TEMPORARY TABLE statement is executed. Because of the requirement that objects must exist at compile time, the INSERT statement must also be executed using an EXECUTE IMMEDIATE statement.