Exporting the Database Objects
Exporting the Database Objects
Deploying a Database Application 7-3
an outside source (such as an older application or from a flat file or spreadsheet),
you should disable the constraints before loading the data.
If any data fails to meet the constraints, that constraint will not be enabled and you
will need to correct the data and try again.
The following is a general guideline for the order in which to run the installation
scripts for different types of database objects:
1.
Package specifications
2.
Tables (with constraints and indexes) in proper order
3.
Sequences (because they are most often used by triggers)
4.
Triggers
5.
Synonyms
6.
Views (because they may reference functions, procedures, or synonyms)
7.
Package bodies
8.
Data (optionally disabling all constraints before loading the data and re-enabling
them afterwards)
Package specifications are listed first because they will always be valid and other
objects might refer to them. Package bodies should be the last object type created
because they will probably refer to other object types. Because of dependency issues,
you are encouraged to put functions and procedures into packages.
If you followed the instructions in other sections of this guide, you created objects in
the sample Oracle HR schema. This section assumes that you are deploying them to
another standard HR schema.
In the tables you have created,
scores
has foreign keys to both
performance_
parts
and
evaluations
. This means that you cannot create those foreign keys until
the primary keys for the referenced tables are created. You will first create the
evaluations
table and constraints, then the
performance_parts
table, and then
the
scores
table. You will have just one script for tables, sequences, and triggers. This
will minimize the manual editing for this exercise. You will also create just create one
script for the function and the package because you only have one of each. The last
script you will create will be for the synonym and view.
This is obviously a simplistic deployment example. For real-world applications, you
will need to consult with the database designer and map out the order for creating the
objects. If you have a diagram of the design, such as an Entity Relationship Diagram, it
can be very useful during this phase
Exporting the Database Objects
This section demonstrates how to export the database objects.
Using SQL Developer to Export Database Objects
If you have maintained scripts to create your database objects, you can use those. If
you have not maintained scripts, you should generate the data definition language
(DDL) statements for each object based on its definition in the database. To generate
the DDL for your database objects, you can use Oracle SQL Developer, specifically the
Export DDL (and Data) feature. This feature generates DDL statements to create
specified objects and types of objects, and it can generate
INSERT
statements to insert
exported data into the new tables that are created.
