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
Hi,

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.

Outcomes