Block based on Stored Procedures & Locking_Mode
Date: 22 Aug 2003 06:36:05 -0700
Message-ID: <d178ea15.0308220536.2a14fb68_at_posting.google.com>
Hello,
I'm creating a block in Forms 6.0.8 based on Stored Procedures. I'm using the example given in Metalink doc 52778.1. Updates to data work fine if locking_mode = 'Immediate'. If I set locking_mode to 'Delayed' and run the form, updates don't work. The IF condition in lock-procedure (grp_lock) always returns TRUE and I run into the exception trapped there. It appears that Forms is passing NEW (changed) values in p_grp_data if locking_mode is Delayed and this is causing the problem. In 'Immediate' mode, the form passes OLD values and that works fine.. Is there any thing I could do in the form or procedure (preferably in the procedure) to solve this problem ? I'm trying to write procedures that will work with both Immediate and Delayed locking_modes
thanks in advance..
- Table and package source:
- GRP create sequence grp_s /
create table grp
(
id number constraint grp_pk primary key,
name varchar2(10) not null constraint grp_uk unique,
description varchar2(30) not null,
active varchar2(1) default 'Y'
)
/
create or replace trigger grp_bri before insert on grp for each row
begin
select grp_s.nextval
into :new.id
from dual ;
end ;
/
CREATE OR REPLACE PACKAGE grp_pkg AS
TYPE grpidrec IS RECORD( id grp.id%TYPE ) ;
TYPE grprec IS RECORD ( id grp.id%type, name grp.name%type, description grp.description%type, active grp.active%type ) ;
TYPE grp_cursor IS REF CURSOR RETURN grp%rowtype ;
TYPE grp_tab IS TABLE OF grp%rowtype INDEX BY BINARY_INTEGER ;
TYPE grp_id_tab IS TABLE OF grpidrec INDEX BY BINARY_INTEGER ;
PROCEDURE grp_refcur( p_grp_data IN OUT grp_cursor, p_group_name IN grp.name%type ) ; -- use if a ref cursor is required
PROCEDURE grp_query( p_grp_data IN OUT grp_tab, p_group_name IN grp.name%TYPE ) ;
PROCEDURE grp_insert( p_grp_data IN grp_tab ) ;
PROCEDURE grp_update( p_grp_data IN grp_tab ) ;
PROCEDURE grp_delete( p_grp_data IN grp_id_tab ) ;
PROCEDURE grp_lock( p_grp_data IN grp_tab ) ;
END grp_pkg ;
/
sho err
create or replace package body grp_pkg as
- ================================================================================
PROCEDURE grp_refcur( p_grp_data IN OUT grp_cursor, p_group_name IN
grp.name%type ) AS
begin
open p_grp_data FOR select id, name, description, active
from grp
where name = nvl( p_group_name, name ) ;
end ;
- ================================================================================
PROCEDURE grp_query( p_grp_data IN OUT grp_tab, p_group_name IN
grp.name%TYPE ) AS
i number ;
CURSOR grp_select IS
SELECT id, name, description, active
FROM grp
WHERE name = nvl( p_group_name, name ) ;
begin
OPEN grp_select ;
i := 1 ;
LOOP
FETCH grp_select INTO p_grp_data(i).id, p_grp_data(i).name,
p_grp_data(i).description, p_grp_data(i).active ;
EXIT WHEN grp_select%NOTFOUND ;
i := i + 1 ;
END LOOP ; end ;
- ================================================================================
PROCEDURE grp_insert( p_grp_data IN grp_tab ) AS
i NUMBER ;
begin
FOR i in p_grp_data.FIRST .. p_grp_data.LAST
LOOP
INSERT INTO grp( name, description, active )
VALUES ( p_grp_data(i).name, p_grp_data(i).description,
p_grp_data(i).active ) ;
END LOOP ;
end ;
- ================================================================================
PROCEDURE grp_update( p_grp_data in grp_tab ) AS
i binary_integer ;
rec_modified exception ;
BEGIN
FOR i in p_grp_data.first .. p_grp_data.last LOOP
UPDATE grp
SET name = p_grp_data(i).name,
description = p_grp_data(i).description,
active = p_grp_data(i).active
WHERE id = p_grp_data(i).id ;
if sql%rowcount = 0 then
raise rec_modified ;
else
-- success
null ;
end if ;
END LOOP ; exception
when rec_modified then
raise_application_error(-20006, 'Record already modified' ) ;
when others then
raise_application_error(-20007, 'Other error : ' || sqlerrm ) ;
END ;
- ================================================================================
PROCEDURE grp_delete( p_grp_data IN grp_id_tab ) AS
i BINARY_INTEGER ;
begin
FOR i IN p_grp_data.FIRST .. p_grp_data.LAST LOOP
DELETE FROM grp
WHERE name = p_grp_data(i).id ;
END LOOP ; end grp_delete ;
- ================================================================================
PROCEDURE grp_lock( p_grp_data IN grp_tab ) AS
i BINARY_INTEGER ;
grec grprec ;
err varchar2(255) ;
errcd number ;
rec_modified exception ;
begin
FOR i in p_grp_data.FIRST .. p_grp_data.LAST LOOP
begin
SELECT id, name, description, active
INTO grec
FROM grp
WHERE id = p_grp_data(i).id
FOR UPDATE OF description NOWAIT ;
- this part returns true
- if locking_mode = 'Delayed'
- Forms is passing NEW values in p_grp_data if mode is Delayed
- and OLD values if mode is Immediate
if ( grec.name != p_grp_data(i).name
OR grec.description != p_grp_data(i).description
OR grec.active != p_grp_data(i).active ) THEN
raise rec_modified ;
end if ;
exception
when no_data_found then
raise_application_error( -20007, 'Record deleted by another user' ) ;
when rec_modified then
raise_application_error(-20006, 'Record modified by another user' ) ;
when others then
raise_application_error(-20009, 'Others' ) ;
end ;
END LOOP ; end ;
- ================================================================================
end grp_pkg ;
/
show error package body grp_pkg Received on Fri Aug 22 2003 - 15:36:05 CEST