Custom SQL

The “sql” tag allows you to specify whatever sql you want. It is useful for complex changes that aren't supported through LiquiBase's automated refactoring tags and to work around bugs and limitations of LiquiBase. The SQL contained in the sql tag can be multi-line.

The createProcedure refactoring is the best way to create stored procedures.

The “sql” tag can also support multiline statements in the same file. Statements can either be split using a ; at the end of the last line of the SQL or a go on its own on the line between the statements can be used.Multiline SQL statements are also supported and only a ; or go statement will finish a statement, a new line is not enough. Files containing a single statement do not need to use a ; or go.

The sql change can also contain comments of either of the following formats:

1. A multiline comment that starts with /* and ends with */.
2. A single line comment starting with <space>–<space> and finishing at the end of the line

Note: By default it will attempt to split statements on a ”;” or “go” at the end of lines. Because of this, if you have a comment or some other non-statement ending ”;” or “go”, don't have it at the end of a line or you will get invalid SQL.

<sql>insert into person (id, name) values (1, 'Bob')</sql>

Automatic Rollback Support: NO

The example below shows how you can use the SQL tag to run a PL/SQL block in Oracle. This allows you to perform some dynamic logic in your changelog.

Imagine you want to perform a generic action on all newly created objects. Examples are:

• Ensure we have synonyms on objects of another schema before running a changelog
• Ensure that we create appropriate grants on newly created tables

For this example, let's assume that we want a view to be created for each table.

We will implement that by running a changeset at the end of each changelog with the runalways attribute. We select all tables which do not have a corresponding view, and then create that view.

  <changeSet author='jsmith' id='1' runAlways='true'>
    <sql splitStatements="false">
      DECLARE
        cursor c_newviews is
          select table_name
          from user_tables
          where table_name like 'DATABASECHANGELOG%'
          AND table_name||'_VW' not in
            (select view_name from user_views);
      BEGIN
        FOR r_newviews in c_newviews LOOP
          EXECUTE IMMEDIATE
            'CREATE VIEW ' || r_newviews.table_name || '_VW ' ||
            'AS SELECT * FROM ' || r_newviews.table_name;
        END LOOP;
      END;
    </sql>
  </changeSet>