Databases Reference
In-Depth Information
For this type of functionality, you have to rely on external programs or scripts. The following list mentions a
number of options, all of which can generate the scripts required to synchronize the production environment's
database objects structure with the changes you may have introduced in development:
•
SQL Developer
: Oracle's own product can run a full schema comparison between two separate
schemas on separate servers and generate a script that synchronizes one schema with
another. Older versions of this tool suffered from some problems, but as of SQL Developer
version 3.2 the comparison engine has been significantly upgraded and the generated scripts
are solid.
•
Oracle Enterprise Manager
: If you have the Change Management Pack and Oracle Enterprise
Manager (OEM), then you can compare schemas and generate a synchronization script.
However, developers are very rarely given access to OEM because it's more of a database
administration tool and would potentially give developers access to several sensitive utilities
they'd rather us not have access to.
•
Schema Compare for Oracle
: Red Gate Software has taken its extensive experience in
creating tools for the SQL Server market and turned its attention to the Oracle database
market. The result is a tool that allows you to compare, view, and generate synchronization
scripts between two Oracle schemas. This is probably the best third-party tool on the market,
but the one downside is that it only runs on Windows.
•
TOAD for Oracle
: TOAD(which originally stood for Tool for Oracle Application Development)
is a tool written and distributed by Dell's software division (formally Quest Software).
Although it can do a lot more, the schema-comparison tool that's available as part of the DB
Admin module is quite sophisticated and will generate very clean and accurate scripts.
Whichever tool you use, the output is a script that, when run against the production environment, executes
the required DDL to alter the underlying database objects and bring them in line with what was created in your
development environment.
However, none of these tools take into account the data that may reside in the tables that are being altered.
Be very careful before you implement any of the generated upgrade scripts, understand what they may do to the
underlying data, and mitigate any risks of data loss or corruption.
This subject is huge and is beyond the scope of this topic. There is no automated solution to the problem of data
migration between versions. More often than not, it boils down to handwritten scripts and heavy testing.
APEX-Based Files
APEX provides the ability for developers to upload static files into the APEX metadata repository as part of an
application's shared components. Figure
10-6
shows the Files section of the shared components page. The three types
of files that are supported are CSS, images, and static files. Let's talk about each of these file types.
