CODEWITHSUNDEEP
sqloracle-databasetransactions
Vortilion
Vortilion

Reputation: 424

How do I use transaction with oracle SQL?

I am trying to use transaction blocks on a SQL-Console with an Oracle DB. I'm used to use transaxction blocks in PostgreSQL like

BEGIN;
<simple sql statement>
END;

but in oracle it seems that this is not possible. I'm always getting "ORA-00900" errors and I don't know how to fix that. If I just use SQL-Statements like

<simple sql statement>
COMMIT;

it works. But isn't there some tag to define the start of a transaction? I tried 

START TRANSACTION;
<simple sql statement>
COMMIT;

But it still throws an ORA-00900. My operating system is windows, I am using IntelliJ IDEA and a Oracle 11g DB.

Upvotes: 12

Views: 57592

Answers (2)

kevinskio
kevinskio

Reputation: 4551

You can have an implicit transaction block by issuing one SQL statement as in

<simple sql statement>
Commit;

For anonymous blocks or PL/SQL procedures/functions/packages more options are available that you may have seen in Postgres.

If you have several statements that must all succeed or all fall (an atomic transaction then, from the documentation, you can do:

DECLARE
   <variable declaration>
BEGIN
   <simple sql statement>
   <simple sql statement>
   <simple sql statement>
   SAVEPOINT do_insert;
   <sql insert statement>
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO do_insert;
      DBMS_OUTPUT.PUT_LINE('Insert has been rolled back');
END;
--and commit outside the transaction

Upvotes: 13

Husqvik
Husqvik

Reputation: 5809

Normal, read committed transaction, starts automatically with the first modified row.

If you want to set the transaction explicitly use:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED - but the transaction will be physically created when first row is modified, not when this statement is executed.

or

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE - in this case read consistency will be as of this command is executed. READ ONLY transaction has the same read consistency effect.

Upvotes: 1

Related Questions