当前位置：首页 > news >正文

Oracle Form - 文件夹数据块点击排序后只剩一行显示(已解决)

news 来源：原创 2024/5/6 21:36:09

问题现象

最近在工作中遇到一个诡异的问题：背景是我开发了一个主从结构的表单，而且主从块都使用了文件夹功能；问题现象是，在查询出结果后，点击主块的标题执行排序，主块突然变成了只显示一行

查看排序前后主块的last query发现异样：

图一为排序前主块last query的WHERE条件，year=2019是块属性里面设置的Where子句，year between 2017 and 2022是我为了方便观察在pre-query触发器里加的查询条件

图二为排序后，可以看到year=2019这一句没变，但是后一句变成了delivery_plan_header_id=34179，就是它直接导致主块只变成一行了，因为这个字段是主键

补充一句：这个现象只在查询后主块第一行没有关联的从块数据时发生

检查过程

经过检查APPFLDR库，我找到了问题原因

首先简述一下排序的实现机制：1、安排好排序字段，顺序，升降序；2、给块设置一个临时的Default Where属性；3、执行查询；4、将块的Default Where属性复原

（参见APPFLDR库文件过程app_folder_drag_and_drop）

这里的第二步我分析其目的就是为了重新执行的查询条件和排序前执行的最后一次查询保持一致

这里设置的临时Default Where属性其内容为：

app_folder.base_where_clause || ' AND -1=-1 AND ' ||
app_folder.pending_where_clause || ' AND -2=-2'

在这里可以看到上图二中标志性的-1=-1和-2=-2，同时也推断出delivery_plan_header_id=34179即是当时app_folder.pending_where_clause的内容

进一步查看发现，这个pending_where_clause是在app_folder_ext_whr_cls过程中做初始赋值的：内容来源于system.last_query，从中截取WHERE到ORDER BY中间的内容

并且该过程还会将这个内容放入记录组folder_master_props.pending_where_clause里与当前块进行绑定

最后还会将一个全局变量global.folder_where_extracted标记为TRUE

这个变量大有来头：

1、在app_folder.event('PRE-QUERY')中，会直接令其为FALSE

2、在app_folder.event('POST-QUERY')中，会判断当它是FALSE时，执行app_folder_ext_whr_cls

3、app_folder.event('KEY-EXEQRY')中，只做了两件事：execute_query，和判断global.folder_where_extracted为FALSE时，执行app_folder_ext_whr_cls

另外，我又在app_folder.event('PRE-BLOCK')中发现，它调用的app_folder_get_instance_values过程，会用system.trigger_name去匹配出folder_master_props.pending_where_clause的内容存放至app_folder.pending_where_clause

至此问题就讲得通了

问题原因

对于一个独立的文件夹数据块来说，执行查询时触发器的执行顺序是：

KEY-EXEQRY

PRE-QUERY

POST-QUERY

KEY-EXEQRY

这里没有对齐的原因是PRE-QUERY是从KEY-EXEQRY中断去执行的，PRE-QUERY只执行一次，POST-QUERY在每加载出一行记录后各执行一次，最后回到KEY-EXEQRY来

而如果这个数据块同时又是一个主数据块，它在自己的最后一行加载出来，执行一次POST-QUERY后，便会回到第一条记录，再跳到从块上执行关联查询

这时先触发主块的POST-BLOCK，接下来依次是从块的PRE-BLOCK、PRE-QUERY、POST-QUERY、POST-BLOCK，再又回到主块，触发PRE-BLOCK，中断结束，继续KEY-EXEQRY触发器

问题就发生在这个过程中：当从块没有数据时，其POST-QUERY就没有执行；而它的PRE-QUERY已将全局变量global.folder_where_extracted赋值为FALSE了，此时回到主块的KEY-EXEQRY，正是执行完execute_query，该判断这个参数的时候了，程序发现它是FALSE，当即执行app_folder_ext_whr_cls，然而此时的system.last_query是时方才从块的last query，结果就是张冠李戴了

前图二中的delivery_plan_header_id正是设置的主从块关系条件

解决方法

方法一：主块不使用app_folder.event('KEY-EXEQRY')作为KEY-EXEQRY触发器，或者干脆不增加KEY-EXEQRY触发器

方法二：客制化app_folder.event('PRE-BLOCK')，即在取出app_folder.pending_where_clause内容后，举手之劳把global.folder_where_extracted赋值为TRUE

附程序单元：

1)程序包规格

PACKAGE bhsc_folder IS
  procedure event(trigger_name varchar2);
END;

2)程序包主体

