Insert multiple rows

[Endorphin]

I'm using an Oracle database, with all the code to connect, insert, delete etc written in C#.

I need to be able to insert multiple rows at once, however if one row fails to be inserted then all of the rows inserted, or to be inserted must fail.

Is this possible in a standard command?

Or would I have to write some code which deletes the already inserted rows after an error occurs?

[davide++]

Hi all.

Oracle is a transactional database; a transaction is a group of SQL command (INSERT, UPDATE or DELETE) that you can confirm (COMMIT) or cancel (ROLLBACK); when you commit a transaction all database changes are effective, when you rollback a transaction all database command are canceled and your database returns where it was before starting the transaction.
So, when an error occours you have to do ROLLBACK, if there are no error you can confirm all changes performing COMMIT.

[Endorphin]

So will this transaction have to be written in PL/SQL?

I ask this because I have found a method using TRY CATCH blocks, but this seems to be only available in T-SQL, which Oracle does not support. (?)

If I have to write this in PL/SQL, is there any support for TRY CATCH statements?

[davide++]

No.

COMMIT and ROLLBACK are Oracle SQL command, TRY and CATCH are C# instructions.

I think you're writing a C# program with instructions that perform database operations, may be using strings that contain the SQL command (INSERT, UPDATE) that are executed by a special C# instruction, or using other techinques (doesn't matter); well, you can put all database accesses within a TRY block that ends with the SQL COMMIT command, and put the ROLLBACK command in the CATCH block. So, if your code performs correctly all database operations in the TRY block COMMIT will confirm changes, instead if an error occours the execution jumps to CHATCh block and all changes are canceled.

I hope this will help you.

[Endorphin]

Yeah has helped alot. Cheers!