<sql_code_dir>

User-defined directory in SCM to use as the root for SQL scripts. Subdirectories are provided for different types of SQL. Example: sql_code. 

archive

The archive directory is created and managed by the Deployment Packager. Files added in the ddl, sql, sqlplus, sql_direct, and data_dml directories will be copied here after successful Packaging to prevent post Packaging modification. The user does not need to create this directory.

data_dml

Contains scripts that load, manipulate or delete data.

ddl

Contains scripts that make ddl changes such as creating, modifying, and dropping tables, indexes, sequences, and other database objects that are not one of the other types.

function

Contains scripts that create or replace one function.

package

Contains scripts that create or replace one package definition.

packagebody

Contains scripts that create new or replace existing package bodies.

sql

Limited Use.

Creates a sqlFile changeset. When deployed, an internal SQL processor to run the script. These changesets can be deployed to any database platform. This is useful if you have a multi-platform project (e.g., Oracle and SQL Server).

It's strongly suggested to use sql_direct if you use the same database platform in all steps in a project. 

sqlplus

Deprecated. 

Use sql_direct instead.

sql_direct

The scripts are run directly using the database's native client. The changesets are database-specific. They cannot be deployed to other database platforms. 

• DB2 - Command Line Processor Plus (clpplus)

• Oracle - SQL*Plus (sqlplus)
• EDB Postgres - EDB*Plus (edbplus)
• SQL Server - SQLCMD (sqlcmd)

trigger

Contains SQL scripts that create or replace one trigger.

DDL - Data Definition Language

CREATE, ALTER, DROP, COMMENT

DML - Data Manipulation Language

INSERT, UPDATE, DELETE, TRUNCATE, MERGE, SELECT

DCL - Data Control Language

GRANT, REVOKE

Stored Logic

CREATE (OR REPLACE) / DROP PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TRIGGER 

• Archived: After the changes are processed, the SQL script is moved to the archive directory.
• SQL scripts with the same name in the same directory cannot be re-packaged

ddl

sql_direct

data_dml

• Not archived: The SQL script remains in the directory and is versioned. 
• SQL scripts can be repackaged

• Converts SQL code to produce changesets into an object model (based on the differences when comparing before and after states of REF)
• Full Forecasting and Rules 
• Typically slower than other folders/methods
• The actual script is NOT run during the final deploy (therefore certain statements or the ordering of statements in the original script may not be applied)

ddl folder (CONVERT packaging method)

• Runs SQL to produce the changes and embeds native SQL calls in the changesets
• Limited Forecasting and Rules if not using SQL Parser (Oracle customers can optionally enable SQL Parser for Oracle for forecasting)
• Faster than ddl folder (when ddl folder is using its default CONVERT packaging method)
• The actual sql script IS run
• The native script timeout setting that we recommend setting for your REF environments IS applicable because it uses the native SQL client tools (SQLPlus, SQLCmd, ClpPlus, EDBPlus)
• Cannot use passwords that contain spaces or these special characters @ & / : < > " ' ` | ^ ! = , \

data_dml and sql_direct folders (DATA_DML and DIRECT packaging methods)

• Uses JDBC to create, modify, or delete stored logic objects
• Limited Forecasting and Rules (Oracle, SQL Server, and DB2 customers can optionally enable the Stored Logic Validity Check project setting)
• Faster than ddl folder (when ddl folder is using its default CONVERT packaging method)
• The actual sql script IS run
• Only include one statement per script
• Files should be UTF-8 in stored logic folders
• The native script timeout setting that we recommend setting for your REF environments is NOT applicable because JDBC connection is used

• Uses JDBC 
• If you have multiple statements, they must be semicolon-delimited
• You cannot have semicolons within the statement itself
• The native script timeout setting that we recommend setting for your REF environments is NOT applicable because JDBC connection is used

Check In New File

Processed as a new file. The changes are packaged as a changeset to be added to the changelog.xml file.

Rename File

Packaging processes the script with the new filename as a new changeset. 

Moving a file more than once may cause errors.

Delete File

Deleting a file has no effect on the changelog.xml file.