Oracle Insert - multiple rows in transaction possible?

[Szymon]

Hi,

How to insert multiple rows at a time into Oracle table within one transaction?
Example:
Reading 10 lines from CSV/Excel file.
Insert 10 lines into Oracle using Oracle insert.
When one of the lines throws an error, none of the lines shall be written.
A classical rollback mechanism.
How to do that?

kind regards
Simon

[igormicev · Accepted solution]

Hi @Szymon,

1. The harder way:
   If it’s literally 10, 20, or even 100s of rows you can dynamically build an INSERT INTO … VALUES()… pl/sql string and execute it with the Oracle - Execute snap. But if it’s thousands oF rows or even more, then you can store the temporary data into a temp table and then apply the BEGIN … COMMIT/ROLLBACK again with an Oracle - Execute snap.

2. The easier way 🙂
   But, maybe you’re seeking this option in a snap (Oracle - Bulk Load):

/Igor

[Szymon]

Thank you Igor.

1. yes, thought of that, but i would like to avoid execute snap, since this is just a kind of error prone, if i need to build insert statements manually.
2. saw this option too, right now the sqlldr does not work properly, so cannot say, if that is an option.
   Again, thank you very much for confirming the options.
   I almost thought i might have missed some useful feature 😆
   cheers
   Simon

[Szymon]

Igor,
thank you again. I have the .exe now and it seems like the setup you mentioned works.
You need to set 0(zero) in max. error count for that to work. Docs says nothing about it.

Now i will test it witl large batches.

cheers
Simon

PS. In INSERT mode, it says - table not empty, even if table is empty, but i will do another post about it.

[Raviteja]

Hi,

I'm trying to use oracle bulk insert. In the snap I see SQLLDR setting. what does that mean where should I get that exe file? Could anyone help on this?