AnsweredAssumed Answered

Installing 1.4PreviewRel with Oracle - SQL script Errors

Question asked by gnewtonaus on Sep 24, 2006
Latest reply on Sep 25, 2006 by gnewtonaus

I have been trying for hours to get 1.4 preview release to work with Oracle (Express Ed, which is same as 10g and 9i for most purposes).

I can get Alfresco running ok if I stick to defaults and run it with MySQL, however the enterprise I work for here in Sydney (Leightons), will only allow the use of Oracle for the database.

I have worked through the Wiki doco. When I start Alfresco for the first time, after making the changes, the first line starts with "CompileOracle…" (or something to that effect) - indicating to me that it recognises I've configured it for Oracle.

It then displays other messages followed by a lot of Hibernate warnings (which it also did during the default install using MySQL). It then tries to execute a database script in the \temp\Alfresco directory called AlfrescoSchemaCreateXXXX.sql. This seems to contain errors and everything turns to mud from this point on.

After this, if I try to run that script directly in the Oracle alfresco schema, I get errors. There appears to be a table column named after an Oracle reserved word ("date"). These lines generate errors and the script fails. If I add double-quotes around the column name (because it's named using an Oracle reserved word: date), then the script runs fine - but of course this is too late for my Alfresco install.

The 2 faulty parts seem to be:-

    create table alf_audit_date (
        id number(19,0) not null,
        date date not null,
        day_of_year number(10,0) not null,
        day_of_month number(10,0) not null,
        day_of_week number(10,0) not null,
        week_of_year number(10,0) not null,
        week_of_month number(10,0) not null,
        month number(10,0) not null,
        quarter number(10,0) not null,
        halfYear number(10,0) not null,
        year number(10,0) not null,
        primary key (id)

The third line of this should have quotes around the column name:-
        "date" date not null,

Then further down:-
    create index adt_date_idx on alf_audit_date (date);

Should read:-
    create index adt_date_idx on alf_audit_date ("date");

or alternatively the column could be named using an underscore like all the other similarly named columns elsewhere (I assume so the scripts work in different types of databases).

I can't find where the original for this apparently-generated-during-the-install sql script is, or I would fix it myself.

It would be great if someone could fix this so I can get this running under Oracle.

Thanks in advance.

Greg Newton,
Leighton Group
Sydney, Australia.