Use the Table from SQL report element to create a table in a generated report using a result set returned from a SQL SELECT statement or a PL/SQL block.
Targets specified for this element on the Set Parameters page override target settings specified on report definition General page.
You can also specify the number of rows that should be shown at one time in the display table. Table navigation controls are added to navigate through the data set. The maximum total number of rows that will be retrieved is 2000.
Click on column headers to sort data in the table.
The following example shows how to structure your SQL statement (or PL/SQL block).
select target_name "Name", type_display_name "Type"
from mgmt$target
where target_type = 'oracle_database'
Output Example:
If you have a more complicated query or need to use logical constructs in your SQL statement, you have the option of supplying a PL/SQL block to retrieve the result set. Select the “PL/SQL” option if you are supplying a PL/SQL block.
The result set should be returned in a cursor which is bound in your PL/SQL with the following bind variable placeholder: ??EMIP_BIND_RESULTS_CURSOR??.
Information Publisher will bind an output cursor in your statement where the bind variable placeholder is located.
The following example shows a PL/SQL statement that is used to generate a table with a count of each target type in your management database.
DECLARE
TYPE CURSOR_TYPE IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
BEGIN
result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
OPEN result_cursor_out for
select type_display_name "Name", count(*) "Count"
from mgmt$target
group by type_display_name
order by count(*) desc;
END;
Example:
You can take advantage of a dynamically supplied target and time period by including bind variable placeholders in your SQL or PL/SQL statement.
SQL:
select distinct target_name "Target",
metric_label||':'||column_label "Metric",
metric_type "Type"
from mgmt$target_type
where target_guid = ??EMIP_BIND_TARGET_GUID??
Example:
Additionally, your SQL or PL/SQL statement can bind the time period associated with a report. The following bind variable placeholders contain information about the time period.
??EMIP_BIND_START_DATE?? – Start date of the time period.
??EMIP_BIND_END_DATE?? – End date of the time period.
??EMIP_BIND_TIMEZONE_REGION?? – Timezone region of the time period.
Any date values returned in your result set should be returned as dates in the time zone region of the time period. This will ensure that the report viewer can interpret all dates properly as the time zone of the time period will be displayed on the report.
Alternatively, if choose not to convert dates to the time zone region of the
time period you can explicitly display the time zone associated with each date
in an additional column.
The following example shows a SQL statement used to generate a table with dynamic date bind variable placeholders:
select distinct hist.target_name "Name",
hist.alert_state "State",
hist.alert_duration "Duration",
hist.message "Message"
from mgmt$alert_history hist, mgmt$target tgt
where
tgt.target_guid = hist.target_guid
and hist.collection_timestamp >
mgmt_view_util.adjust_tz(??EMIP_BIND_START_DATE??,
??EMIP_BIND_TIMEZONE_REGION??, tgt.timezone_region)
and hist.collection_timestamp <
mgmt_view_util.adjust_tz(??EMIP_BIND_END_DATE??,
??EMIP_BIND_TIMEZONE_REGION??, tgt.timezone_region)
order by mgmt_view_util.adjust_tz(hist.collection_timestamp, tgt.timezone_region,
??EMIP_BIND_TIMEZONE_REGION??) desc
Example:
Enterprise Manager Information Roadmap
Copyright © 1996, 2007, Oracle. All rights reserved.