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

Nothing has been done for this on 4.6 or on the HEAD. It would be useful if someone who has made it work could provide the laundry list of what needs to be done. I am under the impression it's mostly the reserved word collisions for delete and some others. I don't think it's a lot of work but upgrade scripts might be pretty tough I guess.

Actually I think you can still use "delete" in 9i if you fully qualify it in the source.  I'm not sure of this, but it would be worth checking out.

If so that would give us a clean way to upgrade for those staying with 8i.  Users with custom types etc wouldn't have to clean them up until they themselves upgrade to 9i, in which case they'll get annoyed at Oracle, not us.

If I'm right, that is ...

That doesn't sound bad at all ...

For parameters I'd love to switch parameter names to the convention we began on the PG side ... p_foo rather than foo (which nicely complements the v_foo style for variables.)

But with all the calls that use named parameters that's probably impractical.  It would make things more readable ...

It's ironic that I had it backwards - it is the fully-qualified form that fails rather than the unqualified form!  That's bizarre, frankly.  Both should work within a package itself if Oracle followed normal scoping rules for Ada-ish languages, but, hell, I'm just a compiler writer, what do *I* know?

You can actually preserve the function signatures though this has a kind of band-aid quality to it. The trick is to disambiguate by declaring a new variable, assigning it to the parameter passed in, and henceforth using the new variable. So the revised content-folder.delete, for example, would look like this

procedure delete (
  folder_id     in cr_folders.folder_id%TYPE
) is

  v_count integer;
  v_parent_id integer;
*  v_folder_id integer;

begin
*  v_folder_id := folder_id;


  -- check if the folder contains any items

  [snip snip snip]

  content_folder.unregister_content_type(
*      folder_id        => v_folder_id,
      content_type     => 'content_revision',
      include_subtypes => 't' );

  delete from cr_folder_type_map
*    where folder_id = v_folder_id;

  [etc etc etc]
end delete;

where new/changed code is marked with a * (I would have used color but it looks like FONT tags are disallowed).

I used this approach on my personal site as a one-off since it's on an already-dead codebase (Classic ACS 4.2).

Oh, also there are a few cases where this approach falls down due to the use of the dot notation in cursor declarations. The trick is to declare the cursor inline (for cur in (select ...) loop) instead of in the declaration block.

Executing the following view drops the Oracle connection (end of communciation).

I followed up all the views and they all seem to work indvidually. Then I tried to play around with the outer joins and the view works if I take out the second outer join(party_id). Is there something that I missiing in Oracle 9i (ver 9.2)? May be sombody would point me in the right direction?

CREATE OR REPLACE VIEW REL_CONSTRAINTS_VIOLATED_ONE ( CONSTRAINT_ID, 
CONSTRAINT_NAME, REL_ID, CONTAINER_ID, PARTY_ID, 
REL_TYPE, REL_SEGMENT, REL_SIDE, REQUIRED_REL_SEGMENT
 ) AS select constrained_rels.constraint_id, constrained_rels.constraint_name,
   constrained_rels.rel_id, constrained_rels.container_id,
   constrained_rels.party_id, constrained_rels.rel_type,
   constrained_rels.rel_segment,constrained_rels.rel_side,
   constrained_rels.required_rel_segment
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 rspm.party_id(+) = constrained_rels.container_id

OACS is still not ported to Oracle 9i.

My work on porting is stalled for now. I have been having aolserver4 problems on Solaris and my attempt to port is stalled and have little time this week to look at it. I am also going away for a month, so if someone else is willing to volunteer for the Oracle 9i porting work, please let me know.

/Mohan

Hazi,

How are you executing this, from within AOLserver or sqlplus? I tried it on our 9i db from sqlplus and it works fine.

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

BTW, I still think it's a good practice to include all the actual column names in the views; I was just too lazy (constrained_rels.*) to follow my own suggestions, I guess 😉

I am going through all the views and changing * to actual column names.

Don, what level of testing is required for our modifcations to be included in OpenACS release. Do we need to test all packages?

Well it seems the problem is that even though the user is dropped and recreated some things stay in the background until they're cleaned up by Oracle.
See for more on this:
http://www.dbaclick.com/forums/archive/20/1422.html
(Ciaran DeBuitlear found this solution)

I had to wait for an hour after dropping user, and then the install continued without above error (still failed though for other reasons) ie.

*** Initializing content repository hierarchy...
declare
*
ERROR at line 1:
ORA-02291: integrity constraint (ENKE_KM.ACS_OBJECTS_CONTEXT_ID_FK) violated -
parent key not found
ORA-06512: at "ENKE_KM.ACS_OBJECT", line 68
ORA-06512: at "ENKE_KM.CONTENT_ITEM", line 146
ORA-06512: at "ENKE_KM.CONTENT_FOLDER", line 34
ORA-06512: at line 5

and then it stopped at:

Completing Install sequence by mounting the main site and other core packages.

SQL*Plus: Release 8.1.7.0.0 - Production on Fri May 23 16:30:33 2003
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.3.0 - Production
With the Partitioning option
JServer Release 9.0.1.3.0 - Production
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "ENKE_KM.ACS", line 90
ORA-06512: at line 9