Re: locks through PL-SQL

Consider a simple SQL*Plus analogy:

SQL> select * from emp_info for update;

    list of columns appears
    N rows selected
SQL>
    The rows are still locked.

Why should pl/sql behave differently from SQL*Plus.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 03 January 2003 11:56

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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