Writing Scripts for Database Objects

Packaging for Datical proceeds by database object type. It is a best practice to break down SQL scripts into a script per database object type.

Using the sql_direct and ddl_direct Folder

Use the ddl_direct or sql_direct folder for SQL scripts to be processed by the native database client.

The ddl_direct and sql_direct folders are identical except for the order in which they are processed in packaging: ddl, ddl_direct, view, function, procedure, package, packageBody, trigger, sql, sql_direct, data_dml.

You may need to package scripts in this folder under certain conditions:

You use multi-object scripts that you cannot break out into per-object scripts.
Your scripts include database objects that are not supported by the CONVERT packaging method (which is the default method for the DDL folder) such as database links, queues, types, cursors, public synonyms, renames, grants, and revokes. See these pages for details:
Your scripts have Complex and Interdependent Database Changes.
You need to preserve the actual statements in the script and the order of the statements in the script (which may not be preserved if using ddl folder/CONVERT packaging method, see CONVERT/ddl folder versus DIRECT/sql_direct folder ).
You get errors during packaging in other folders that you cannot resolve by other means.

Note

Changesets produced using the database native client cannot be deployed to other types of database.

Encoding and Characters in Script Files

File Encoding Best Practice

For best results, scripts should use UTF-8 encoding. Other encoding types can result in lexical issues with Datical DB that require the affected files to be converted to UTF-8.

This conversion is performed automatically when you use the Deploy Packager & the packagemethod is set to direct or ddl_direct in the metadata.properties file that affects the script's directory
This conversion IS NOT performed when you create change sets that target scripts in the Change Set Wizard of the Datical DB GUI. These options include:
- Execute with sqlplus for Oracle Databases
- Execute a DML script with sqlplus for Oracle Databases (Required for DML Forecasting)
- Execute with sqlcmd for Microsoft SQL Server & Azure SQL Databases
- Execute with clpplus for DB2 Databases
- Execute with psql for Postgres Databases
- Execute with edbplus for EnterpriseDB Databases
- Execute a SQL script file using JDBC

Using the NLS_LANG environment variable with Oracle

NLS_LANG is [Language]_[Country].<Encoding>. NLS_LANG allows the Oracle Client, which is a sqlplus CLI tool, to communicate with the database via encoding. The NLS_LANG environment variable can impact the operation of Liquibase Enterprise in two ways.

`sqlplus` Change Set Execution

This setting is used by the database to ensure that script contents are interpreted appropriately when executed and that data values are stored appropriately in the database. This can effect how characters are displayed in query output and in SQL IDEs and workbenches. If you are unable to standardize on UTF-8 as recommended above, it is recommended that all scripts processed for a given Liquibase Enterprise project use the same locale and character set designations. You may then set the NLS_LANG value that corresponds to your script in your Datical DB project as described in Configuring Project Settings

Log & Console Output

The setting also effects the characters sets used for output and logs. If the language and character set of the database does not match the language and character set specified on the client, you may see unexpected characters or question marks in your output instead. This can occur during Deploy operations or during the Backup and Restore phases of the Deploy Packager and, in some cases, can lead to operation failures. It is highly recommended that the NLS_LANG setting on the client system from which the Deploy Packager is executed match the setting on the RefDB. This can be accomplished by setting the NLS_LANG environment prior to executing Liquibase Enterprise on the client.

Impact and Best Practices

This section covers how certain settings will impact your database and our recommended best practices.

Learn how to enable encoding on Oracle here: /wiki/spaces/DDOC/pages/896569326 and Setting the character encoding to UTF-8 on Oracle
If you are using Oracle 12 and higher, the default character set is AL32UTF8 which is a superset of Oracle UTF-8. We recommend leaving this value as your default character set. The older Oracle UTF-8 does not have complete implementation of Unicode UTF-8 spec, which supports only 1, 2 and 3-byte characters, not 4-byte like UTF-8 spec requires. AL32UTF8 completely implements Unicode UTF-8 spec.
Liquibase Enterprise can set NLS_LANG in the following ways:
- System/User env
- Through the Enterprise GUI app or project property file.
  
  It is important to note that this implementation will override the parameter for Liquibase Enterprise, but will not change in the System. This is an option if other apps on the System require different value than Liquibase Enterprise.
