Home » SQL & PL/SQL » SQL & PL/SQL » commit in procedure (Oracle 9i)
commit in procedure [message #381974] Tue, 20 January 2009 09:02 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Create or replace procedure p1
is
n number;
begin
insert into t1 values('ramesh');
commit;
n:=sql%rowcount;
dbms_output.put_line('no.of rows inserted='||n);
end;
/
In this scenario O/p is No.of rows inserted=0
If i use commit after sql%rowcount statement no.of rows inserted=1.
what is the reason please help me?
Re: commit in procedure [message #381987 is a reply to message #381974] Tue, 20 January 2009 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the documentation on %rowcount...
Conclusion?

Regards
Michel
Re: commit in procedure [message #381997 is a reply to message #381987] Tue, 20 January 2009 09:50 Go to previous messageGo to next message
ranu123ghosh
Messages: 2
Registered: January 2009
Junior Member
%rowcount statement counts the number of rows affected by the previous sql statement , so to get the correct number of rows modified it should be placed after the insert and before the commit statement .
Re: commit in procedure [message #382002 is a reply to message #381997] Tue, 20 January 2009 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct.

Regards
Michel

[Updated on: Tue, 20 January 2009 10:34]

Report message to a moderator

Re: commit in procedure [message #382010 is a reply to message #382002] Tue, 20 January 2009 10:56 Go to previous messageGo to next message
ranu123ghosh
Messages: 2
Registered: January 2009
Junior Member
In the example shown it is n :=sql%rowcount .
Re: commit in procedure [message #382011 is a reply to message #382010] Tue, 20 January 2009 11:02 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And...?

Regards
Michel
Previous Topic: Forall
Next Topic: writing table data into csv file(s)
Goto Forum:
  


Current Time: Sat Dec 10 12:51:59 CST 2016

Total time taken to generate the page: 0.04558 seconds