Forum OpenACS Q&A: OpenACS 5.1 in Oracle 9i

Collapse
1: OpenACS 5.1 in Oracle 9i
Posted by Sung Hong on 06/26/04 12:46 AM
The automatic installation of Content Repository 5.1.0 in Oracle 9i failed:

...
Trigger created.
No errors.
Package body created.
No errors.
*** Refreshing content type attribute views...
PL/SQL procedure successfully completed.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY CONTENT_ITEM:
LINE/COL ERROR
-------- -----------------------------------------------------------------
600/6553 PL/SQL: SQL Statement ignored
0
605/36     PL/SQL: ORA-01747: invalid user.table.column, table.column, or
     column specification
621/5     PL/SQL: SQL Statement ignored
622/35     PL/SQL: ORA-01747: invalid user.table.column, table.column, or
     column specification
Trigger created.
No errors.
Trigger created.
No errors.
...

Does anyone try to install it using Oracle 9i?

Collapse
2: Re: OpenACS 5.1 in Oracle 9i (response to 1)
Posted by Barry Books on 06/28/04 01:34 PM
Try this patch

diff -r acs-content-repository/sql/oracle/content-item.sql /usr/local/CVS/openacs-4-all/openacs-4/packages/acs-content-repository//sql/oracle/content-item.sql
611,614d610
<  v_item_id cr_items.item_id%TYPE;
<  v_name    cr_items.name%TYPE;
<
<
621c617
<      cr_items.name = v_name
---
<blockquote>      cr_items.name = content_item.rename.name
</blockquote>
628c624
<                    cr_items.item_id = v_item_id);
---
<blockquote>                    cr_items.item_id = content_item.rename.item_id);
</blockquote>
639,640c635,636
<      set cr_items.name = v_name
<              where cr_items.item_id = v_item_id;
---
<blockquote>      set cr_items.name = content_item.rename.name
              where cr_items.item_id = content_item.rename.item_id;
</blockquote>

Collapse
3: Re: OpenACS 5.1 in Oracle 9i (response to 2)
Posted by Sung Hong on 06/28/04 05:28 PM
Thank you for the patch but I cannot find the second difference.  Would you please send me the patched file to mailto:shhong@mednet.ucla.edu?  I will test it and post the corresponding result.
Collapse
4: Re: OpenACS 5.1 in Oracle 9i (response to 1)
Posted by Barry Books on 06/28/04 08:20 PM
it's the rename procedure that's broken

procedure rename (
  item_id in cr_items.item_id%TYPE,
  name    in cr_items.name%TYPE
) is
  v_item_id cr_items.item_id%TYPE;
  v_name    cr_items.name%TYPE;


  cursor exists_cur is
    select
      item_id
    from
      cr_items
    where
      cr_items.name = v_name
    and
      parent_id = (select
                    parent_id
                  from
                    cr_items
                  where
                    cr_items.item_id = v_item_id);

  exists_id integer;
begin

  open exists_cur;
  fetch exists_cur into exists_id;

  if exists_cur%NOTFOUND then
    close exists_cur;
    update cr_items
        set cr_items.name = v_name
        where cr_items.item_id = v_item_id;
  else
    close exists_cur;
    if exists_id <> item_id then
      raise_application_error(-20000,
        'An item with the name ' || name ||
        ' already exists in this directory.');
    end if;
  end if;

end rename;

Collapse
5: Re: OpenACS 5.1 in Oracle 9i (response to 4)
Posted by Sung Hong on 06/30/04 05:15 PM
OpenACS 5.1 with the patch installed successfully in Oracle 9i.  Thanks!