Order of generated objects in Oracle SQL scripts

[Jan Holub]

Dear colleagues,
I've found serious issue when generated Package DDL.
Let's assume having 3 objects:
table: Table1
view: ZZ (selecting from Table1)
view: AA (selecting from view ZZ)

After generation of DDL script is Table1 generated as first object (so far, so good), but the views are always generated in alphabetic order(!) In my case the ZZ view should be generated first, the AA view (depending on ZZ view) as second. But it's ordered alphabetically. This wrong ordering causes Oracle compilation error of the AA view, because ZZ view doesn't exists in database yet.
Have you got any intention how to workaround this?

Generated script included.

Thank you,
Jan Holub
IS Department/Development division
Czech National Bank
Na Prikope 28
Prague, Czech Republic

--   --------------------------------------------------
--   Generated by Enterprise Architect Version 7.5.848
--   Created On : ptek, 04 z, 2009
--   DBMS       : Oracle
--   --------------------------------------------------



--  Drop Tables, Stored Procedures and Views
DROP VIEW AA CASCADE CONSTRAINTS;
DROP TABLE Table1 CASCADE CONSTRAINTS;
DROP VIEW ZZ CASCADE CONSTRAINTS;

--  Create Tables
CREATE TABLE Table1
(
      ID  NUMBER(18) NOT NULL
);

--  Create Primary Key Constraints
ALTER TABLE Table1 ADD CONSTRAINT Table1_PK
      PRIMARY KEY (ID)
 USING INDEX ;

--  Create Views
CREATE OR REPLACE VIEW AA
AS
select * from ZZ;

CREATE OR REPLACE VIEW ZZ
AS
select * from Table1;

[Geert Bellekens]

I would report this as a bug to Sparx and ask them for a workaround.
In theory it should be possible to write a little program that reads in the whole ddl script and re-orders it according to the dependencies.
(must be fun to write too)

Geert

[Jan Holub]

Guys, just for your information, I've reported this issue as a bug and so far I've ben advices got following workaround by Allan Butt:

One workaround would be to generate script for all first and second
level objects, and then generate a separate script for third level
objects. These objects could then be moved to separate packages to help selection of the appropriate objects.

I hope this is of help.

[Paolo F Cantoni]

I would report this as a bug to Sparx and ask them for a workaround.
In theory it should be possible to write a little program that reads in the whole DDL script and re-orders it according to the dependencies.
(must be fun to write too)

Geert

Some dependencies can be circular... But I believe View creation dependencies can't...

Paolo

[Geert Bellekens]

I think you can definitely design circular dependencies between views in EA, but you certainly can't create a database like that.
Detecting the circular dependencies could be a nice project on its own.

Geert