1

I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower layer. So there is this scenario:

I execute a SP in the upper layer which results in some inserts in both layers but for some reason one of the transactions in the lower layer fails. Is it possible to rollback the whole thing?

Improve this question
7
  • Yes, this works fine. Just make sure there are no commits in any of your stored procedures, or at worst, only in the top-level stored procedures.
    – Colin 't Hart
    Commented Nov 6, 2013 at 8:41
  • @Colin'tHart: Actually this is not a good solution, because I might wanna call a procedure directly from the lowest layer. That procedure, which is an atomic logical operation, must be either committed or rolled back. One (imaginary) solution would be some chaining-like mechanism that you could bound the execution of some transactions to the execution of another. I'm not hopeful if such a feature exists!
    – SJ.Jafari
    Commented Nov 6, 2013 at 8:53
  • 3
    Then, like I said, make sure there are NO commits in your stored procedures and do all the committing outside of them.
    – Colin 't Hart
    Commented Nov 6, 2013 at 9:04
  • I added a "commit_op" parameter to all my SPs which let's the caller decide whether to commit it or not. @Colin'tHart, if you answered as an "Answer" I could accept yours :)
    – SJ.Jafari
    Commented Nov 6, 2013 at 12:16
  • Why add parameter and if statement to each procedure when the caller could simply do proc(); commit; ?
    – Colin 't Hart
    Commented Nov 6, 2013 at 12:29

2 Answers 2

Reset to default
3

yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).

Improve this answer
2

In Oracle schema has nothing to do with transactions. Schema is a namespace and it is assocated with a user:

  • it is the default namespace for this user if she issues an sql statement, e.g select * from customers; selects data from the table named customer of the current schema.
  • she is the owner of the objects of this namespace which means she has some default privileges , e.g. select,insert,update,delete on her's tables.
  • there are system privileges that applies for the default namespace, e.g. create table allows creation of the table in the schema (default namespace of the user)

There is no relation between schema and transactions.

Improve this answer
2
  • 2
    Why the down vote?
    – miracle173
    Commented Nov 6, 2013 at 18:10
  • That's right. Transactions are schema namespace independient. I upvote.
    – vegatripy
    Commented Nov 12, 2013 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.