rewrite database rules in new format

[OliverLemm]

Based on the restruction of the qa_main_pkg and the type qa_rule_t all rules have to be adjusted.

• No more access to the table QA_RULES
• Type has to be called with Parameters PI_...
• Overall the Query needs every Parameter from the rule as an IN Parameter (see :1, :2, ....)
• Every call to user_.... should be all_...

The new rule format is:

with param as
(select :1 scheme
,:2 qaru_id
,:3 qaru_category
,:4 qaru_error_level
,:5 qaru_object_types
,:6 qaru_error_message
,:7 qaru_sql
from dual)
select qa_rule_t(pi_qaru_id => p.qaru_id
,pi_qaru_category => p.qaru_category
,pi_qaru_error_level => p.qaru_error_level
,pi_qaru_error_message => p.qaru_error_message
,pi_qaru_object_types => p.qaru_object_types
,pi_qaru_sql => p.qaru_sql
,pi_object_id => uo.object_id
,pi_object_name => ut.table_name
,pi_object_type => 'TABLE'
,pi_object_value => null
,pi_object_updated_user => null
,pi_object_updated_date => uo.last_ddl_time)
from param p
cross join user_tables ut
join all_objects uo on ut.table_name = uo.object_name
where (uo.owner = p.scheme or p.scheme is null)
and not exists (select null
from all_constraints cons
join all_cons_columns cols on cols.constraint_name = cons.constraint_name
where cols.table_name = ut.table_name
and cons.constraint_type = 'P'
and cons.status = 'ENABLED')

more details in the internal wiki

[PhilippDahlem]

Following Categorys have been modified and set to active

• DDL
• DATA