using commit in pl/sql programs

[mgl70]

Hello,
I am a learner.
do we use commit/rollback in pl/sql programs. If yes , how often we use these for security point of view.

and one more thing I want to know.
can we return more than one value in functions.
as per my knowledge we can't since a function can return only one value.

Please guide me.

Thanks.

 

[johnherman]

Commit and rollback are used as they are needed for transaction concurrency. For instance, if I transfer $100 from my checking account to my savings account, you want to commit or rollback after both transactions, not in between. If you do not understand this concept, please let us know. Commit and Rollback are not security features.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[SantaMufasa]

MGL,

Although the typical behaviour of a function is to return a single value, there are "tricks" you can do to return more than one value if that is an imperative. We typically try to exhaust all avenues of dealing with the problem using a single value, then resort to a multi-value method. Could you offer an example of your case where more than one return value is in order? That way we can tailor the best trick of the trade.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:26 (25Feb05) UTC (aka "GMT" and "Zulu"),
@ 08:26 (25Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[mgl70]

Thanks john. I understand that if we give commit, at the end of the transaction , the changes will be permanent in the database.
Rollback, we can't do once if you give commit.

My question is suppose in any financial applicalication like as you said in banking transactions, how often we give commits.

suppose if there are 2 tables are there . table1 is for checking account and table2 is for savings account.
I want to transfer $100 from table1 to table 2.
Then we need 2 updates.
where do you put commit? after each update or at the end of the transaction.

I heard like financial applications need commits frequently since there are many records in tables.

For how many records we give commits?.

 

[mgl70]

hi Mufasa,
Thanks. This is an interview question. I could not figured out in any books also.

I did one time returning more than one string by using CHAR() in between those string in ref cursor.
Is this the way we have to do?

I have not much knowledge.
Thanks.

 

[carp]

The basic rule I go by is you commit as often as you need to, but no more. Commits do bring a certain amount of processing overhead with them so you don't want to do them any more often then necessary.

From a business standpoint, the same holds true.

In your example, you do not want to commit until the end of the transaction. That is what a commit does - it marks the end of a transaction.

Suppose I'm transferring $1000 from your checking to your savings. If I deduct $1000 from your checking account, commit, and then the system crashes you will be shorted $1000.

If I decide to go the other way, add $1000 to savings, commit, and the system crashes, the bank is out $1000.

If I deduct $1000 from savings, DON'T commit and the system crashes, when the database is brought back up it will be like the transaction never happened. So, although you need to reattempt the transfer, at least nobody has lost any money.

Definitely wait until the end of the transaction.

 

[dbomrrsm]

a useful sumation of what is a transaction

To appreciate transactions in Oracle, consider the "ACID" principle: a transaction has atomicity, consistency, isolation, and durability, which are defined as follows:

Atomic
A transaction's changes to a state are atomic: either they all happen or none happens.

Consistent
A transaction is a correct transformation of state. The actions taken as a group do not violate any integrity constraints associated with that state.

Isolated
Even though many transactions may be executing concurrently, from any given transaction's point of view, other transactions appear to have executed before or after its execution.

Durable
Once a transaction completes successfully, the changes to the state are made permanent, and they survive any subsequent failures.

understanding what a transaction is will help you understand at what point to commit


[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]