You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Robert Björn <ob...@robertb.eu> on 2007/09/13 18:50:14 UTC
System tables and foreign key constraints issues with Oracle
Hi,
I'm having two problems with DdlUtils in conjunction with Oracle. I've
saved an XML representation of an existing database using
readModelFromDatabase(), added some foreign key constraints to the
XML, and am trying to modify the target database using the
alterDatabase() method.
a) The first problem is that while specifying the table types in the
readModelFromDatabase) call works fine -- I supply only the "TABLES"
type to avoid getting the system tables also -- this does not work for
alterDatabase(). As stated in the documentation, that argument affects
only what is being read and not what is being modified in the target
database. The result is that DdlUtils wants to remove all of the
system tables. I thus get "Error while executing SQL DROP TABLE
AUDIT_ACTIONS CASCADE CONSTRAINTS".
b) The second problem is that the foreign key constraints that I added
seem to result in DdlUtils wanting to add indices, for some reason
resulting in "Cannot write unnamed index".
Log snippet below. I am running DdlUtils 1.0 and Oracle 10g Enterprise
10.2.0.3.0.
Have browsed the docs and website extensively to no avail. Any help or
advice would be highly appreciated.
Best Regards,
Robert
---
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Foreign key Foreign key [foreign table=TBL_WORKSTATION; 1 references]
needs to be created for table TBL_DEVICE
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Index null needs to be created for table TBL_DEVICE
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Foreign key Foreign key [foreign table=TBL_WORKSTATION; 1 references]
needs to be created for table TBL_DEVICE_ASSOCIATION
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Index null needs to be created for table TBL_DEVICE_ASSOCIATION
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Index null needs to be created for table TBL_WORKSTATION
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table AUDIT_ACTIONS needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table DUAL needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table HELP needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table IMPDP_STATS needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table MAP_OBJECT needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table PSTUBTBL needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table STMT_AUDIT_OPTION_MAP needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table SYSTEM_PRIVILEGE_MAP needs to be removed
356312 [main] INFO org.apache.ddlutils.alteration.ModelComparator -
Table TABLE_PRIVILEGE_MAP needs to be removed
356343 [main] WARN org.apache.ddlutils.platform.SqlBuilder -
Encountered a foreign key in table TBL_DEVICE that has no name.
DdlUtils will use the auto-generated and shortened name
TBL_DEVICE_FK_W_BL_WORKSTATION instead.
356343 [main] WARN org.apache.ddlutils.platform.SqlBuilder - Cannot
write unnamed index Unique index [name=null; 1 columns]
356343 [main] WARN org.apache.ddlutils.platform.SqlBuilder -
Encountered a foreign key in table TBL_DEVICE_ASSOCIATION that has no
name. DdlUtils will use the auto-generated and shortened name
TBL_DEVICE_ASSO_BL_WORKSTATION instead.
356343 [main] WARN org.apache.ddlutils.platform.SqlBuilder - Cannot
write unnamed index Unique index [name=null; 1 columns]
356343 [main] WARN org.apache.ddlutils.platform.SqlBuilder - Cannot
write unnamed index Unique index [name=null; 3 columns]
Re: System tables and foreign key constraints issues with Oracle
Posted by Robert Björn <ro...@objective.se>.
Hi Thomas,
That solved both of the problems. I thought that I had already tested
specifying the schema but I can only assume that I made some mistake
the first time. Thanks!
Regards,
Robert
On 9/14/07, Thomas Dudziak <to...@gmail.com> wrote:
> On 9/13/07, Robert Björn <ob...@robertb.eu> wrote:
>
> > a) The first problem is that while specifying the table types in the
> > readModelFromDatabase) call works fine -- I supply only the "TABLES"
> > type to avoid getting the system tables also -- this does not work for
> > alterDatabase(). As stated in the documentation, that argument affects
> > only what is being read and not what is being modified in the target
> > database. The result is that DdlUtils wants to remove all of the
> > system tables. I thus get "Error while executing SQL DROP TABLE
> > AUDIT_ACTIONS CASCADE CONSTRAINTS".
>
> For Oracle, you should specify the database schema when using Ant or
> the API (using the method variants that allow to specify the catalog
> and the schema). This way, system tables won't be visible to DdlUtils.
>
> > b) The second problem is that the foreign key constraints that I added
> > seem to result in DdlUtils wanting to add indices, for some reason
> > resulting in "Cannot write unnamed index".
>
> Could you provide some more info, e.g. the XML schema and the Ant
> build file or the code snippet if you're using the API ?
>
> Tom
>
Re: System tables and foreign key constraints issues with Oracle
Posted by Thomas Dudziak <to...@gmail.com>.
On 9/13/07, Robert Björn <ob...@robertb.eu> wrote:
> a) The first problem is that while specifying the table types in the
> readModelFromDatabase) call works fine -- I supply only the "TABLES"
> type to avoid getting the system tables also -- this does not work for
> alterDatabase(). As stated in the documentation, that argument affects
> only what is being read and not what is being modified in the target
> database. The result is that DdlUtils wants to remove all of the
> system tables. I thus get "Error while executing SQL DROP TABLE
> AUDIT_ACTIONS CASCADE CONSTRAINTS".
For Oracle, you should specify the database schema when using Ant or
the API (using the method variants that allow to specify the catalog
and the schema). This way, system tables won't be visible to DdlUtils.
> b) The second problem is that the foreign key constraints that I added
> seem to result in DdlUtils wanting to add indices, for some reason
> resulting in "Cannot write unnamed index".
Could you provide some more info, e.g. the XML schema and the Ant
build file or the code snippet if you're using the API ?
Tom