STORED PROCEDURE QUESTION
Hi friends, I am looking for a way in which I can apply a row lock inside of a procedure. Is that posible? I just tried SELECT FOR UPDATE but i get an error. Thanks in advance JPG
Javier Puente wrote:
> Hi friends,
>
> I am looking for a way in which I can apply a row lock
> inside of a procedure.
>
> Is that posible? I just tried SELECT FOR UPDATE but i get an error.
>
> Thanks in advance
>
> JPG
Hello Javier!
As far as I know, you can only change the locking level via an
ALTER TABLE statement, like so ...
ALTER TABLE my_table LOCK MODE ROW
So I guess you'd put that statement into your stored procedure.
Then I guess at the end of your stored procedure you'd change
the locking mode back to its original level. Probably like this ...
ALTER TABLE my_table LOCK MODE PAGE
HTH,
Avi.
--
/\\ \\ /| Avi Abrami, Analyst/Programmer, Telegate Ltd.
/__\\ \\ / | 7 Haplada Street, Or-Yehuda, ISRAEL
/ \\ \\/ | Phone:+972-3-5388717 Fax:+972-3-5335877 eMail:avia@telegate.co.il
Hi JPG,
I am sending you an example for an update cursor in a stored procedure,
hope it will help.
create proccedure delete_orders()
define p_ship_date date;
begin work;
foreach cur1 for
select ship_date into p_ship_date from orders
where order_date<today-100
if p_ship_date is not null then
delete from orders where current of cur1
end if;end foreach;
commit work;
By,
Javier Puente wrote:
> Hi friends,
>
> I am looking for a way in which I can apply a row lock
> inside of a procedure.
>
> Is that posible? I just tried SELECT FOR UPDATE but i get an error.
>
> Thanks in advance
>
> JPG
--
_________________________________________________________________________
|
| Miya Nadri,
| ATG department.
| ComSoft Technologies Ltd.
| 39 Ha'galim blvd, Herzelia, Israel.
| Tel: 972-9-9598637
| Fax: 972-9-9598980
| E-Mail: miyan@comsoft.co.il
| WEB: www.comsoft.co.il
|________________________________________________________________________