bound variables using oracle parameters-CodePudding

Looking for possibility to bound oracle parameters inside the execute immediate statement. But right now facing with issue that I couldn't execute multiple queries inside one statement because of Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01008: not all variables bound exception. After researching I've no clue if is possible to do that.

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

Is there any way to do that or should I implement of using multiple command statement and execute them consistently without 'execute immediate'

UPDATE 1 Added using statement and it worked but now experiencing another issue, I've added one more statement inside oracle command to update table from temporary, but faced with another issue with: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-32462: cannot use an object modified in current transaction, the updated statement with commands below:

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;"
};

CodePudding user response：

The error ORA-32462 as described [here][1] simple means that the table USERS contains uncommited changes and you can not use it to create a PRIVATE TEMPORARY TABLE with CTAS

create PRIVATE TEMPORARY TABLE ORA$PTT_USERSTMP as select * from USERS where 1=0;

triggers

ORA-32462: cannot use an object modified in current transaction

so you must either commit the transaction before the creation if the PTT or if not feasible split the creation in create table and insert

Simplified example

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;
...

Btw you may define the PTT with ON COMMIT DROP DEFINITION which could eliminate the need of excplicite DROP [1]: ORA-32462: cannot use an object modified in current transaction