This feature is available in version 5.0 and later.

Using Flexible Folders

This feature can help you if you cannot use fixed-name folders in SCM under a <sql_code> root.

Using this capability, you may use any number of arbitrarily named folders under a <sql_code> root. This enables you to use folder names that are meaningful to your environment.

When you use flexible folders, the folders containing SQL code must have a metadata.properties file, even if it is blank.

You need to specify two properties (new to version 5.0) in a metadata.properties file in the folder. They explicitly specify packaging behavior that was implicit for code in fixed-name folders.

packageMethod - specifies how to package code in this folder
rerunnable - specify whether this code can be repackaged (updated in place)

SQL File Processing

SQL files are packaged in a determined order by type. The packaging method is determined from the type.

Ordering

Packaging is done in the following order, using the SQLFileType returned from parsing. It is the same order used with static-name directories. The following table shows the packageMethod used for each SQLFileType.

Order	SQLFileType	packageMethod
1	ddl	CONVERT
2	view	STOREDLOGIC
3	ssis	SSIS
4	ssis_project	SSIS
5	function	STOREDLOGIC
6	procedure	STOREDLOGIC
7	package	STOREDLOGIC
8	packagebody	STOREDLOGIC
9	trigger	STOREDLOGIC
10	sql	SQLFILE
11	sqlplus	DIRECT
12	sql_direct	DIRECT
13	data_dml	DATA_DML

Complex and Interdependent Database Changes

Normally DDL changes are placed in folders that use the CONVERT packaging method. However, when you need to package complex and interdependent changes, place them in a folder that uses the DIRECT packaging method instead.

If the order that the multiple statements are listed in the single script is critical to the success of the script, then use the DIRECT packaging method. The DIRECT packaging method will preserve the order of the statements in the script. (The CONVERT packaging method may not preserve the order because it creates the change sets by doing a diff of before and after snapshots, and that comparison process does not know the original order of the statements.)

Examples of sets of statements in a single sql script that need to use the DIRECT packaging method:

create table
move data from old table to new table
drop old table

alter table drop pk constraint
alter table drop column (a column that the pk constraint depends on)
alter table add column (a column that the pk constraint depends on)
alter table add pk constraint

Resources Directory

During SQL file packaging, files are copied to directories under the project Resources directory. The directories have the same name except for files in sql, sqplus, and sql_direct, which are all placed in Resources/sql.

SQLFileType	Resources folder
DDL	N/A
VIEW	view
SSIS	ssis
SSIS_PROJECT	ssis_project
FUNCTION	function
PROCEDURE	procedure
PACKAGE	package
PACKAGEBODY	packagebody
TRIGGER	trigger
SQL	sql
SQLPLUS	sql
SQL_DIRECT	sql
DATA_DML	data_dml

Properties in metadata.properties

Requirement for metadata.properties

When you are using flexible folders, folders containing SQL code must have a metadata.properties file, even if it is blank.

Folders containing metadata.properties file are processed.
Folders not containing metadata.properties file are skipped.

More about the different properties that can be defined in metadata.properties files: Using the metadata.properties file

ignore

Set to true or false. If true, packager skips this folder and all subfolders during processing.

packageMethod

If specified, determines the processing method to use during packaging. The file is further parsed to determine the SQLFileType, which determines the order of processing.

If not specified, determine the type based on directory location (static-name directories) and parsed SQLFileType.

CONVERT (convert)
STOREDLOGIC (native)
DIRECT (native)
DATA_DML (native)
SQLFILE (native)
SSIS (native)

rerunnable

Important: The newer rerunnable property replaces two properties in metadata.properties. These are deprecated and should not be used:

allowRepackaging
archive

Set rerunnable to true or false.

true - the SQL code file is not archived. It can be repackaged.
false - the SQL code file is archived. It cannot be repackaged.

If not set, packager checks whether fixed-name folders are being used under the <sql_code> root and assigns the rerunnable property as follows. In general, stored logic is rerunnable.