How do I use transaction with oracle SQL?

Question

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.

Answer 1

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