Re: Some bugs with Oracle

[Thomas Dudziak <to...@gmail.com>]

On 2/23/06, Hans Muñoz <Ha...@digibis.com> wrote:
> >> ----Getting DDL and generation xml from the database----
> >> - Fails to remove internal primary keys (JDBCModelReader.java in
> >> removeInternalPrimaryKeyIndex:629 - always false) So it fails while
> triyng
> >> to Create tables from xml generated, because is duplicated.
>
> >? Could you post the table definition SQL, and the generated XML ?
> I think the problem is in the method removeInternalPrimaryKeyIndex as long
> as it only have one line... return false; so it can never remove the
> duplicate index.
> The original Database hasn't been created with ddlUtils, the SQL definition
> of one table is:
>
> ALTER TABLE ACCESOS DROP PRIMARY KEY CASCADE;
> DROP TABLE ACCESOS CASCADE CONSTRAINTS;
>
> CREATE TABLE ACCESOS
> (
>   IDACCESO    NUMBER(10)                        NOT NULL,
>   USUARIO     VARCHAR2(255 BYTE)                NOT NULL,
>   OPERACION   VARCHAR2(255 BYTE)                NOT NULL,
>   FECHA       DATE                              NOT NULL,
>   ADICIONAL1  NUMBER(10),
>   ADICIONAL2  VARCHAR2(255 BYTE),
>   NUMERO      NUMBER(10)                        DEFAULT 1
> NOT NULL
> )
> TABLESPACE BIBDESA_DAT
> LOGGING
> NOCACHE
> NOPARALLEL;
>
>
> CREATE UNIQUE INDEX PK_ACCESOS ON ACCESOS
> (IDACCESO)
> LOGGING
> TABLESPACE BIBDESA_IND
> NOPARALLEL;
>
>
> CREATE OR REPLACE TRIGGER TRG_ACCESOS
> BEFORE INSERT
> ON ACCESOS
> REFERENCING NEW AS NEW OLD AS OLD
> FOR EACH ROW
> BEGIN
>    IF :new.IDACCESO IS NULL THEN
>           SELECT SEQ_ACCESOS.NEXTVAL INTO :new.IDACCESO FROM dual;
>    END IF;
> END ;
> /
> SHOW ERRORS;
>
>
>
> ALTER TABLE ACCESOS ADD (
>   CONSTRAINT PK_ACCESOS PRIMARY KEY (IDACCESO)
>     USING INDEX
>     TABLESPACE BIBDESA_IND);

This is not an internal index - "internal index" refers to indices
that the database creates automatically (i.e. you don't specify them)
for primary keys or foreign keys, and which are returned in the JDBC
metadata. Oracle does not define such indices (or at least they are
not present in JDBC) and hence the methods simply contain a "return
false". Your index however is created manually via SQL and thus is not
internal but rather a normal unique index (which plays the role of the
primary key as the table in question does not seem to contain one
which would be unique automatically).

> >Mhmm, this works on Oracle 10. What driver do you use ?
> Oracle 9i.

In general you should use the newest JDBC driver available because
they are usually downward compatible (Oracle drivers are) and contain
important bugfixes. In your case, you should use the driver that can
be downloaded from the Oracle 10 site.


As for the other things, I'll try to get an Oracle 9 installation
running in the next days and then check against it.

Tom