Home » SQL & PL/SQL » SQL & PL/SQL » I have a packaged procedure
I have a packaged procedure [message #435786] Fri, 18 December 2009 00:53 Go to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
I have a packaged procedure which inserts data into a table.
Question is where should we put commit?
1> In the procedure body or,
2> When we are calling that procedure, lets suppose are calling it in an anonymous block?
its a confusion as in the book, I cant find commit anywhere.
Re: I have a packaged procedure [message #435793 is a reply to message #435786] Fri, 18 December 2009 01:03 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Certainly not 1.
Possibly 2 depending upon circumstances.
Possibly 3 in the application code depending upon circumstances.

[Updated on: Fri, 18 December 2009 01:05]

Report message to a moderator

Re: I have a packaged procedure [message #435795 is a reply to message #435793] Fri, 18 December 2009 01:17 Go to previous messageGo to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
thank you
Re: I have a packaged procedure [message #435911 is a reply to message #435786] Fri, 18 December 2009 21:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
in most cases, the place where you start your transaction is the place where you should put your commit.

It should be a rarity for a commit to be found in a piece of plsql. Usually only controler programs have them and even then, controler pieces of code often do not because they wish to leave the decision of commit/rollback to the piece of code that called them.

Where to put a commit? = What is transaction you want to create?

Good luck, Kevin
Re: I have a packaged procedure [message #437187 is a reply to message #435786] Thu, 31 December 2009 15:44 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

You should commit after the insert statement in your procedure.

Re: I have a packaged procedure [message #437200 is a reply to message #437187] Thu, 31 December 2009 23:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Will you please justify your answer?

regards,
Delna
Re: I have a packaged procedure [message #437205 is a reply to message #437200] Fri, 01 January 2010 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What, would you rather commit BEFORE insert?

I suppose that committing AFTER insert is perfectly OK. If you think of the process that way, of course (before/after, I mean).
Re: I have a packaged procedure [message #437267 is a reply to message #435786] Sat, 02 January 2010 15:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
All answers given here are potentially correct. However, the only right answer is the one I gave. Let me state it again as it seems I was not clear enough.

COMMIT = END OF A TRANSACTION

You put your commit where it is you intend to end a transaction.

99 times out of 100, where you start your transaction is where you end it and thus where the commit goes. That means #2 given your original choices.

You could put the commit after the insert, if you want every insert to be its own transaction. But if your processes are like most everyone elses, that is a bad idea if only because it would be massively expensive. Commit is an expensive operation. Committing after every insert would cause significant overhead. How much overhead depends upon how many inserts you are doing but with a lot of inserts, 100 to 1000 times more work than if you commit only once at the end of all your inserts is likely.

But more importantly,

COMMIT = END OF A TRANSACTION

You put your commit where you want to end your transaction. This suggests of course that you actually have some idea of what your transaction should look like. DO YOU?

Again, in most cases, where you start your transaction will be where you end it and thus where you put the commit. If you start in an autonomous block, then you commit in the autonomous block. If you start in Oracle Forms, then you commit from Oracle Forms. If you insert from a java app, then you commit in the java app.

If you put the commit inside the plsql routine that does the insert, then that commit will cause and END OF TRANSACTION and START OF NEW TRANSACTION for every routine that uses it. That means these routines no longer have control of the definition of their transaction any more. And this may be the biggest concern for where commit goes. When you hard code a commit, you make your piece of code no longer capable of being reused as part of a larger transaction and if other routines decide to use your code with a commit in it and do not know that the commit exists, then their code will eventually produce half done transactions and they won't know why and when they find out will be very upset with you.

So remember this,

COMMIT = END OF A TRANSACTION

You put your commit where you want to end your transaction.

In most cases, where you start your transaction will be where you end it and thus where you put the commit.

This suggests of course that you actually have some idea of what your transaction should look like. DO YOU?

So tell us, what does your transaction look like? How do you want it to end? How much work should it contain? What is its definition?

Kevin
Re: I have a packaged procedure [message #437472 is a reply to message #437200] Mon, 04 January 2010 10:48 Go to previous messageGo to next message
syed.nayeem316
Messages: 21
Registered: December 2009
Location: US
Junior Member

create or replace procedure p1(p_deptno number, p_dname varchar2, p_loc varchar2) as
Begin

insert into dept (deptno, dname, loc) values (p_deptno, p_dname, p_loc);

commit;

end p1;


--------------------------------

Now in your package when you call your procedure. It will commit the values.

exec proc1 (50,'Tax','LA').


I have a question.

How do I post My Question.


Nayeem

[Updated on: Mon, 04 January 2010 10:49]

Report message to a moderator

Re: I have a packaged procedure [message #437474 is a reply to message #437472] Mon, 04 January 2010 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First you should carefully read Kevin's post.

Quote:
How do I post My Question

Click on "New Topic" button but before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: I have a packaged procedure [message #437475 is a reply to message #435786] Mon, 04 January 2010 11:09 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
syed.nayeem316 please comment on the following:

We have to do the following need and descriptive rules:

1) add a department
2) add an employee to it

Both these operations must succeed or I should rollback the work so that nothing is done. For example, if the employee insert fails, then I should rollback so that the department insert is undone. Under no circumstances should we create a department without any employees.

Now we run using your code to do the department insert:

begin
   exec proc1 (50,'Tax','LA');
   insert into emp values (emp_id,dept_id) values (1/0,50);
   commit;
exception when others then
   rollback;
   raise;
end;
/

The above clearly defines a unit of work as being the department and employee creates done together as one operation. So...

Q: What will be the result of the above anonymous block?
A: The employee insert will generat a zero divide error. We will issue a rollback because of the error but we in the end we will have a department with no employess.

Q: Is this what I want?
A: NO, the requirement specifically stated no departments without employess.

Q: So how did this happen when the code clearly issued a rollback after the error?
A: (you tell me)

Kevin
Previous Topic: How to get all the running process in oracle
Next Topic: ORA-00942 Grant access to Role for Function
Goto Forum:
  


Current Time: Sun Sep 25 19:41:12 CDT 2016

Total time taken to generate the page: 0.38118 seconds