Monday 16 April 2018

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:



3 comments:

  1. Hello,
    Great post! I am see the programming coding and step by step execute the outputs.I am gather this coding more information. It's helpful for me. Also great blog here with all of the valuable information you have. Oracle EBS Training

    ReplyDelete
  2. I just do not really know how to get l_item_template_id and the other parm needed, could you help me please

    ReplyDelete
  3. Are you sure we can use wwv_* packages and tables? As per oracle they are undocumented and can be changed any time. Do you have any better way of doing it using apex_* packages?

    ReplyDelete