In SDDM 3.2 will there be way to tell the DDL generator which objects are dependent on which objects? Oracle Designer DDL generator made a pretty guess at the order of generation for database objects. SDDM doesn't do a very good job. Sequences can go first, followed by object types, then tables, package headers, procedures and functions, then views, then the bodies of the object types and packages. Then constraints, indexes, triggers, synonyms and grants.
If the SDDM team would include the dependency tree of objects within each namespace that would be world class.
In my previous consulting engagements I have had package/type/table dependencies graphs that are far more intricate than:
create or replace function a(b in number) return number deterministic
create table c
(d number, e as (a(1)));
which fails to be gen'd in the correct order by SDDM.
It is a question which comes first the table or the function (chick or egg). Either answer is correct. In your example, the function first. In this one, the table first:
create table foo (a number, b varchar2 (100));
create or replace function get_foo (p_b in foo.b%type) return foo.a%type as
select foo.a into lv_a from foo where foo.b = p_b;
exceptions <they all go here>
I can see the SDDM design team's problem of trying to generate a set of DDL files that keeps both sides of the question/problem happy. The way Oracle Designer generated DDL seemed to be a good solution. All related DDL objects were in the same file, I think it was alphabetic by object name. There was a wrapper .SQL file which called each of the DDL type files. Just getting to this again with SDDM would be an improvement over the current DDL generation model.
Form what I am seeing in SDDM 3.3 EA2 and now SDDM 3.3 production, the dependency generation for object types and relational tables is much much better. I like the object types are pre-defined, then the type fully defined. The tables are next and then the object type bodies. Much better. This coupled with the ability to add the keyword FORCE and the DEFAULT NULL for parameters on method definitions goes along ways in being to rapidly cycle through the prototype process.