Oracle Designer and Forms to Apex: List of Values

If you are converting your Oracle Designer and Forms to Application Express (Apex), there are some components you can easily re-use. One of them is domain List of Values.

Domains and domain values are typically stored in table CG_REF_CODES. From this table it is simple to generate Apex List of Values for each domain.


declare
  -- Modify v_flow_id to your application ID
  v_flow_id      number := 101;
  -- You can give the LOV name a prefix to easily recognize LOV's
  -- derived from a domain, e.g. DMN_GENDER
  v_lov_prefix   varchar2(10) := 'DMN_';
begin
  for r in
  (select distinct rc.rv_domain
   from cg_ref_codes rc
   where rc.rv_meaning is not null
   and not exists
     (select 1
      from   apex_application_lovs
      where  application_id = v_flow_id
      and    list_of_values_name = v_lov_prefix || rc.rv_domain
     )    
  )
  loop
    wwv_flow_api.create_list_of_values (
      p_id       => null,
      p_flow_id  => v_flow_id,
      p_lov_name => v_lov_prefix || r.rv_domain,
      p_lov_query=> 'select rv_meaning d, rv_low_value r'||unistr('\000a')||
'from   cg_ref_codes'||unistr('\000a')||
'where  rv_domain = '''||r.rv_domain||''''||unistr('\000a')||
'order by 1');
 
  end loop;
end;

That's it.

I will be posting more tips on the Forms to Apex migration.