Forum Discussion

Szymon's avatar
Szymon
New Contributor II
5 years ago
Solved

Oracle Insert - multiple rows in transaction possible?

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's avatar
    igormicev
    5 years ago

    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

4 Replies

  • Szymon's avatar
    Szymon
    New Contributor II
    5 years ago

    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.

  • igormicev's avatar
    igormicev
    Contributor
    5 years ago

    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's avatar
      Szymon
      New Contributor II
      5 years ago

      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
  • Raviteja's avatar
    Raviteja
    New Contributor
    2 years ago

    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?