Support for Oracle Database & External Tables

General Discussion
1

Hi.

we are considering adopting Liquibase, BUT, I have been told that there are limitations in what it supports in respect to Oracle DDL syntax. One specific example is that of Oracle EXTERNAL TABLES. I would also be interested to know whether there are also any problems with database directories.

Thanks,

Clive.

2

Because this is Oracle-specific, you would need to write your changeset as SQL rather than as a Liquibase create table change. A Liquibase create table changeset might look like this:

  <changeSet author="steve" id="1470867976783-1" >

        <createTable tableName="TRACE_TAB">

              <column name="SEQNO" type="NUMBER(*, 0)">

                    <constraints nullable="false"/>

              </column>

              <column name="TEXT" type="VARCHAR2(4000 CHAR)"/>

        </createTable>

  </changeSet>

But an External table/directory would need to look like this:

  <changeSet author="steve" id="externalTableDirectory" >

        <sql>

              CREATE OR REPLACE DIRECTORY ext_tab_data AS '/data';

        </sql>

  </changeSet>

  <changeSet author="steve" id="externalTable" >

        <sql>
              CREATE TABLE countries_ext (

                    country_code

VARCHAR2(5),

                    country_name

VARCHAR2(50),

                    country_language  VARCHAR2(50)
              )
              ORGANIZATION EXTERNAL (

                    TYPE ORACLE_LOADER

                    DEFAULT DIRECTORY ext_tab_data

                    ACCESS PARAMETERS (

                          RECORDS DELIMITED BY NEWLINE

                          FIELDS TERMINATED BY ','

                          MISSING FIELD VALUES ARE NULL

                          (

                                country_code

CHAR(5),

                                country_name

CHAR(50),

                                country_language  CHAR(50)

                          )

                    )

                    LOCATION ('Countries1.txt','Countries2.txt')
              )
              PARALLEL 5
              REJECT LIMIT UNLIMITED;

        </sql>

  </changeSet>

The main limitation when using inline SQL like this is that it is difficult to support deployment to different environments, because Liquibase can’t really parse/replace things in the SQL.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

3

Steve,

thanks for the prompt response. However, due to my ignorance in respect of Liquibase, this raises more questions…

The team (in another part of the company I work for) who have been doing the Liquibase POC have been working based on plain old fashioned DDL files (not XML JSON formats etc.).

Your reply seems to imply, that there is not “out of the box” functionality for either DATABASE DIRECTORIES or EXTERNAL TABLES. Am I interpreting this correctly? In our case, would we need to mix XML (specifically for directories, external tables) as per your example with our plain DDL files?

Thanks,

Clive.

4

That is correct. Liquibase is really designed to use “formatted changelogs” (these can be in XML or JSON or YAML, also ‘formatted SQL’) rather than just plain SQL files. While you can just use straight SQL files, I think that you lose a lot of the benefits of Liquibase over other tools if you take that path.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

5

Clive,

Yes, you are correct. You would require a mix of XML and SQL.

That’s one of the reasons we developed Datical DB. It uses the Liquibase engine but includes custom extensions for Oracle to support database objects that are not in Liquibase. We also have the ability to Forecast changes without persisting them to the database, a Rules Engine to enforce standards like naming conventions or technical standards, and Deployment Packager to onboard SQL files into the XML format.

DM me and we can arrange a look at Datical DB.

Thanks!

Robert