Hello Hazi,
I think this is a bug in the SQL code of the rel_constraints_violated_one
and rel_constraints_violated_two
views; it seems that they use the equality operator(=) on NULL columns. However, because of tri-value logic in SQL, NULL=NULL always fails.
I believe the two view should be re-written as following (note the code in bold):
create or replace view rel_constraints_violated_one as select constrained_rels.*
from (select rel_constraints.constraint_id, rel_constraints.constraint_name,
r.rel_id, r.container_id, r.party_id, r.rel_type,
rel_constraints.rel_segment,
rel_constraints.rel_side,
rel_constraints.required_rel_segment
from rel_constraints, rel_segment_party_map r
where rel_constraints.rel_side = 'one'
and rel_constraints.rel_segment = r.segment_id
) constrained_rels,
rel_segment_party_map rspm
where rspm.segment_id(+) = constrained_rels.required_rel_segment
and constrained_rels.container_id is null
and rspm.party_id is null;
create or replace view rel_constraints_violated_two as
select constrained_rels.*
from (select rel_constraints.constraint_id, rel_constraints.constraint_name,
r.rel_id, r.container_id, r.party_id, r.rel_type,
rel_constraints.rel_segment,
rel_constraints.rel_side,
rel_constraints.required_rel_segment
from rel_constraints, rel_segment_party_map r
where rel_constraints.rel_side = 'two'
and rel_constraints.rel_segment = r.segment_id
) constrained_rels,
rel_segment_party_map rspm
where rspm.segment_id(+) = constrained_rels.required_rel_segment
and constrained_rels.party_id is null
and rspm.party_id is null;
See also an old thread
Installation of ACS 4.2 and Oracle 9i on Linux