« Griot's Spray-On Protective Bra | Main | Z4 M Coupé Strut Brace »

July 13, 2006

Getting and Setting Sort Preferences

Q:For reports that have clickable column headers, HTML DB remembers this sorting choice by establishing a preference named FSP_P_R_SORT with a value of "fsp_sort_(_desc)". I would like to be able to programatically get and set this preference. Essentially, I would like to save (and re-establish) the user's sorting preference, but I can't rely on the built-in mechanism to do this because the region source is generated from a PL/SQL block, and the resulting query can vary significantly. Getting the App and Page ID's are no problem, but I'd rather not hard-code in a Region ID (if that's even what that 3rd number is). Is there some way to access this value from within a PL/SQL process? Thanks. -Rich

A:It is possible to find the region_id using htmldb_application.g_plug_id (plugs are regions) via g_plug_name

This code will show the approach:

begin
  htp.tableOpen;
  htp.tableRowOpen;
  htp.tableHeader('region_id');
  htp.tableHeader('region_name');
  htp.tableRowClose;
  for i in 1.. htmldb_application.g_plug_id.COUNT loop
    htp.tableRowOpen;
      htp.tableData(htmldb_application.g_plug_id(i));
      htp.tableData(htmldb_application.g_plug_name(i));
    htp.tableRowClose;
  end loop;
  htp.tableClose;
end;

Here is the full solution:

Before Header Process: Set region sort preference

declare
  l_region_id varchar2(20);
begin
  -- find the ID of the region
  for i in 1.. htmldb_application.g_plug_id.COUNT loop
 
    if htmldb_application.g_plug_name(i) = :P1_REGION_NAME then
      l_region_id := htmldb_application.g_plug_id(i);
    end if;
 
  end loop;
 
  :P1_REGION_FSP_SORT := 'FSP' || :APP_ID || '_P' || :APP_PAGE_ID || '_R' || l_region_id || '_SORT';
end;


On Submit Process: Reset Sort Change

HTMLDB_UTIL.SET_PREFERENCE(
p_preference => :P1_REGION_FSP_SORT
, p_value => :P1_FSP_DEFAULT_SORT_COLUMN);

Item: P1_FSP_DEFAULT_SORT_COLUMN is set to the column value I want to reset to, ie. fsp_sort_1_desc

Posted by rimblas at July 13, 2006 9:57 AM