Re: locking rows with DBMS_SQL
Date: Sat, 04 Jul 1998 00:03:17 GMT
Message-ID: <359d70d3.599241_at_192.86.155.100>
A copy of this was sent to Javier Rojas <jrv_at_quisar.com> (if that email address didn't require changing) On Thu, 02 Jul 1998 14:00:52 +0200, you wrote:
>I want to lock rows using the function above but it doesn't lock the row
>selected (no errors) WHY ???.
>Is there another way to do dynamic row locking ???
>
>if sql_lock('SELECT pk_id FROM table WHERE pk_id = 12345 FOR UPDATE')
>then
> row lock SUCCESS...
>else
> row lock NOT SUCCESS....
>end if;
>
[Quoted] I ran your function in 2 different sessions, the exact call was:
begin
if sql_lock( 'select empno from emp where empno = 7369 for update nowait' ) then
dbms_output.put_line( 'Got It' );
else
dbms_output.put_line( 'Failed...' );
end if;
end;
/
In the first session, it printed "Got It", on the second session it printed "Failed". The one big difference is that I had a NOWAIT on the select (which causes an error)....
It works -- can you explain futher (with an example) of why you think it does not work?
>
>FUNCTION sql_lock(sql_text IN VARCHAR2) RETURN BOOLEAN IS
> cur_id NUMBER;
> status NUMBER;
>BEGIN
> cur_id := dbms_sql.open_cursor;
> dbms_sql.parse(cur_id, sql_text, DBMS_SQL.NATIVE);
> status := dbms_sql.execute(cur_id);
> status := dbms_sql.fetch_rows(cur_id);
> dbms_sql.close_cursor(cur_id);
>
> if status > 0 then
> RETURN TRUE;
> else
> RETURN FALSE;
> end if;
>
> EXCEPTION
> when OTHERS then
> if dbms_sql.is_open(cur_id) then
> dbms_sql.close_cursor(cur_id);
> end if;
>
> RETURN FALSE;
>END;
>
>
>
>TIA
>
>Javier Rojas.
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jul 04 1998 - 02:03:17 CEST