Oracle Insert - multiple rows in transaction possible?

Designing Pipelines
#1

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

#2

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 :slight_smile:
    But, maybe you’re seeking this option in a snap (Oracle - Bulk Load):

image
image979×444 22 KB

/Igor

#3

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 :laughing:
    cheers
    Simon
#4

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.

1 Like