Databases Reference
In-Depth Information
•
All files in lower case:
All of the file names have to be in lower case; upper case or
mixed case is not allowed. The main reason for this is safety. Microsoft Windows
environments are often used by developers. Since Microsoft Windows is not case
sensitive you are allowed to call another sql script in the wrong case; for example,
if the file name is
Script1.sql
then invoking this file
@script1.sql
or
@ScRiPt1.sql
in another sqlplus script is a completely valid statement. When you want to run
these scripts on a Linux or UNIX platform, they will not work since these
environments are case sensitive.
•
Separate Files for Each Object and DML Statements:
The DDL statements for any
database object will be placed in a separate file. This holds true for the DML
statements as well. In Table 9-4 the different file types are detailed and placed in
the correct order in which they are run in the
patch
<db schema>
.sql
script.
•
Consolidation:
We will deviate from the above principle only for very few
exceptions; for example, all grants and all synonyms will each be placed into a
single file.
Table 9-4.
File Types and Names
Category
Convention
Description
<sequence name>
.sql
For example, the file
fm id seq.sql
will contain
the statement
CREATE SEQUENCE FM ID SEQ;
Sequences
Synonyms (consuming)
synonyms consuming.sql
These are the synonyms that our application
uses to address objects in a different database
schema.
<type name>
.sql
Types
Here we only create the type specification; the
body implementation follows later.
Table DDL statements
<table name>
.sql
Examples:
fm bookings.sql
fm countries.sql
This file contains all relevant DDL statements for
the table except referential constraints, grants,
and triggers:
• create table
•
create index
•
create constraint (primary key, unique key,
check constraint)
<table name>
ref.sql
Examples:
fm bookings ref.sql
fm countries ref.sql
Table referential
constraints
Once all tables have been installed, we can safely
install the foreign key constraints between the
relevant tables.