- If it is not set by one of the ways mentioned above, then the NLS_LANG value specified in Oracle will be used.
We recommend setting NLS_LANG like so:
[WhateverLantuage]_[WhateverCountry] UTF8 (which corresponds to NLS_CHARACTERSET=AL32UTF8 which is set on database)

Additional Information

See Oracle documentation for more information:

Oracle Database Globalization Support Guide - Choosing a Locale with the NLS_LANG Environment Variable
NLS_LANG FAQ - https://www.oracle.com/database/technologies/faq-nls-lang.html

Error Handling

Do not put error-handling statements in the object definition. For example:

Oracle - do not include WHENEVER SQLERROR statements with the object definition.
SQL Server - do not include IF EXISTS statements with the object definition.

Datical DB has its own error-handling that wraps SQL scripts. Specifying error-handling within the statements is not necessary.

Troubleshoot

Prevent partial deployments by setting enableRollbackOnError=true in the metadata.properties file. Learn more here: Using the metadata.properties file

Oracle and PL/SQL

We recommend that you do not put error handling statements in your scripts, because Datical DB has its own error-handling that wraps SQL scripts. However, If you include error-handling code, use the raise_application_error() method as part of your catch routine. Datical relies on raise_application_error() to determine the status of a change (success, failure).
Datical creates and uses a DATICAL_SPERRORLOG table in the Datical tracking schema. It is re-created as needed when a sqlplus change is deployed.

Stored-Logic Scripts

Stored logic refers to the following "programmatic" SQL objects (when they are in a stored logic folder or in a folder with packageMethod=STOREDLOGIC).

function
package
packagebody
procedure
trigger
view

Conventions

Use the following conventions for writing stored-logic scripts:

Create or modify only one object per file.
- Exception: Oracle packages. A package header and package body can be included in the same file.
For stored logic scripts, only use the CREATE OR REPLACE command syntax.
Include only the object definition. Do not place other statements in the file.
- Deployment Packager fails if it encounters statements before an object definition for scripts in a stored logic folders or where packageMethod is defined in the metadata.properties as packageMethod=STOREDLOGIC.
- This includes statements such as ALTER SESSION and USE. These statements should not be necessary when using Liquibase Enterprise.
- This also includes statements such as SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF. If these statements need to be included, use the sql_direct folder.
- Do not put error-handling statements in the object definition for stored logic scripts. Datical DB has its own error-handling that wraps SQL scripts. Specifying error-handling within the statements is not necessary.

Managing Drops of Stored Logic Objects

Version 6.16 and Earlier

To add a change set to the changelog.xml that drops a stored logic object a user can delete the file in the SQL code repo that contained that object's definition. The next time the Deploy Packager runs, the files absence will be noted and the change set will be added to the changelog.xml.

Version 6.17 and Later

In 6.17 this mechanism was altered to provide more precise control over adding change sets that drop stored logic objects to the changelog.xml file. Instead of deleting the file, the user can update the file to contain a valid SQL statement to drop that object. The next time the Deployment Packager runs, the change set to drop the object will be added to the changelog.xml.

To prevent attempting to drop a nonexistent object, a special precondition is added to the change set to check and see if the object exists first.

Versioning and Stored Logic Object Drops

The change set that drops the stored logic object will be versioned with all other change sets that pertain to the management of that object. Consult the table below for an example versioning history for a stored logic object.

Description of Change SQL File Contents Version

procPrintHelloWorld is initially created

