Block based on Stored Procedures & Locking_Mode

[Sri]

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