尋找在execute immediate陳述句中系結 oracle 引數的可能性。但是現在面臨的問題是我無法在一個陳述句中執行多個查詢，因為Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01008: not all variables bound exception. 經過研究，我不知道是否有可能做到這一點。
var cmd = new OracleCommand
{
CommandText = "begin "
$" execute immediate 'CREATE PRIVATE TEMPORARY TABLE ORA$PTT_USERSTMP AS SELECT {string.Join(',', columnNames)} from USERS WHERE 1=0';"
$" execute immediate 'INSERT INTO ORA$PTT_USERSTMP ({string.Join(',', columnNames)}) VALUES ({string.Join(',', columnNames.Select((c, index) => $":{index 1}"))})';"
"end;"
};
cmd.ArrayBindCount = valuesToInsert.First().Count();
foreach (var value in valuesToInsert)
{
cmd.Parameters.Add(new OracleParameter { OracleDbType = GetType(value), Value = value });
}
await cmd.ExecuteNonQueryAsync();
有沒有辦法做到這一點，或者我應該實作使用多個命令陳述句并在沒有“立即執行”的情況下一致地執行它們
更新 1
添加了using陳述句并且它起作用了，但現在遇到了另一個問題，我在 oracle 命令中添加了一個陳述句來從臨時更新表，但面臨另一個問題：Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-32462: cannot use an object modified in current transaction，更新陳述句的命令如下：
var cmd = new OracleCommand
{
CommandText = "begin "
$" execute immediate 'CREATE PRIVATE TEMPORARY TABLE ORA$PTT_USERSTMP AS SELECT {string.Join(',', columnNames)} from USERS WHERE 1=0';"
$" execute immediate 'INSERT INTO ORA$PTT_USERSTMP ({string.Join(',', columnNames)}) VALUES ({string.Join(',', columnNames.Select((c, index) => $":{index 1}"))})' using {string.Join(',', columnNames.Select((c, index) => $":{index 1}"))};"
$" execute immediate 'UPDATE USERS2 t1 SET ({string.Join(',', columnNames.Where(s => s != idColumnName).Select(s => "t1." s))}) = (SELECT {string.Join(',', columnNames.Where(s => s != idColumnName).Select(s => "t2." s))} FROM ORA$PTT_USERSTMP t2 WHERE t1.{idColumnName} = t2.{idColumnName})';"
" execute immediate 'DROP TABLE ORA$PTT_USERSTMP';"
"end;"
};
uj5u.com熱心網友回復：
[此處][1] 中描述的錯誤 ORA-32462 簡單意味著該表USERS包含未提交的更改，您不能使用它來創建PRIVATE TEMPORARY TABLE帶有 CTAS 的
create PRIVATE TEMPORARY TABLE ORA$PTT_USERSTMP as select * from USERS where 1=0;
觸發器
ORA-32462: cannot use an object modified in current transaction
所以你必須commit在創建之前進行交易，如果PTT或者如果不可行，將創建拆分為create table和insert
簡化示例
begin
execute immediate 'create PRIVATE TEMPORARY TABLE ORA$PTT_USERSTMP (id int, col int)';
execute immediate 'insert into ORA$PTT_USERSTMP(id,col) values(:1,:2)' using 1, 999;
...
順便說一句，你可以定義PTT與ON COMMIT DROP DEFINITION可以消除excplicite的需要DROP
[1]：ORA-32462：無法使用物件修改當前事務
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/313593.html
上一篇：無法確定序列化資訊