OracleBulkCopy AutoCommits

c# odp.net oracle sqlbulkcopy

Question

C#.Net I am using OracleBulkCopy to copy data to global temp tables that are declared as Delete on commit. So when I use OracleBulkCopy.WriteToServer(DataReader). It commits and I am losing all the data. How to prevent this?

Accepted Answer

OracleBulkCopy seems not to support transactions. Auto commit is never smart to use, copy in a more controlled way for example using bulk inserts.

OPEN z;
LOOP
    FETCH z BULK COLLECT INTO z_array LIMIT z_array_size;

    FORALL i IN 1..z_array.COUNT
    INSERT INTO t2 VALUES z_array(i);

    EXIT WHEN z%NOTFOUND;
END LOOP;

Popular Answer

It is no good idea to declare global temporary tables as Delete on commit when using .NET anyway.

Better practice is to delete from gtt before doing whatever you want and let it as it is after commit. Debugging becomes easier too.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why