EXPERT RESPONSE
From the description, it sounds like this is intended to be a packaged
application of some sort. First, for deploying the schema, I would prepare
SQL scripts and ship them with the product. Before doing so, make sure that
your product's documentation clearly states what versions of Oracle that your
product has been certified (or at least tested) with. If there is some
significant amount of seed data that is to be included with new deployments,
then I would create SQLLDR scripts and load that data from flat text files.
Alternatively, you could also create insert statements in a script if there's
not too much data.
For getting data back from a site, I would use SQLLDR and use a select
statement to generate the raw data file for loading on the remote site. That
will bypass any issues you have with length of SQL statements. You can find
many scripts on the internet for doing just that task--search for data dumper
or SQL unloader.
You should track the version of the database schema in the database somewhere
and an upgrade script should check the current version and apply the necessary
changes to upgrade that version to the current or target release. Internally,
such a script might have smaller functions or blocks that do the individual
upgrades from one version to the next so that an outer section would just need
to figure out where to start and run all of the functions in sequence.
|
