streamlining creating of packages and views

Question

I have few SQL scripts which setups the db of an app. have few scripts creating packages which has few references of views, and similarly have scripts for creating views which has references of packages.

Is there a way to separate these two scripts which would then individually create only packages or views respectively.

Or any alternate to work on this.

Answer 1

I think you have to calculate the reference graph manually and then order the execution of the scripts accordingly.

So you need to create a set of scripts views1.sql, views2.sql, ... and packages1.sql, packages2.sql, ... Views1.sql contains only views that are not referencing any packages. Packages1.sql contains only packages that are not referencing any views. Views2.sql contains only views that are referencing packages from packages1.sql. Packages2.sql contains only packages that are referencing views from views1.sql.

And so on until you are finished.

Answer 2

You could just create all your views first using the syntax

CREATE OR REPLACE FORCE VIEW

which creates a view even if the referenced objects don't exist yet, then create all your package specs, then the bodies.

Now you could compile all invalid objects or just let Oracle take care of it (see this link)

Ask Tom - "invalid objects"

Answer 3

First, create the package specifications.

Second, create the views -- they reference the specification, not the body.

Third, create the package bodies -- they reference the views.