Building Dynamic Regions in Oracle APEX using APEX API


Modifying
region queries/source dynamically can be done easily, whereas building regions
dynamically can be done only using APEX API.
Using
the below code, a static region and a chart region shall be created. Modify the
code based on the requirement of the region type.
This
can also be used as a plugin by uploading the below SQL script in the
application and calling the plugin in the required page with required inputs.
Steps:
Create
a procedure with input parameters as given in the below code.
Code:
CREATE OR REPLACE PROCEDURE test_api_chart_region (
   iv_n_template_id        IN   NUMBER,
   iv_n_item_template_id   IN   NUMBER,
   iv_n_page_no            IN   NUMBER
)
AS
   l_static_region_id   NUMBER := wwv_flow_id.next_val;
   l_region_id          NUMBER := wwv_flow_id.next_val;
   l_template_id        NUMBER := iv_n_template_id;   –77632827835402642885;
   –give your ID from APEX API tables
   l_item_template_id   NUMBER := iv_n_item_template_id;
                                                     
–77632848475788642917;
   –give your ID from APEX API tables
   l_page_id            NUMBER := iv_n_page_no;          
–give your page ID
   l_chart_id           NUMBER := wwv_flow_id.next_val;
   l_series_id          NUMBER := wwv_flow_id.next_val;
   l_xaxis_id           NUMBER := wwv_flow_id.next_val;
   l_yaxis_id           NUMBER := wwv_flow_id.next_val;
   l_item_id            NUMBER := wwv_flow_id.next_val;
BEGIN
   wwv_flow_api.create_page_plug
              (p_id                              => l_static_region_id,
               p_flow_id                         => v (‘APP_ID’),
               p_page_id                         => l_page_id,
               p_plug_name                       => ‘TestRegion’,
               p_region_template_options         => ‘#DEFAULT#:t-Region–scrollBody’,
               p_plug_template                   => l_template_id,
               p_plug_display_sequence           => 10,
               p_include_in_reg_disp_sel_yn      => ‘Y’,
               p_plug_display_point              => ‘BODY’,
               p_plug_query_options              => ‘DERIVED_REPORT_COLUMNS’,
               p_attribute_01                    => ‘N’,
               p_attribute_02                    => ‘HTML’
              );
   wwv_flow_api.create_page_plug
      (p_id                              => l_region_id,
       p_flow_id                         => v (‘APP_ID’),
       p_page_id                         => l_page_id,
       p_plug_name                       => ‘Chart1’,
       p_region_template_options         => ‘#DEFAULT#:t-Region–scrollBody’,
       p_escape_on_http_output           => ‘Y’,
       p_plug_template                   => l_template_id,
       p_plug_display_sequence           => 20,
       p_include_in_reg_disp_sel_yn      => ‘Y’,
       p_plug_display_point              => ‘BODY’,
       p_plug_source                     => wwv_flow_string.JOIN
                                               (wwv_flow_t_varchar2
                                                  
(‘SELECT NULL LINK,’,
                                                   
       deptno LABEL,’,
                                                   
       SAL VALUE’,
                                                   
,
                                                   
‘FROM   EMP’,
                                                   
‘where SAL between 1000 and
3000’
                                                                                    /*,
                                                  
‘and MGR = :P2000_X’*/
                                                  
)
                                               ),
       p_plug_source_type                => ‘NATIVE_JET_CHART’,
       p_plug_query_num_rows             => 15,
       p_plug_query_options              => ‘DERIVED_REPORT_COLUMNS’
      );
   wwv_flow_api.create_jet_chart (p_id                            => l_chart_id,
                                  p_region_id                     => l_region_id,
                                 
p_chart_type                   
=> ‘bar’,
                                 
p_animation_on_display         
=> ‘none’,
                                  p_animation_on_data_change      => ‘none’,
                                 
p_orientation                  
=> ‘vertical’,
                                 
p_data_cursor                  
=> ‘auto’,
                                 
p_data_cursor_behavior         
=> ‘auto’,
                                 
p_hide_and_show_behavior       
=> ‘none’,
                                 
p_hover_behavior               
=> ‘none’,
                                  p_stack                         => ‘off’,
                                  p_zoom_and_scroll               => ‘off’,
                                 
p_tooltip_rendered             
=> ‘Y’,
                                 
p_show_series_name             
=> TRUE,
                                  p_show_group_name               => TRUE,
                                 
p_show_value                   
=> TRUE,
                                 
p_show_label                   
=> TRUE,
                                 
p_legend_rendered              
=> ‘on’,
                                 
p_legend_position              
=> ‘auto’
                                 );
   wwv_flow_api.create_jet_chart_series
                                       (p_id                           => l_series_id,
                                        p_chart_id                     => l_chart_id,
                                       
p_seq                         
=> 10,
                                       
p_name                        
=> ‘New’,
                                       
p_data_source_type            
=> ‘REGION_SOURCE’,
                                       
p_items_value_column_name     
=> ‘VALUE’,
                                       
p_items_label_column_name     
=> ‘LABEL’,
                                       
p_assigned_to_y2              
=> ‘off’,
                                       
p_items_label_rendered        
=> FALSE
                                       );
   wwv_flow_api.create_jet_chart_axis
(p_id                       => l_xaxis_id,
                                      
p_chart_id                
=> l_chart_id,
                                      
p_axis                    
=> ‘x’,
                                      
p_is_rendered             
=> ‘on’,
                                      
p_format_scaling          
=> ‘auto’,
                                      
p_scaling                 
=> ‘linear’,
                                      
p_baseline_scaling        
=> ‘zero’,
                                      
p_major_tick_rendered     
=> ‘on’,
                                      
p_minor_tick_rendered     
=> ‘off’,
                                      
p_tick_label_rendered     
=> ‘on’,
                                       p_tick_label_rotation      => ‘auto’,
                                      
p_tick_label_position     
=> ‘outside’
                                      );
   wwv_flow_api.create_jet_chart_axis
(p_id                       => l_yaxis_id,
                                       p_chart_id                 => l_chart_id,
                                      
p_axis                    
=> ‘y’,
                                      
p_is_rendered             
=> ‘on’,
                                       p_format_scaling           => ‘auto’,
                                      
p_scaling                 
=> ‘linear’,
                                      
p_baseline_scaling        
=> ‘zero’,
                                      
p_position                 
=> ‘auto’,
                                      
p_major_tick_rendered     
=> ‘on’,
                                      
p_minor_tick_rendered     
=> ‘off’,
                                      
p_tick_label_rendered     
=> ‘on’
                                      );
END;
Call the procedure by passing the
required parameters in the page on required event.

Output:

  • April 16, 2018 | 20 views
  • Comments