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

Collapse
Posted by Sung Hong on
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
Posted by Barry Books on
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
Posted by Sung Hong on
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
Posted by Barry Books on
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
Posted by Sung Hong on
OpenACS 5.1 with the patch installed successfully in Oracle 9i.  Thanks!