Placement and Precedence

The metadata-variables.properties file applies to all files and subdirectories in the directory where it is found, including the root directory for SQL scripts.   Additional metadata-variables.properties files can be used in the subdirectories. A property set in a subdirectory overrides the same property set in a parent directory. 

Place variables in the metadata-variables.properties file, one per line. The notation for a variable depends on whether the script you are packaging is a DDL script or a native script, as described in Using Variables. 

Variable Processing

Variables are interpreted as positional variables. The first value in the metadata-variables.properties file replaces the first occurrence of a variable in the script, and so on. 

Example

Variables and changelog properties may be used together to express variable values per context (database target). For example, you may want to express a different TABLESPACE value depending on the context (database) where you are deploying changes. 

Changelog properties are interpreted before processing for variables. See Setting Properties in the Project Changelog. 

The following example is based on scripts intended for Oracle SQL*Plus. Variables use the form &var.

• Properties in changelog
  

  <property context="REF" name="CLPROP_TBL" value="TBLVAR">
  <property context="REF" name="CLPROP_TBLSPC" value="TBLSPC">

  
  

• metadata-variables.properties variables (standard SQL script in DDL folder)
  

  &1=${CLPROP_TBL}
  &2=${CLPROP_TBLSPC}

  
  

• Script
  

  CREATE TABLE MYTABLE_&1
  (c1 varchar2(20))
  TABLESPACE "&2"; 

  
  

• Processed Script with changelog properties and variables interpreted
  

  CREATE TABLE MYTABLE_TBLVAR
  (c1 varchar2(20))
  TABLESPACE "TBLSPC"; 

  
  

Using Variables

SQL scripts may use variables to represent values that vary per environment. For example, tablespace names may differ between DEV, TEST & PROD environments. To use a single script to update all of these environments, the tablespace is represented by a variable in the script. When the script is executed the user may either pass the values for the variables on the command line or be prompted for their values as scripts execute.

When used on the command line, variable values may be passed in as positional parameters. Positional means that values are substituted in the order they are passed in.

Variables in DDL Scripts

Variable representation in scripts is done as SQL-style variables (SET @varnam = "value"). DDL scripts are one of the following: 

• Scripts in the ddl fixed folder.
• Scripts in a flexible folder with packageMethod=CONVERT. 

@1="myvalue"
@2=true
@3=${MYPROPERTY}      # value is substituted with the value of changelog property MYPROPERTY

Variables in Native SQL Scripts

Variable representation in scripts depends on the client-command syntax for the database. Native scripts are one of the following: 

• Scripts in the sql_direct or ddl_direct fixed folders.
• Scripts in a flexible folder with packageMethod=DIRECT or packageMethod=DDL_DIRECT. 

Oracle

SQL*PLUS is the native command facility for Oracle databases. Variables are represented as follows:

&1="myvalue"
&2=true
&3=${MYPROPERTY}

PostgreSQL

PSQL is the native command facility for PostgreSQL databases. Variables are represented as follows:

replaceMap=:myvariable1="myvalue"
replaceMap=:myvar2=true
replaceMap=:myvar3=${MYPROPERTY}

SQL Server

SQLCMD is the native command facility for SQL Server databases. Variables are represented as follows:

$(myvariable1)="myvalue"
${myvar2)=true
$(myvar3)=${MYPROPERTY}