1 Reply Latest reply on May 20, 2010 6:47 PM by 455683

    How to pass bind variable in report

    user11974118
      I'm trying to add tables and chart based of the value selected by the user using the filters.

      l_param_values := MGMT_IP_PARAM_VALUE_LIST();
      l_param_values.extend(13);
      l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT TO_DATE(TO_CHAR (alloc.timestamp, ''MON RR''),''MON RR'') AS CALENDAR_MONTH,ROUND(AVG(alloc.avg_size_mb/1024),2) AS SIZE_GB,ROUND(AVG(used.avg_used_mb/1024), 2) AS USED_GB,
      ROUND (AVG ((alloc.avg_size_mb - used.avg_used_mb )/1024), 2) AS FREE_GB
      FROM
      (SELECT m.metric_column AS metric_column,m.rollup_timestamp AS timestamp,sum (m.average) AS avg_size_mb
      FROM
      mgmt$metric_daily m, mgmt$target_type t
      WHERE t.target_name LIKE ??EMIP_BIND_PARAMNAME??
      AND (t.target_type = ''rac_database''
      OR (t.target_type = ''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST''))
      AND m.target_guid = t.target_guid AND m.metric_guid = t.metric_guid AND t.metric_name = ''tbspAllocation''
      AND (t.metric_column = ''spaceAllocated'')
      and m.rollup_timestamp >= ??EMIP_BIND_START_DATE??
      and m.rollup_timestamp <= ??EMIP_BIND_END_DATE??
      GROUP BY m.rollup_timestamp, m.metric_column) alloc,
      ( SELECT m.metric_column AS metric_column, m.rollup_timestamp AS timestamp, sum (m.average) AS avg_used_mb
      FROM mgmt$metric_daily m, mgmt$target_type t
      WHERE t.target_name = ??EMIP_BIND_PARAMNAME??
      AND (t.target_type = ''rac_database''
      OR (t.target_type = ''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST''))
      AND m.target_guid = t.target_guid AND m.metric_guid = t.metric_guid AND t.metric_name = ''tbspAllocation''
      AND (t.metric_column = ''spaceUsed'')
      and m.rollup_timestamp >= ??EMIP_BIND_START_DATE??
      and m.rollup_timestamp <= ??EMIP_BIND_END_DATE??
      GROUP BY m.rollup_timestamp, m.metric_column) used
      WHERE alloc.timestamp = used.timestamp
      GROUP BY TO_CHAR (alloc.timestamp, ''MON RR'') order by 1');
      l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterNames','NAME');
      l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterPromptNAME','Database Name');
      l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterTipNAME','Filter on the target names');
      l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterPromptTYPE','Target Type');
      l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterTipTYPE','Filter on the target types');
      l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterStartEmpty', 'yes');
      l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterEmptyTableHeaders','Calender Month, Size GB,Used GB,Free GB');
      l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterSqlNAME',
      'select distinct t.target_name from mgmt$target t where (t.target_type = ''rac_database'' OR (t.target_type = ''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) order by 1');
      l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.numberOfColumnsShowed','4');
      l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterHeaderText','DB Filter');
      l_param_values(12) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterTipText','My Tip Text');
      l_param_values(13) := MGMT_IP_PARAM_VALUE_RECORD (
      'oracle.sysman.eml.ip.render.elem.TableRender.filterButtonText','Get Growth');

      l_element_guid := mgmt_ip.add_element_to_report_def (
      p_report_guid => l_report_guid,
      p_element_name_nlsid => 'user_table_from_sql',
      p_element_type_nlsid => 'any_target_type',
      p_header_nlsid => 'Select DB for 6 Month Growth Summary',
      p_element_order => 1,
      p_element_row => 1,
      p_parameters => l_param_values,
      p_targets => null);

      ..

      Is there a way to pass the EMIP_BIND_PARAMNAME to a new element added to the report ?
        • 1. Re: How to pass bind variable in report
          455683
          The report framework dev response was that the code was not designed for this use case (e.g. a report filter bind parameter used in multiple report elements). They said it is possible that this may work by chance (go ahead and try to pass the same filter parameters into other report elements and see what happens). If that does not work then you can work with your Oracle support contact to file an enhancement request.

          Edited by: cfowler on May 20, 2010 2:46 PM