CREATE OR REPLACE PROCEDURE procPrintHelloWorld
IS
BEGIN
  
  DBMS_OUTPUT.PUT_LINE('Hello World!');

END;

1

procPrintHelloWorld is modified

CREATE OR REPLACE PROCEDURE procPrintHelloWorld
IS
BEGIN
  
  DBMS_OUTPUT.PUT_LINE('Hello there World!');

END;

2

procPrintHelloWorld is dropped

DROP PROCEDURE procPrintHelloWorld;

3

See Changeset Versioning: version and versionStrategy for more information about how change set versioning is used in Datical DB.

Statement Terminators

Use statement terminators in SQL scripts (the semicolon by convention).

When a statement is missing the terminator, packager will stop processing and return an error if the script time out setting has been set for REF dbDef. See "CLPPlus Timeout", "SQL*Plus Timeout", "EDB*Plus Timeout", and "SQLCMD Timeout" here: Configuring Step Settings (DbDefs).

Dynamic SQL

Datical DB works with "compile-time" or "static" SQL statements in SQL script files.

It does not work on the SQL code that is generated at run time through features for dynamic SQL.

It accepts and passes through statements like EXECUTE IMMEDIATE that are used to produce SQL code dynamically.

CONVERT method/ddl folder versus DIRECT method/sql_direct folder versus DDL_DIRECT method/ddl_direct folder

With versions 7.15 and later, you can use the DDL_DIRECT method/ddl_direct folder (instead of DIRECT method/sql_direct folder).

The ddl_direct package method allows you to have more control over the processing order of scripts using the DIRECT package method so that scripts with DDL statements run before scripts with data changes that rely on those DDL changes.

You can use ddl_direct as follows:

A ddl_direct fixed folder name
A ddl_direct package method set for any other folder. You can set it using the metadata.properties file in a flexible folder where you want to use it: packageMethod=ddl_direct

An example of the new processing order for fixed folders: ddl, ddl_direct, view, function, procedure, package, packageBody, trigger, sql, sql_direct, data_dml.

An example of the new processing order when using flexible folders (where packageMethod is definied in the metadata.properties): convert, ddl_direct, storedlogic, sqlfile, direct, data_dml.

If you use version 7.14 or earlier, refer to the following instructions below.

The CONVERT packaging method (which is used by default in the ddl folder) does not run the actual sql script during the final deploy. The CONVERT packaging method (ddl folder) will deploy the change set that was created by comparing the "before" snapshot to the "after" snapshot where the change set is based on the difference between the two. The CONVERT method (ddl folder) may not preserve the order of statements and it may not include statements that do not result in a change set during the diff process.

If the order of the statements in the script is critical or if the script contains statements that would not be represented in a change set generated by diffing the before and after states of the database, you could instead package those scripts with the DIRECT method (which is used by default in the sql_direct folder). Scripts packaged with the DIRECT method in the sql_direct folder (or other folders that you have set to use the DIRECT packaging method) will run the actual script, thereby preserving the statements and the order of the statements.

Note that the possible drawback with using the DIRECT method/sql_direct folder with some configurations is losing some of the simulation/forecasting/modeling/profiling abilities that may happen with the CONVERT method/ddl folder. For Oracle you could use Datical's optional SQL Parser feature to add forecasting to the DIRECT method/sql_direct folder (or other folders that you have set to use the DIRECT packaging method).

Here are pages with additional information such as an overview of the workflows packager uses, packaging methods, packaging folders, and which methods/folders to use for different types of changes:

Overview of Packaging

Placing Files in the SCM Repository

Fixed Folder Names

Flexible Folder Names

SQL Server Database Objects and Packaging

Oracle Database Objects and Packaging

PostgreSQL Server Database Objects and Packaging

Using SQL Parser

How To: Choose Between CONVERT (ddl) and DDL_DIRECT (ddl_direct) Packaging Methods

What is the difference between "ddl" and "sql_direct" folders?