Version configuration from SQL

Question

How would you create an installation setup that runs against multiple schemas taking into consideration the latest version of the database updates? Ideally: update a single file with a new version number, then send the DBAs an archive containing everything needed to perform the database update.

Here is the directory structure:

|   install.sql
|   install.bat
|
\---DATABASE_1.3.4.0
    |   README.txt
    |
    \---SCHEMA_01
    |       install.sql
    |       SCM1_VIEW_NAME_01_VW.vw
    |       SCM1_VIEW_NAME_02_VW.vw
    |       SCM1_PACKAGE_01_PKG.pkb
    |       SCM1_PACKAGE_01_PKG.pks
    |
    \---SCHEMA_02
            install.sql
            SCM2_VIEW_NAME_01_VW.vw
            SCM2_VIEW_NAME_02_VW.vw
            SCM2_PACKAGE_01_PKG.pkb
            SCM2_PACKAGE_01_PKG.pks

The following code (sanitized and trimmed for brevity and security) is in install.sql:

ACCEPT tns
ACCEPT schemaUsername
ACCEPT schemaPassword

CONNECT &&schemaUsername/&&schemaPassword@&&tns

@@install.sql
/

The following code is in install.bat:

@echo off
sqlplus /nolog @install.sql
pause

There are several schemas, not all of which need updates each time. Those that do not need updates will not have directories created.

What I would like to do is create two files:

• version.txt
• schemas.txt

These two (hand-crafted) files would be used by install.sql to determine which version of scripts to run.

For example:

version.txt

1.3.4.0

schemas.txt

SCHEMA_01
SCHEMA_02

What I really would like to know is how would you read those text files from install.sql to run the corresponding install scripts? (Without PL/SQL; other Oracle-specific conventions are acceptable.)

All ideas welcome; many thanks in advance.

Answer 1

Here is a solution.

install.bat

@echo off
REM *************************************************************************
REM
REM This script performs a database upgrade for the application suite.
REM
REM *************************************************************************

setLocal EnableDelayedExpansion

REM *************************************************************************
REM
REM Read the version from the file.
REM
REM *************************************************************************
set /p VERSION=<version.txt

set DB=DB_%VERSION%
set SCHEMAS=%DB%\schema-order.txt

REM *************************************************************************
REM
REM Each line in the schema-order.txt file contains the name of a schema.
REM Blank lines are ignored.
REM
REM *************************************************************************
for /f "tokens=* delims= " %%a in (%SCHEMAS%) do (
  if not "%%a" == "" sqlplus /nolog @install.sql %VERSION% %%a
)

Primary install.sql

ACCEPT schemaUsername    CHAR DEFAULT &2   PROMPT 'Schema Owner [&2]: '
ACCEPT schemaPassword    CHAR PROMPT 'Password: '  HIDE

PROMPT Verifying Database Connection
CONNECT &&schemaUsername/&&schemaPassword@&&tns

DEFINE INSTALL_PATH = DB_&1&&ds^&2&&ds

@@&&INSTALL_PATH^install.sql

This uses a batch file to parse the files, then passes the parameters to the SQL script on the command-line.

Secondary install.sql

Each line in the file executed by the first installation script can then use the INSTALL_PATH variable to reference a file containing actual SQL to run. This secondary script is responsible for running the individual SQL files that actually exact a change in the database.

@@&&INSTALL_PATH^DIR&&ds^SCM1_VIEW_OBJECT_VW.vw

This solution could be modified to automatically run all files in a specific order through clever use of sorting and naming of directories (i.e., the SQL files listed in a table directory run before the SQL files in a view directory).