Blitz Report Parameter Bind Variable Validation

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
This report can be used to check which :bind variables were assigned to which Blitz Report parameter, in case there are more than one :binds in the parameter sql text, or in case the same :bind variable name is incorrectly used in different parameters.
select z.* from (
select
xrpv.report_name,
xrpv.category,
xrpv.display_sequence,
xrpv.parameter_name,
xrpv.anchor,
xxen_api.bindvar_name(xrpv.parameter_id) bindvar_name,
xxen_report.is_select_(xrpv.sql_text) is_dynamic_sql_text,
xrpv.sql_text,
y.binds,
y.bind_count,
xrpv.parameter_id
from
xxen_report_parameters_v xrpv,
(
select distinct
x.parameter_id_display,
count(*) over (partition by x.parameter_id_display) bind_count,
listagg(x.bind,', ') within group (order by x.bind) over (partition by x.parameter_id_display) binds
from
(
select distinct
xrplv.parameter_id_display,
lower(dbms_lob.substr(regexp_substr(xrplv.sql_text,':\w+',1,rowgen.column_value))) bind
from
(select xrplv.parameter_id_display, xxen_report.clear_text(xrplv.sql_text) sql_text from xxen_report_parameters_link_v xrplv where 1=1) xrplv,
table(xxen_util.rowgen(regexp_count(xrplv.sql_text,':\w+'))) rowgen
) x
) y
where
2=2 and
xrpv.display_sequence is not null and
xrpv.parameter_id=y.parameter_id_display
) z
where
3=3
order by
z.report_name,
z.display_sequence
Parameter NameSQL textValidation
Category
xrpv.report_id in (
select
xrca.report_id
from
xxen_report_category_assigns xrca,
xxen_report_categories_v xrcv
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id
)
LOV
Category is not
xrpv.report_id not in (
select
xrca.report_id
from
xxen_report_category_assigns xrca,
xxen_report_categories_v xrcv
where
xrcv.category=:category_is_not and
xrcv.category_id=xrca.category_id
)
LOV
Report Name
xrpv.report_name=:report_name
LOV
Parameters with missing :binds
z.bindvar_name is null
LOV
Parameters with multiple :binds
y.bind_count>1
LOV
Download
Related Blitz Reports
GL Account Distribution Analysis Blitz Report Parameter Table Alias Validation Blitz Report LOV Comparison between environments Blitz Report Parameter Comparison between environments Blitz Report Comparison between environments CAC Receiving Expense Value (Period-End) CAC Material Account Alias with Lot Numbers CAC Interface Error Summary DIS End User Layers
Blitz Report™