[FORMS PL/SQL] Select for Update [Résolu]

[taska]

Bonjour,
Lorque je lance une forme à l'adresse :

http://xxx:7781/forms/frmservlet?con...x&form=xxx.fmx

une mise à jour est corretement effectuée. Si je lance cette même form depuis mon menu applicatif, mes données ne sont pas mises à jour

http://xxx:7781/forms/frmservlet?con...id=xxx/xxx@xxx

J'ai placé différents messages et dans les 2 cas, je passe au même endroit avec les mêmes valeurs.

Le problême se situe sur le curseur c_sysreg

Quel est votre avis ? merci .

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
declare
 
	t_sysreg_id    system_registry.sysreg_id%type;
 
  CURSOR C_PARENT IS
    SELECT * FROM SYSTEM_REGISTRY
      WHERE SYSREG_ID = :LOCAL.selected_node;
 
  R_PARENT SYSTEM_REGISTRY%ROWTYPE;
 
	cursor c_sysreg IS
	  SELECT 1 FROM system_registry
	    WHERE sysreg_id = :leaf_block.sysreg_id
	    FOR UPDATE of description,sysreg_data
	    nowait;
 
	r_sysreg c_sysreg%rowtype;
 
  cursor c_unique(
    p_parent_desc     varchar2,
    p_description     varchar2,
    p_company_code    varchar2,
    p_department_code varchar2,
    p_point_code      varchar2,
    p_user_id         varchar2
  ) IS
    SELECT sysreg_id FROM system_registry
      WHERE upper(parent_desc)              = upper(p_parent_desc)
      AND   upper(description)              = upper(p_description)
      AND   nvl(upper(company_code),' ')    = nvl(upper(p_company_code),' ')
      AND   nvl(upper(department_code),' ') = nvl(upper(p_department_code),' ')
      AND   nvl(upper(point_code),' ')      = nvl(upper(p_point_code),' ')
      AND   nvl(upper(user_id),' ')         = nvl(upper(p_user_id),' ');
 
  r_unique c_unique%rowtype;
 
	e_handled	exception;
 
begin
 
    -- update existing...
    FOR a IN 1..10 loop
    begin
    			message ('open');
        open c_sysreg;
        	message ('open OK');
        exit;
    	exception
    		when others then
    		  IF sqlcode = -54 then
    		  	IF a = 10 then
    		  		pl_debug.display('Could not lock entry for update');
    		  		raise e_handled;
    		  	end IF;
    		  else
   		  		pl_debug.display('SQL error ' || sqlcode || ' trying to lock registry entry');
   		  		raise e_handled;
   		  	end IF;
    	end;
    end loop;
    fetch c_sysreg INTO r_sysreg;
    IF c_sysreg%found then
    	message ('c_sysreg%found');
    	message ( :editleaf.description);
   		message (:editleaf.sysreg_data);
    	message (:leaf_block.sysreg_id);
    	begin
    	UPDATE system_registry
    	  SET description = :editleaf.description,
    	      sysreg_data = :editleaf.sysreg_data
    	  WHERE current of c_sysreg;
 
    	 commit;
    	 message ('commit');
    	exception when others then 
    		message (sqlerrm);
    	end;
    else
    		message ('Error locating registry entry, refresh and retry');
    		message ('Error locating registry entry, refresh and retry');
    	pl_debug.display('Error locating registry entry, refresh and retry');
    end IF;
    close c_sysreg;
  -- end if;
 
  standard.commit;
  	message ('Populate_Leaves');
 
  Populate_Leaves;
  	message ('hide_window');
 
 
  hide_window('WIN_EDITLEAF');
  go_block('tree_block');
 
exception
	when e_handled then
	  NULL;
	when others then
	  pl_debug.display('Unexpected SQL error ' || sqlcode || chr(10) || sqlerrm(sqlcode));
	  IF c_sysreg%isopen then
	  	close c_sysreg;
	  end IF;
	  IF c_parent%isopen then
	  	close c_parent;
	  end IF;
 
end;

[taska]

Subject: Using "Update ... Where Current Of" Sometimes Data Is Not Updated, Using Version 10.1.2.0.2
Doc ID: Note:369451.1 Type: PROBLEM
Last Revision Date: 26-JUN-2006 Status: PUBLISHED


Cause

The cause of this problem has been identified in Bug 4762734. It is caused by rowid sometimes not obtained correctly. In the test case for the bug this could happen for the first two rows of a cursor in a form that was opened in a separate session.

This is fixed in future version 10.1.2.2

Please note a similar issue exists in Reports, it is identified in Bug 5099183.
Solution

While awaiting the availability of Forms 10.1.2.2 one solution is to apply the Patch 4762734 on top of your 10.1.2.0.2 - if it is available for your platform.
Regarding the reports issue, while awaiting the availability of Reports 10.1.2.2, similarly, a Patch 5099183 is available on top of your 10.1.2.0.2 .

[plaineR]

Merci de nous avoir fait partager la solution

[taska]

solution ....

malgré l'application du patch, l'update se fait toujours de façon aléatoire ...