# Support for Oracle Database & External Tables

**URL:** <https://forum.liquibase.org/t/support-for-oracle-database-external-tables/3413>\
**Category:** General Discussion\
**Created:** [September 23, 2016, 3:11pm UTC](https://forum.liquibase.org/t/support-for-oracle-database-external-tables/3413 "2016-09-23T15:11:00Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![CliveBostock](https://avatars.discourse-cdn.com/v4/letter/c/a6a055/32.png) [@CliveBostock](https://forum.liquibase.org/u/CliveBostock)\
**Post date:** [September 23, 2016, 3:11pm UTC](https://forum.liquibase.org/t/support-for-oracle-database-external-tables/3413/1 "2016-09-23T15:11:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![un1382561729492r88id](https://avatars.discourse-cdn.com/v4/letter/u/c4cdca/32.png) [@un1382561729492r88id](https://forum.liquibase.org/u/un1382561729492r88id)\
**Post date:** [September 23, 2016, 3:11pm UTC](https://forum.liquibase.org/t/support-for-oracle-database-external-tables/3413/2 "2016-09-23T15:11:00Z")

</div>

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/](http://www.datical.com/)

---

<div class="post-metadata">

**Author:** ![un1474617491557r43id](https://avatars.discourse-cdn.com/v4/letter/u/bbce88/32.png) [@un1474617491557r43id](https://forum.liquibase.org/u/un1474617491557r43id)\
**Post date:** [September 23, 2016, 3:11pm UTC](https://forum.liquibase.org/t/support-for-oracle-database-external-tables/3413/3 "2016-09-23T15:11:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![un1382561729492r88id](https://avatars.discourse-cdn.com/v4/letter/u/c4cdca/32.png) [@un1382561729492r88id](https://forum.liquibase.org/u/un1382561729492r88id)\
**Post date:** [September 23, 2016, 3:11pm UTC](https://forum.liquibase.org/t/support-for-oracle-database-external-tables/3413/4 "2016-09-23T15:11:00Z")

</div>

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/](http://www.datical.com/)

---

<div class="post-metadata">

**Author:** ![un1398357527727r51id](https://avatars.discourse-cdn.com/v4/letter/u/da6949/32.png) [@un1398357527727r51id](https://forum.liquibase.org/u/un1398357527727r51id)\
**Post date:** [September 23, 2016, 3:11pm UTC](https://forum.liquibase.org/t/support-for-oracle-database-external-tables/3413/5 "2016-09-23T15:11:00Z")

</div>

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