PACKAGE BODY bhsc_folder IS
	PROCEDURE my_folder_get_instance_values (target_block varchar2) IS
	--
	-- Sets pointers and package globals for the proper folder block
	--
	  block_name      VARCHAR2(30);
	  function_mask   VARCHAR2(18);
	  item_name       VARCHAR2(30);
	  item_id         ITEM;
	  item_width      NUMBER;
	  item_prompt     VARCHAR2(80); 
	  item_x          NUMBER := null;
	  item_y          NUMBER := null;
	  folder_item_id  ITEM;
	  dummy           groupcolumn; --dummy colid to allow creation of the tab order column
	  pointers_set    BOOLEAN := FALSE;
	  rg_id 	  recordgroup;
	  rg_col_id       groupcolumn; 
	  errcode         NUMBER;
	  curr_block_id   BLOCK;
	BEGIN
	  app_folder_frd_stmt('Entering app_folder_get_instance_values. Target_block is '||target_block);
	  app_folder.master_props_id := find_group('folder_master_props');
	  if id_null(app_folder.master_props_id) then 
	    app_folder_set_static_handles;
	  end if;
	  
	  -- Check to see if this block exists in the master_props record group.  
	  -- If not, do nothing.
	  --
	  if (get_group_record_number('folder_master_props.folder_block', target_block)=0) then 
	    return;
	  end if;
	
	  -- 
	  -- Get the current slot in master properties, and the current object name, 
	  -- so that we can use them to check for a default folder
	  -- 
	  app_folder.current_slot := get_group_record_number('folder_master_props.folder_block', target_block);
	  if (app_folder.current_slot = 0) then 
	    message('FOLDER ERROR: Block not defined as folder block: ' || target_block);
	    for i in 1..get_group_row_count(app_folder.master_props_id) loop
	      message('row ' || to_char(i) || ': ' || get_group_char_cell('folder_master_props.prompt_block', i));
	    end loop;
	    return;
	  end if;
	
	  app_folder.curr_object :=
	    get_group_char_cell('folder_master_props.curr_object', app_folder.current_slot);  
	
	  --
	  -- Check to see if master/active record groups have already been created.  If not, 
	  -- check for a default folder, then create and populate the record groups.
	  -- 
	  app_folder.master_list_id := find_group('folderm_'||target_block);
	  if id_null(app_folder.master_list_id) then
	    --
	    -- Get the default folder id if there is one
	    -- 
	    app_folder.default_folder_id := app_folder_get_default_id; 
	    set_group_number_cell('folder_master_props.default_folder_id', app_folder.current_slot, app_folder.default_folder_id);
	    -- 
	    -- Call app_folder_create_groups to create and populate 
	    -- the master and active lists for this block
	    --
	    app_folder_create_groups(target_block);
	    pointers_set := TRUE;
	  end if;
	
	  if app_folder.folder_block != target_block then
	    block_name := target_block;
	    app_folder.folder_block := block_name;
	    app_folder.folder_block_id := find_block(block_name);
	    app_folder.folder_window := get_group_char_cell('folder_master_props.folder_window', app_folder.current_slot);
	    app_folder.folder_window_id := find_window(app_folder.folder_window);
	    app_folder.folder_canvas :=
	      get_group_char_cell('folder_master_props.folder_canvas', app_folder.current_slot);
	    app_folder.folder_tab_canvas :=
	      get_group_char_cell('folder_master_props.folder_tab_canvas', app_folder.current_slot);
	    app_folder.folder_content_canvas :=
	      get_group_char_cell('folder_master_props.folder_content_canvas', app_folder.current_slot);
	    app_folder.folder_fixed_canvas :=
	      get_group_char_cell('folder_master_props.folder_fixed_canvas', app_folder.current_slot);
	    app_folder.prompt_block := 
	      get_group_char_cell('folder_master_props.prompt_block', app_folder.current_slot);
	    app_folder.total_block := 
	      get_group_char_cell('folder_master_props.total_block', app_folder.current_slot);
	    app_folder.total_fields :=
	      get_group_number_cell('folder_master_props.total_fields', app_folder.current_slot);
	    app_folder.base_records_displayed :=
	      get_group_number_cell('folder_master_props.base_records_displayed', app_folder.current_slot);
	    app_folder.base_order_by := 
	      get_group_char_cell('folder_master_props.base_order_by', app_folder.current_slot);
	    app_folder.base_where_clause := 
	      get_group_char_cell('folder_master_props.base_where_clause', app_folder.current_slot);
	    --fnd_message.debug('Setting damage flag in GIV');
	    app_folder.damage_flag := 
	      (get_group_char_cell('folder_master_props.damage_flag', app_folder.current_slot) = 'Y');
	    app_folder.instantiated := 
	      (get_group_char_cell('folder_master_props.instantiated', app_folder.current_slot) = 'Y');
	    app_folder.tab_sorted := 
	      (get_group_char_cell('folder_master_props.tab_sorted', app_folder.current_slot) = 'Y');
	    app_folder.cut_column_flag := 
	      get_group_char_cell('folder_master_props.cut_column_flag', app_folder.current_slot);
	    app_folder.curr_folder_name := 
	      get_group_char_cell('folder_master_props.curr_folder_name', app_folder.current_slot);
	    app_folder.curr_folder_id :=
	      get_group_number_cell('folder_master_props.curr_folder_id', app_folder.current_slot);
	    app_folder.curr_autoquery_flag :=
	      get_group_char_cell('folder_master_props.curr_autoquery_flag', app_folder.current_slot);
	    app_folder.curr_owner_id :=          
	      get_group_number_cell('folder_master_props.curr_owner_id', app_folder.current_slot);
	    app_folder.curr_public_flag :=
	      get_group_char_cell('folder_master_props.curr_public_flag', app_folder.current_slot);
	    app_folder.curr_default_flag :=
	      get_group_char_cell('folder_master_props.curr_default_flag', app_folder.current_slot);
	    app_folder.curr_where_clause :=
	      get_group_char_cell('folder_master_props.curr_where_clause', app_folder.current_slot);
	    --
	    -- Moving this call to beginning of procedure, since we need the object name to 
	    -- determine whether the user has a default. 
	    --
	    --app_folder.curr_object :=
	      --get_group_char_cell('folder_master_props.curr_object', app_folder.current_slot);  
	    app_folder.pending_where_clause :=
	      get_group_char_cell('folder_master_props.pending_where_clause', app_folder.current_slot);
	    copy('TRUE', 'global.folder_query_extracted'); --added by yusuf 19-sept-2022
	    app_folder.developer_folder_id := 
	      get_group_number_cell('folder_master_props.developer_folder_id', app_folder.current_slot);
	    --
	    -- Set the master and active record group pointers if not just created.
	    --
	    if not pointers_set then 
	      -- app_folder.master_list_id was set at beginning of routine.
	      -- app_folder.master_list_id := find_group('folderm_'||block_name);
	      app_folder.master_name_id := find_column('folderm_'||block_name||'.name');  
	      app_folder.master_prompt_id := find_column('folderm_'||block_name||'.prompt'); 
	      --app_folder.master_active_flag_id := find_column('folderm_'||block_name||'.active_flag'); 
	      app_folder.master_width_id := find_column('folderm_'||block_name||'.width');
	      app_folder.master_allow_cut_flag_id := find_column('folderm_'||block_name||'.allow_cut_flag');
	      app_folder.master_rows := get_group_row_count(app_folder.master_list_id);
	      --
	      app_folder.active_list_id := find_group('foldera_'||block_name);
	      app_folder.active_name_id := find_column('foldera_'||block_name||'.name');  
	      app_folder.active_prompt_id := find_column('foldera_'||block_name||'.prompt'); 
	      app_folder.active_width_id := find_column('foldera_'||block_name||'.width');
	      app_folder.active_x_pos_id := find_column('foldera_'||block_name||'.x_pos');
	      app_folder.active_y_pos_id := find_column('foldera_'||block_name||'.y_pos');
	      app_folder.active_rows := get_group_row_count(app_folder.active_list_id);
	      -- 
	      -- Also reset default_folder_id
	      -- 
	      app_folder.default_folder_id := get_group_number_cell('folder_master_props.default_folder_id', app_folder.current_slot);
	    end if;
	    --
	    -- Set the function allowed flags from the function mask
	    --
	    function_mask := 
	      get_group_char_cell('folder_master_props.allowed_functions', app_folder.current_slot);  
	    app_folder.autoquery_allowed := (instr(function_mask, 'A') != 0);
	    app_folder.public_allowed    := (instr(function_mask, 'B') != 0);
	    app_folder.default_allowed   := (instr(function_mask, 'C') != 0);
	    app_folder.orderby_allowed := (instr(function_mask, 'D') != 0);
	    app_folder.open_allowed := (instr(function_mask, 'E') != 0);
	    app_folder.tools_allowed := (instr(function_mask, 'F') != 0);
	    app_folder.new_allowed := (instr(function_mask, 'G') != 0);
	    app_folder.save_allowed := (instr(function_mask, 'H') != 0);
	    app_folder.delete_allowed := (instr(function_mask, 'I') != 0);
	    app_folder.add_allowed := (instr(function_mask, 'J') != 0);
	    app_folder.cut_allowed := (instr(function_mask, 'K') != 0);
	    app_folder.swap_allowed := (instr(function_mask, 'L') != 0);
	    app_folder.bigger_allowed := (instr(function_mask, 'M') != 0);
	    app_folder.smaller_allowed := (instr(function_mask, 'N') != 0);
	    app_folder.autosize_allowed := (instr(function_mask, 'O') != 0);
	    app_folder.prompt_allowed := (instr(function_mask, 'P') != 0);
	    app_folder.contents_allowed := (instr(function_mask, 'Q') != 0);
	    app_folder.reset_allowed := (instr(function_mask, 'R') != 0);
	    --
	  end if;
	  app_folder_frd_stmt('Completed app_folder_get_instance_values.');
	END my_folder_get_instance_values;
  
  
  procedure event(trigger_name varchar2) is
  
  begin
  	if upper(trigger_name) = 'PRE-BLOCK' THEN
  	  my_folder_get_instance_values(name_in('system.trigger_block'));
  	ELSE
  	  app_folder_fldr_handler(trigger_name);
  	end if;
  end;
  
END;

3)触发器