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();
}
}
CodePudding user response：
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.
CodePudding user response：
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;
/
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.