I'm working on a Liquibase migration script and need to create temporary tables for different databases (Oracle, MySQL, SQL Server, etc.). I recently discovered that Liquibase 4.19.0+ supports the tableType attribute in the <createTable> change type, which allows for database-agnostic temporary table creation. However, when I use tableType=TEMPORARY, I encounter syntax errors. Additionally, when I use raw SQL to create temporary tables in Oracle, I run into index-related errors.

Current approach: using declarative syntax (tableType): I tried using the tableType attribute to create a temporary table declaratively:

<changeSet id="1" author="my_name">
    <comment>Create TEMPORARY table `user_temp` using declarative syntax</comment>
    <createTable tableName="User_temp" tableType="EMPORARY>
        <column name="user_id" type="VARCHAR(255)">
            <constraints nullable="false"/>
        </column>
        <column name="user_name" type="VARCHAR(255)">
            <constraints nullable="false"/>
        </column>
    </createTable>
    <addPrimaryKey tableName="user_temp" columnNames="user_id, user_name" constraintName="pk_approval_identity_temp"/>
</changeSet>

However, this results in a syntax error when executed.

Using Raw SQL: I also tried creating the temporary table using raw SQL for Oracle:

<changeSet id="2" author="my_name" dbms="oracle">
    <comment>Create GLOBAL TEMPORARY table `user_temp`</comment>
    <sql>
        CREATE GLOBAL TEMPORARY TABLE approval_identity_temp
        (
            user_id VARCHAR2(255) NOT NULL,
            user_name        VARCHAR2(255) NOT NULL
        ) ON COMMIT PRESERVE ROWS;
    </sql>
    <addPrimaryKey tableName="user_temp" columnNames="user_id, user_name" constraintName="pk_user_temp"/>
</changeSet>

This works, but I encounter index-related errors in Oracle, such as:

[Index] mismatch in schema [system] on table [USER_TEMP] with name [SYS_C007381]: Should have [Index] with name [SYS_C007381] but was missing

Questions - declarative syntax (tableType):

Why does using tableType=TEMPORARY result in a syntax error? Is this a bug, or am I missing something?

Are there any additional configurations or attributes required to make tableType=TEMPORARY work correctly?

Raw SQL for Oracle: why am I encountering index-related errors when creating a temporary table in Oracle using raw SQL?

How can I ensure that the primary key constraint and its associated index are created correctly for temporary tables in Oracle?

General best practices: what is the recommended approach for creating temporary tables in Liquibase across different databases (Oracle, MySQL, SQL Server, etc.)?

Should I stick to raw SQL, or is there a way to make the declarative syntax work reliably?

Additional Information:

• Liquibase Version: 4.27.0

Databases:

• Oracle, MySQL, SQL Server 2019, SQL Server 2022

Error Details:

Syntax error when using tableType="TEMPORARY"

Index-related errors in Oracle when using raw SQL.

I'm working on a Liquibase migration script and need to create temporary tables for different databases (Oracle, MySQL, SQL Server, etc.). I recently discovered that Liquibase 4.19.0+ supports the tableType attribute in the <createTable> change type, which allows for database-agnostic temporary table creation. However, when I use tableType=TEMPORARY, I encounter syntax errors. Additionally, when I use raw SQL to create temporary tables in Oracle, I run into index-related errors.

Current approach: using declarative syntax (tableType): I tried using the tableType attribute to create a temporary table declaratively:

<changeSet id="1" author="my_name">
    <comment>Create TEMPORARY table `user_temp` using declarative syntax</comment>
    <createTable tableName="User_temp" tableType="EMPORARY>
        <column name="user_id" type="VARCHAR(255)">
            <constraints nullable="false"/>
        </column>
        <column name="user_name" type="VARCHAR(255)">
            <constraints nullable="false"/>
        </column>
    </createTable>
    <addPrimaryKey tableName="user_temp" columnNames="user_id, user_name" constraintName="pk_approval_identity_temp"/>
</changeSet>

However, this results in a syntax error when executed.

Using Raw SQL: I also tried creating the temporary table using raw SQL for Oracle:

<changeSet id="2" author="my_name" dbms="oracle">
    <comment>Create GLOBAL TEMPORARY table `user_temp`</comment>
    <sql>
        CREATE GLOBAL TEMPORARY TABLE approval_identity_temp
        (
            user_id VARCHAR2(255) NOT NULL,
            user_name        VARCHAR2(255) NOT NULL
        ) ON COMMIT PRESERVE ROWS;
    </sql>
    <addPrimaryKey tableName="user_temp" columnNames="user_id, user_name" constraintName="pk_user_temp"/>
</changeSet>

This works, but I encounter index-related errors in Oracle, such as:

[Index] mismatch in schema [system] on table [USER_TEMP] with name [SYS_C007381]: Should have [Index] with name [SYS_C007381] but was missing

Questions - declarative syntax (tableType):

Why does using tableType=TEMPORARY result in a syntax error? Is this a bug, or am I missing something?

Are there any additional configurations or attributes required to make tableType=TEMPORARY work correctly?

Raw SQL for Oracle: why am I encountering index-related errors when creating a temporary table in Oracle using raw SQL?

How can I ensure that the primary key constraint and its associated index are created correctly for temporary tables in Oracle?

General best practices: what is the recommended approach for creating temporary tables in Liquibase across different databases (Oracle, MySQL, SQL Server, etc.)?

Should I stick to raw SQL, or is there a way to make the declarative syntax work reliably?

Additional Information:

• Liquibase Version: 4.27.0

Databases:

• Oracle, MySQL, SQL Server 2019, SQL Server 2022

Error Details:

Syntax error when using tableType="TEMPORARY"

Index-related errors in Oracle when using raw SQL.

• oracle-database
• liquibase
• temp-tables
• liquibase-sql

1 Answer 1

Strange, tableType="temporary" should work. Liquibase has integration tests for it, like here and they are executed against oracle. Also there are no xsd validations defined for this field, so anything you write there should be passed to the sql.

You could try running update-sql command and check if the generated SQL is correct . If there is something wrong then it may be a bug, but still it's a bit weird.