| Best method to call the package which one is update the details table [message #441407] |
Sun, 31 January 2010 19:12  |
arunn
Messages: 52
Registered: September 2008
Location: usa
|
Member |
|
|
Hi Friends,
Hope you guys are doing good.
Environment: Oracle forms 6i, oracle db 9i, 10g, windows nt
1. i want to update morethan 10 to 20 detail tables data(probably its change based on the row like 20000 to 100000 rows), based one update in the master table.
In this case, 50% tables have relationship with master, remaining have relationship but its dont have directly.
i am going to update the masters table in the forms based business logic.
Note: have package to update the details record too.
herewego, have some methods to call update package:-
1. Call the package from forms directly, just before the master table update transacation are going to commit.
( in this, i have check to do the commit for master table update based on the package success or fail. so i can control the master update transaction like allow to commit if the package is success or allow to rollback if the package is fails. )
2. write the trigger for update master table and call the update package from that trigger.
i have consider the above 2 ways:-
My questions:-
1. Is there any good methods to handle this?
2. which is best method in the above 2 methods in the following case like control the transaction, performance wise, future enhancement, future enhancement cost saving, working hour, ro bust - reliable?
3. How it will work if the best method is best?
Thanks in Advance,
|
|
|
|
| Re: Best method to call the package which one is update the details table [message #441486 is a reply to message #441407] |
Mon, 01 February 2010 07:04  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
First off, If you're having to update 20-100 thousand rows across 10-20 tables based on an update to the master table my default assumption would be that there is a serious flaw in your data model.
What exactly are you updating and why?
If you call this package from either a db trigger on the master table or a pre-insert/update trigger on the corresponding block in the form then there should be no difference in terms of transactional control or performance.
Since the package will be doing all the slow work where you call it from isn't going to affect how long it takes. And if the package fails the update to the master table will fail in either case (as long as you don't trap the exception in the package). You should never need to check if the package succeeded since you'll get an error if it fails.
The only real consideration here (apart from whether or not you should be doing these updates at all) is maintainability. If you put it in a db trigger then it'll always run, even if someone creates a new form that updates the same master table. But code in triggers tends to get forgotten about and overlooked and you could end up with people spending hours trying to work out why modifying data in the form takes so long because they don't realise the db trigger is there.
Code in the form is easier to spot but you could get the situation where someone creates a new form that updates the same table but forgets to include the call to the package.
Which is best for you probably depends on how you document your code.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
