Forum OpenACS Q&A: Re: OpenACS 4.6 does not yet work with Oracle 9i?

Collapse
Posted by Simos Gabrielidis on
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