0

I've a very minimalistic script that does not work. 

SET TRANSACTION NAME 'anyname';
DELETE FROM V_RECHNUNG WHERE recno=207;
INSERT INTO V_RECHNUNG (recno) VALUES (207);
ROLLBACK;
SELECT * FROM V_RECHNUNG ORDER BY RECNO DESC;

I don't understand why the select statement get an entry with recno 207. In my opinion entry with recno 207 should not exist at all.

Important: I checked that recno 207 did not exist before i run this script.

IS there any oracle server configuration that disables transactions?

Sid M
  • 4,354
  • 4
  • 30
  • 50
Benjamin Eckstein
  • 884
  • 2
  • 9
  • 19
  • 3
    What tool are you using to run this script? Any chance you've configured that tool to autocommit statements it issues? – Justin Cave Jul 16 '14 at 15:44
  • i am using a raw php script as well as php adminer to test statements. – Benjamin Eckstein Jul 16 '14 at 15:50
  • 4
    Then the problem is almost certainly in your PHP script. If you run this script in a vanilla SQL*Plus client, your rollback would revert both the `DELETE` and the `INSERT`. Most likely, your PHP script is running in autocommit mode. – Justin Cave Jul 16 '14 at 15:52
  • 1
    Are you using OCI? If you are you'll want to specify [`OCI_NO_AUTO_COMMIT`](http://php.net/manual/en/function.oci-execute.php) – Conrad Frix Jul 16 '14 at 16:06
  • use an anonymous block instead to discard auto commit – Sebas Jul 16 '14 at 20:53

1 Answers1

1

If your table all ready had an entry for recno 207 before you had done any inserts, or any deletes, then it would make sense :)

Think About it.

You have say the following records:

Point 1: 204, 205, 206, 207, 208

Point 2: You issued the Delete Statement for 207, Hence, you're left with 204, 205, 206, 208

Point 3: You issues the Insert Statement for 207, Hence, you're left with 204, 205, 206, 207, 208

Point 4: You issued a rollback, which will take you back to Point 1.. Ie: you had 207 from the start.

Panayiotis Savva
  • 134
  • 8