RE: locks through PL-SQL

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



<P>Hi List,</P>

<P>Little confused about the locks. I wrote a&nbsp;plsql procedure as

follows:</P><FONT color=#0000f0 size=2>


<P>---</P>

<P>CREATE</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000f0

size=2>OR</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000f0
size=2>REPLACE</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000f0
size=2>procedure</FONT><FONT color=#000000 size=2> b </FONT><FONT
color=#0000f0 size=2>as</FONT></P>


<P><FONT color=#0000f0>c<FONT size=2>ursor</FONT></FONT><FONT color=#000000

size=2> sel_up </FONT><FONT color=#0000f0 size=2>is</FONT><FONT color=#000000
size=2> </FONT><FONT color=#0000f0 size=2>select</FONT><FONT color=#000000
size=2> </FONT><FONT color=#0000f0 size=2>*</FONT><FONT color=#000000 size=2>


</FONT><FONT color=#0000f0 size=2>from</FONT><FONT color=#000000 size=2>

emp_info fo</FONT><FONT color=#0000f0 size=2>r</FONT><FONT color=#000000
size=2> </FONT><FONT color=#0000f0 size=2>update;</FONT><FONT color=#000000
size=2> </P></FONT><FONT color=#0000f0 size=2>


<P>begin</P>

<P>For</FONT><FONT color=#000000 size=2> emp_rec </FONT><FONT color=#0000f0

size=2>in</FONT><FONT color=#000000 size=2> sel_up1 </FONT><FONT
color=#0000f0 size=2>LOOP</P>


<P>dbms_output.put_line(</FONT><FONT color=#ff0000 size=2>'Hi'</FONT><FONT

color=#0000f0 size=2>);</P>


<P>End</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000f0

size=2>loop;</P>


<P>end;</P>

<P></FONT>&nbsp;---</P>

<P>I am doing select for update in the cursor and coming out of the procedure

without commit/rollback.&nbsp;When I&nbsp;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.</P>


<P>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 ? </P>


<P>Regards,</P>

<P>Dilip</P>

<P>&nbsp;</P>

<P><BR>&nbsp;</P>

<hr><font face="Arial" size="2"><b>Get Your Private, Free E-mail from

Indiatimes at  </font><a href="http://email.indiatimes.com"><font
face="Arial" size="2">http://email.indiatimes.com</font></a></b><br>Buy the
best in Movies at <A
href="http://www.videos.indiatimes.com">http://www.videos.indiatimes.com</A><
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  <a

href="http://indianairlines.indiatimes.com">http://indianairlines.indiatimes.
com</a> and Bid Now !</font>

--=_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).