| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: locks through PL-SQL
--=_MAILER_ATTACH_BOUNDARY1_20031461531571734575198
Content-Type: text/plain; charset=us-ascii
Thanks a lot for the explaination. I got it.
Regards,
~Dilip
ORACLE-L_at_fatcity.com wrote:
Closing a cursor doesn't release the lock.
Think of it in SQL*PLUS. If you issue a SELECT ... FOR UPDATE statment, it opens an implicit cursor returns the rows and closes the cursor. But it doesn't release the lock.
Locks can only be released by COMMIT/ROLLBACK
Regards
Naveen
-----Original Message-----
Sent: Friday, January 03, 2003 4:44 PM
To: Multiple recipients of list ORACLE-L
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421 Content-Type: text/plain; charset=us-ascii
Hi List,
Little confused about the locks. I wrote a plsql procedure as follows:
---
CREATE OR REPLACE procedure b as
cursor sel_up is select * from emp_info for update;
begin
For emp_rec in sel_up1 LOOP
dbms_output.put_line('Hi');
End loop;
end;
---
I am doing select for update in the cursor and coming out of the procedure
without commit/rollback. When I run this procedure from sqlplus, it should
acquire the lock on the rows and when procedure ends it should release locks
as I am closing the cursor.
But even if the procedure completes, it doesn't release lock. It releases
locks only if I terminate the session or manually type rollback or commit on
the sqlplus prompt. Can somebody explain this ?
Regards,
Dilip
Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com
Buy the best in Movies at http://www.videos.indiatimes.com
Now bid just 7 Days in Advance and get Huge Discounts on Indian Airlines
Flights. So log on to http://indianairlines.indiatimes.com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421
Content-Type: text/html; charset=us-ascii
Hi List,
Little confused about the locks. I wrote a plsql procedure as
follows:
---
CREATE size=2>OR size=2>REPLACE size=2>procedure b
color=#0000f0 size=2>as
cursorsize=2> sel_up issize=2> selectsize=2> *
from
emp_info forsize=2> update;size=2>
begin
For emp_rec size=2>in sel_up1
color=#0000f0 size=2>LOOP
dbms_output.put_line('Hi'
color=#0000f0 size=2>);
End size=2>loop;
end;
---
I am doing select for update in the cursor and coming out of the procedure
without commit/rollback. When I run this procedure from sqlplus, it
should acquire the lock on the rows and when procedure ends it should release
locks as I am closing the cursor.
But even if the procedure completes, it doesn't release lock. It releases
locks only if I terminate the session or manually type rollback or commit on
the sqlplus prompt. Can somebody explain this ?
Regards,
Dilip
Get Your Private, Free E-mail from
Indiatimes at
face="Arial" size="2">http://email.indiatimes.com
Buy the
best in Movies at
href="http://www.videos.indiatimes.com">http://www.videos.indiatimes.com<
br>Now bid just 7 Days in Advance and get
Huge Discounts on Indian Airlines Flights. So log on to
href="http://indianairlines.indiatimes.com">http://indianairlines.indiatimes.
com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: dilip7772002
INET: dilip7772002_at_indiatimes.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: naveen_nahata_at_mindtree.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com
Buy the best in Movies at http://www.videos.indiatimes.com
Now bid just 7 Days in Advance and get Huge Discounts on Indian Airlines Flights. So log on to http://indianairlines.indiatimes.com and Bid Now !
--=_MAILER_ATTACH_BOUNDARY1_20031461531571734575198
Content-Type: text/html; charset=us-ascii
<P>Thanks a lot for the explaination. I got it.</P>
<P>Regards,</P>
<P>~Dilip </P>
<P><BR><BR><B><I>ORACLE-L_at_fatcity.com</B></I> wrote:<BR><BR></P>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"><BR>Closing a cursor doesn't release the lock.<BR><BR>Think of it in SQL*PLUS. If you issue a SELECT ... FOR UPDATE statment, it<BR>opens an implicit cursor returns the rows and closes the cursor. But it<BR>doesn't release the lock.<BR><BR>Locks can only be released by COMMIT/ROLLBACK<BR><BR>Regards<BR>Naveen<BR><BR>-----Original Message-----<BR>Sent: Friday, January 03, 2003 4:44 PM<BR>To: Multiple recipients of list ORACLE-L<BR><BR><BR>--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421<BR>Content-Type: text/plain; charset=us-ascii<BR><BR>Hi List,<BR><BR><BR>Little confused about the locks. I wrote a plsql procedure as follows:<BR><BR><BR>---<BR><BR><BR>CREATE OR REPLACE procedure b as<BR><BR><BR>cursor sel_up is select * from emp_info for update; <BR><BR><BR>begin<BR><BR><BR>For emp_rec in sel_up1 LOOP<BR><BR><BR>dbms_output.put_line('Hi');<BR><BR><BR>End loop;<BR><BR><BR>end;<BR><BR><B!
R>!
!
---<BR><BR><BR>I am doing select for update in the cursor and coming out of the procedure<BR>without commit/rollback. When I run this procedure from sqlplus, it should<BR>acquire the lock on the rows and when procedure ends it should release locks<BR>as I am closing the cursor.<BR><BR><BR>But even if the procedure completes, it doesn't release lock. It releases<BR>locks only if I terminate the session or manually type rollback or commit on<BR>the sqlplus prompt. Can somebody explain this ? <BR><BR><BR>Regards,<BR><BR><BR>Dilip<BR><BR><BR><BR><BR><BR><BR><BR>Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com<BR>Buy the best in Movies at http://www.videos.indiatimes.com<BR>Now bid just 7 Days in Advance and get Huge Discounts on Indian Airlines<BR>Flights. So log on to http://indianairlines.indiatimes.com and Bid Now !<BR><BR>--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421<BR>Content-Type: text/html; charset=us-ascii<BR><BR>
<P>Hi List,</P><BR>
<P>Little confused about the locks. I wrote a plsql procedure as<BR>follows:</P><FONT color=#0000f0 size=2><BR>
<P>---</P><BR>
<P>CREATE</FONT><FONT color=#000000 size=2> </FONT><FONT color=#00f0b0>size=2>OR</FONT><FONT color=#000000 size=2> </FONT><FONT color=#00f0b0>size=2>REPLACE</FONT><FONT color=#000000 size=2> </FONT><FONT color=#00f0b0>size=2>procedure</FONT><FONT color=#000000 size=2> b </FONT><FONT<BR>color=#0000f0 size=2>as</FONT></P><BR>
<P><FONT color=#0000f0>c<FONT size=2>ursor</FONT></FONT><FONT color=#0000b0>size=2> sel_up </FONT><FONT color=#0000f0 size=2>is</FONT><FONT color=#0000b0>size=2> </FONT><FONT color=#0000f0 size=2>select</FONT><FONT color=#0000b0>size=2> </FONT><FONT color=#0000f0 size=2>*</FONT><FONT color=#000000 size=2><BR></FONT><FONT color=#0000f0 size=2>from</FONT><FONT color=#000000 size=2><BR>emp_info fo</FONT><FONT color=#0000f0 size=2>r</FONT><FONT color=#0000b0>size=2> </FONT><FONT color=#0000f0 size=2>update;</FONT><FONT color=#0000b0>size=2> </P></FONT><FONT color=#0000f0 size=2><BR>
<P>begin</P><BR>
<P>For</FONT><FONT color=#000000 size=2> emp_rec </FONT><FONT color=#00f0b0>size=2>in</FONT><FONT color=#000000 size=2> sel_up1 </FONT><FONT<BR>color=#0000f0 size=2>LOOP</P><BR>
<P>dbms_output.put_line(</FONT><FONT color=#ff0000 size=2>'Hi'</FONT><FONT<BR>color=#0000f0 size=2>);</P><BR>
<P>End</FONT><FONT color=#000000 size=2> </FONT><FONT color=#00f0b0>size=2>loop;</P><BR>
<P>end;</P><BR>
<P></FONT> ---</P><BR>
<P>I am doing select for update in the cursor and coming out of the procedure<BR>without commit/rollback. When I run this procedure from sqlplus, it<BR>should acquire the lock on the rows and when procedure ends it should release<BR>locks as I am closing the cursor.</P><BR>
<P>But even if the procedure completes, it doesn't release lock. It releases<BR>locks only if I terminate the session or manually type rollback or commit on<BR>the sqlplus prompt. Can somebody explain this ? </P><BR>
<P>Regards,</P><BR>
<P>Dilip</P><BR>
<P> </P><BR>
<P><BR> </P><BR>
<HR>
<FONT face=Arial size=2><B>Get Your Private, Free E-mail from<BR>Indiatimes at </FONT>face="Arial" size="2">http://email.indiatimes.com </B><BR>Buy the<BR>best in Movies at <A<BR>href="http://www.videos.indiatimes.com">http://www.videos.indiatimes.com</A><<BR>br><FONT face=Arial size=2>Now bid just <B>7 Days in Advance</B> and get<BR><B>Huge Discounts</B> on Indian Airlines Flights. So log on to <A<BR>href="http://indianairlines.indiatimes.com">http://indianairlines.indiatimes.<BR>com</A> and Bid Now !</FONT><BR><BR>--=_MAILER_ATTACH_BOUNDARY1_2003135164054859484421--<BR><BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.net<BR>-- <BR>Author: dilip7772002<BR>INET: dilip7772002@indiatimes.com<BR><BR>Fat City Network Services -- 858-538-5051 http://www.fatcity.com<BR>San Diego, California -- Mailing list and web hosting services<BR>----------------------------------------------------------!
--!
!
---------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).<BR><BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.net<BR>-- <BR>Author: Naveen Nahata<BR>INET: naveen_nahata@mindtree.com<BR><BR>Fat City Network Services -- 858-538-5051 http://www.fatcity.com<BR>San Diego, California -- Mailing list and web hosting services<BR>---------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also !
se!
!
nd the HELP command for other information (like subscribing).<BR></BLOCKQUOTE><BR>
<hr><font face="Arial" size="2"><b>Get Your Private, Free E-mail from Indiatimes at </font>http://email.indiatimes.com</b><br>Buy the best in Movies at http://www.videos.indiatimes.com<br><font face="Arial" size="2">Now bid just <b>7 Days in Advance</b> and get <b>Huge Discounts</b> on Indian Airlines Flights. So log on to http://indianairlines.indiatimes.com and Bid Now !</font>
--=_MAILER_ATTACH_BOUNDARY1_20031461531571734575198--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: dilip7772002
INET: dilip7772002_at_indiatimes.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sat Jan 04 2003 - 04:05:15 CST
 |
 |