create PL/SQL Package or Procedure,
because it is a Prototyp i just create an Procedure.
create or replace procedure create_page_with_region_ig
(p_schema_name varchar2,
p_pagename varchar2,
p_page_id number,
p_table_name varchar2,
p_column_list varchar2) as
-- we have to store different ids to handle dynamic creation
l_region_id number;
l_rowact_id number;
l_rowsel_id number;
l_irgrid_rep_view_id number;
l_rowid_id number;
l_irgrid_id number;
l_irgrid_rep_id number;
l_data_type varchar2(30);
l_item_type varchar2(30);
l_is_nullable varchar2(1);
l_is_required boolean;
l_display_sequence number;
l_column_list varchar2(4000) := replace(p_column_list,':',',');
type l_numarray is table of number;
l_array l_numarray;
begin
The API Calls:
wwv_flow_imp_page Calls are in each APEX Installationfile, so i think it will be exists in the next 20 years from now :-)
FYI: I just make an manuel created Apex application and exported it and take a look on it. (You will not find a lot about the API in the web or doc)
-- create a MODAL Page
wwv_flow_imp_page.create_page(
p_id=>p_page_id
,p_name=>p_schema_name||'_'||p_table_name||'_'||p_page_id
,p_alias=>p_schema_name||'_'||p_table_name||'_'||p_page_id
,p_page_mode=>'MODAL'
,p_step_title=>nvl(p_pagename,p_schema_name||'_'||p_table_name)||'_'||p_page_id
,p_autocomplete_on_off=>'OFF'
,p_page_template_options=>'#DEFAULT#:ui-dialog--stretch'
,p_protection_level=>'C'
,p_page_component_map=>'11'
,p_last_updated_by=>'ADMIN'
,p_last_upd_yyyymmddhh24miss=>to_char(sysdate,'YYYYMMDDHH24MISS')
);
Add REGION so called a PLUG:
-- get an id from the apex environment
l_region_id :=wwv_flow_id.next_val;
-- create_page_plug is for an Region
-- the Plug_template ID i got from my first export auf the manuel
-- created apex export file -- i have to change this to a generative one
-- in my case it is a interactive grid region
wwv_flow_imp_page.create_page_plug(
p_id=>wwv_flow_imp.id(l_region_id)
,p_plug_name=>p_table_name||'_'||p_page_id
,p_region_template_options=>'#DEFAULT#'
,p_component_template_options=>'#DEFAULT#'
--,p_plug_template=>wwv_flow_imp.id(10663362955261417) /* dynamic ? */
,p_plug_display_sequence=>10
,p_query_type=>'SQL'
-- we choose ROWID -- dont care about PKs for my use-case
,p_plug_source=>'select rowid,' || l_column_list || ' from ' || p_schema_name||'.'||p_table_name || ' a'
,p_plug_source_type=>'NATIVE_IG'
-- this are standards - i dont care about
,p_prn_units=>'INCHES'
,p_prn_paper_size=>'LETTER'
,p_prn_width=>11
,p_prn_height=>8.5
,p_prn_orientation=>'HORIZONTAL'
,p_prn_page_header=>p_table_name
,p_prn_page_header_font_color=>'#000000'
,p_prn_page_header_font_family=>'Helvetica'
,p_prn_page_header_font_weight=>'normal'
,p_prn_page_header_font_size=>'12'
,p_prn_page_footer_font_color=>'#000000'
,p_prn_page_footer_font_family=>'Helvetica'
,p_prn_page_footer_font_weight=>'normal'
,p_prn_page_footer_font_size=>'12'
,p_prn_header_bg_color=>'#eeeeee'
,p_prn_header_font_color=>'#000000'
,p_prn_header_font_family=>'Helvetica'
,p_prn_header_font_weight=>'bold'
,p_prn_header_font_size=>'10'
,p_prn_body_bg_color=>'#ffffff'
,p_prn_body_font_color=>'#000000'
,p_prn_body_font_family=>'Helvetica'
,p_prn_body_font_weight=>'normal'
,p_prn_body_font_size=>'10'
,p_prn_border_width=>.5
,p_prn_page_header_alignment=>'CENTER'
,p_prn_page_footer_alignment=>'CENTER'
,p_prn_border_color=>'#666666'
);
Now we need Region Colums, 3 Columns are fixed based for each Object for this use-case: ROW_SELECTOR, ROW_ACTION and ROWID as Primary Key:
l_rowsel_id := wwv_flow_id.next_val;
-- now we need to create Region COLUMNS based on input
-- first i decide to use the IGRid Classical Columns for Interactiveiness
wwv_flow_imp_page.create_region_column(
p_id=>wwv_flow_imp.id(l_rowsel_id)
,p_name=>'APEX$ROW_SELECTOR'
,p_item_type=>'NATIVE_ROW_SELECTOR'
,p_display_sequence=>10
,p_attribute_01=>'Y'
,p_attribute_02=>'Y'
,p_attribute_03=>'N'
);
l_rowact_id := wwv_flow_id.next_val;
wwv_flow_imp_page.create_region_column(
p_id=>wwv_flow_imp.id(l_rowact_id)
,p_name=>'APEX$ROW_ACTION'
,p_item_type=>'NATIVE_ROW_ACTION'
,p_display_sequence=>20
);
l_rowid_id := wwv_flow_id.next_val;
-- the Primary KEY Column is the ROWID
wwv_flow_imp_page.create_region_column(
p_id=>wwv_flow_imp.id(l_rowid_id)
,p_name=>'ROWID'
,p_source_type=>'DB_COLUMN'
,p_source_expression=>'ROWID'
,p_data_type=>'ROWID'
,p_session_state_data_type=>'VARCHAR2'
,p_item_type=>'NATIVE_HIDDEN'
,p_display_sequence=>30
,p_attribute_01=>'Y'
,p_use_as_row_header=>false
,p_is_primary_key=>true
,p_include_in_export=>false
);
Now we need the Selected Columns from the p_column_list parameter:
Get the Metadata from the Dictonary:
/* read oracle metadata to create the other columns */
l_array := l_numarray();
for c in (select column_name, data_type, nullable,column_id
from all_tab_columns
where table_name = p_table_name
and owner = p_schema_name
and instr(l_column_list, column_name) > 0
and data_type not like ('BLOB'))
loop
l_display_sequence := 30+c.column_id;
l_data_type := c.data_type;
l_is_nullable := c.nullable;
-- Mapping from DATATYPE to ITEM TYPE --
l_item_type := 'NATIVE_' ||case when l_data_type in ('NUMBER','FLOAT') then 'NUMBER_FIELD'
when l_data_type like '%CHAR%' or l_data_type like '%TIMESTAMP%' then 'TEXT_FIELD'
when l_data_type = 'DATE' then 'DATE_PICKER_APEX'
when l_data_type in ('CLOB') then 'TEXTAREA'
else 'TEXT_FIELD'
end ;
-- set p_is_required based on nullable value
if l_is_nullable = 'N' then
l_is_required := true;
else
l_is_required := false;
end if;
l_array.extend;
-- we add the generated IDs into an array too handle the IG Report Column mapping later
l_array(l_array.last) := wwv_flow_id.next_val;
Now we need the mapping to the ITEM Types:
-- not that super fance code ever --
if l_item_type = 'NATIVE_NUMBER_FIELD' then
wwv_flow_imp_page.create_region_column(
p_id => wwv_flow_imp.id( l_array(l_array.last)),
p_name => c.column_name,
p_source_type => 'DB_COLUMN',
p_source_expression => c.column_name,
p_data_type => l_data_type,
p_is_query_only => false,
p_item_type => l_item_type,
p_heading => c.column_name,
p_heading_alignment => 'RIGHT',
p_display_sequence => l_display_sequence,
p_value_alignment => 'RIGHT',
p_attribute_03 => 'left',
p_attribute_04 => 'decimal',
p_is_required => l_is_required,
p_enable_filter => true,
p_filter_is_required => false,
p_filter_lov_type => 'NONE',
p_use_as_row_header => false,
p_enable_sort_group => true,
p_enable_control_break => true,
p_enable_hide => true,
p_is_primary_key => false,
p_duplicate_value => true,
p_include_in_export => true
);
end if;
if l_item_type = 'NATIVE_DATE_PICKER_APEX' then
wwv_flow_imp_page.create_region_column(
p_id => wwv_flow_imp.id( l_array(l_array.last)),
p_name => c.column_name,
p_source_type => 'DB_COLUMN',
p_source_expression => c.column_name,
p_data_type => l_data_type,
p_is_query_only => false,
p_item_type => l_item_type,
p_heading => c.column_name,
p_heading_alignment=>'LEFT',
p_display_sequence => l_display_sequence,
p_value_alignment=>'LEFT',
p_attribute_01=>'N',
p_attribute_02=>'POPUP',
p_attribute_03=>'NONE',
p_attribute_06=>'NONE',
p_attribute_09=>'N',
p_attribute_11=>'Y',
p_is_required => l_is_required,
p_enable_filter=>true,
p_filter_is_required=>false,
p_filter_date_ranges=>'ALL',
p_filter_lov_type=>'DISTINCT',
p_use_as_row_header=>false,
p_enable_sort_group=>true,
p_enable_control_break=>true,
p_enable_hide=>true,
p_is_primary_key=>false,
p_duplicate_value=>true,
p_include_in_export=>true
);
end if;
if l_item_type = 'NATIVE_TEXTAREA' then
wwv_flow_imp_page.create_region_column(
p_id => wwv_flow_imp.id( l_array(l_array.last)),
p_name => c.column_name,
p_source_type => 'DB_COLUMN',
p_source_expression => c.column_name,
p_data_type => l_data_type,
p_is_query_only => false,
p_item_type => l_item_type,
p_heading => c.column_name,
p_heading_alignment=>'LEFT',
p_display_sequence => l_display_sequence,
p_value_alignment=>'LEFT',
p_attribute_01=>'Y',
p_attribute_02=>'N',
p_attribute_03=>'N',
p_attribute_04=>'BOTH',
p_is_required=>l_is_required,
p_max_length=>2000,
p_enable_filter=>true,
p_filter_operators=>'C:S:CASE_INSENSITIVE:REGEXP',
p_filter_is_required=>false,
p_filter_text_case=>'MIXED',
p_filter_lov_type=>'NONE',
p_use_as_row_header=>false,
p_enable_sort_group=>false,
p_enable_hide=>true,
p_is_primary_key=>false,
p_duplicate_value=>true,
p_include_in_export=>true
);
end if;
if l_item_type = 'NATIVE_TEXT_FIELD' then
wwv_flow_imp_page.create_region_column(
p_id => wwv_flow_imp.id( l_array(l_array.last)),
p_name => c.column_name,
p_source_type => 'DB_COLUMN',
p_source_expression => c.column_name,
p_data_type => l_data_type,
p_is_query_only => false,
p_item_type => l_item_type,
p_heading => c.column_name,
p_heading_alignment=>'LEFT',
p_display_sequence => l_display_sequence,
p_value_alignment=>'LEFT',
p_attribute_05=>'BOTH',
p_is_required=>l_is_required,
p_max_length=>2000,
p_enable_filter=>true,
p_filter_operators=>'C:S:CASE_INSENSITIVE:REGEXP',
p_filter_is_required=>false,
p_filter_text_case=>'MIXED',
p_filter_lov_type=>'DISTINCT',
p_use_as_row_header=>false,
p_enable_sort_group=>true,
p_enable_hide=>true,
p_is_primary_key=>false,
p_duplicate_value=>true,
p_include_in_export=>true
);
end if;
end loop;
l_irgrid_id := wwv_flow_id.next_val;
Time for the Interactive Grid Component:
-- now we can create the interactive grid component
wwv_flow_imp_page.create_interactive_grid(
p_id=>wwv_flow_imp.id(l_irgrid_id)
,p_internal_uid=>l_irgrid_id
,p_is_editable=>true
,p_edit_operations=>'i:u:d'
,p_lost_update_check_type=>'VALUES'
,p_add_row_if_empty=>true
,p_submit_checked_rows=>false
,p_lazy_loading=>false
,p_requires_filter=>false
,p_select_first_row=>true
,p_fixed_row_height=>true
,p_pagination_type=>'SCROLL'
,p_show_total_row_count=>true
,p_show_toolbar=>true
,p_enable_save_public_report=>false
,p_enable_subscriptions=>true
,p_enable_flashback=>true
,p_define_chart_view=>true
,p_enable_download=>true
,p_download_formats=>'CSV:HTML:XLSX:PDF'
,p_enable_mail_download=>true
,p_fixed_header=>'PAGE'
,p_show_icon_view=>false
,p_show_detail_view=>false
);
l_irgrid_rep_id := wwv_flow_id.next_val;
-- each interactive grid also need an ig report
-- for correct working
wwv_flow_imp_page.create_ig_report(
p_id=>wwv_flow_imp.id(l_irgrid_rep_id)
,p_interactive_grid_id=>wwv_flow_imp.id(l_irgrid_id)
,p_static_id=>to_number(to_char(sysdate,'ddmmhh24miss'))
,p_name=>to_char(sysdate,'ddmmhh24miss')
,p_type=>'PRIMARY'
,p_default_view=>'GRID'
,p_show_row_number=>false
,p_settings_area_expanded=>true
);
l_irgrid_rep_view_id := wwv_flow_id.next_val;
wwv_flow_imp_page.create_ig_report_view(
p_id=>wwv_flow_imp.id(l_irgrid_rep_view_id)
,p_report_id=>wwv_flow_imp.id(l_irgrid_rep_id)
,p_view_type=>'GRID'
,p_srv_exclude_null_values=>false
,p_srv_only_display_columns=>true
,p_edit_mode=>false
);
wwv_flow_imp_page.create_ig_report_column(
p_id=>wwv_flow_imp.id(wwv_flow_id.next_val)
,p_view_id=>wwv_flow_imp.id(l_irgrid_rep_view_id)
,p_display_seq=>0
,p_column_id=>wwv_flow_imp.id(l_rowact_id)
,p_is_visible=>true
,p_is_frozen=>false
);
wwv_flow_imp_page.create_ig_report_column(
p_id=>wwv_flow_imp.id(wwv_flow_id.next_val)
,p_view_id=>wwv_flow_imp.id(l_irgrid_rep_view_id)
,p_display_seq=>1
,p_column_id=>wwv_flow_imp.id(l_rowid_id)
,p_is_visible=>true
,p_is_frozen=>false
);
-- each created RegionColumn needs an IG Report Column
-- here comes the array in the game ;-)
for i in l_array.first..l_array.last loop
wwv_flow_imp_page.create_ig_report_column(
p_id => wwv_flow_imp.id(wwv_flow_id.next_val),
p_view_id => wwv_flow_imp.id(l_irgrid_rep_view_id),
p_display_seq => i+2,
p_column_id => wwv_flow_imp.id(l_array(i)),
p_is_visible => true,
p_is_frozen => false
);
end loop;
Finish it with the PAGE Process:
-- now just add the Page Process for DML Handling within the Grid
wwv_flow_imp_page.create_page_process(
p_id=>wwv_flow_imp.id(wwv_flow_id.next_val)
,p_process_sequence=>10
,p_process_point=>'AFTER_SUBMIT'
,p_region_id=>wwv_flow_imp.id(l_region_id)
,p_process_type=>'NATIVE_IG_DML'
,p_process_name=>'new - save interactive grid data'
,p_attribute_01=>'REGION_SOURCE'
,p_attribute_05=>'Y'
,p_attribute_06=>'Y'
,p_attribute_08=>'Y'
,p_error_display_location=>'INLINE_IN_NOTIFICATION'
,p_internal_uid=>wwv_flow_id.next_val
);
end;
That's it :-) Now add select Lists and an Button in your App with dynamic Action to call the procedure and your are fine..
But Enable: "Runtime API Usage" for this App also!
Happy trying!
