IfNotExists attribute doesn't work for Oracle

General Discussion
1

I’ve created a bunch of yaml createTable change sets that use the IfNotExists attribute. Some of these tables already exist in some of our environments so I figured this was the best way to mark the change sets as run if the tables already existed. I have an Oracle database and I know Oracle doesn’t support IF NOT EXISTS when creating a table, but the docs said that this attribute was supported by Oracle so I assumed that there was something done behind the scenes for this case. I tried running my scripts and I’m getting the ORA-00955 error. Am I missing something or do the docs just need to be fixed? And is there something else I can do to get the same expected results?

databaseChangeLog:
    -   changeSet:
            id: HPT_AR_INSTITUTION_SETTING
            author: hp_dev
            comment: create HPT_AR_INSTITUTION_SETTING # 24.1.0
            labels:
                - init
            changes:
                -   createTable:
                        ifNotExists: true # <------ This doesn't work
                        tableName: HPT_AR_INSTITUTION_SETTING
                        columns:
                            -   column:
                                    name: INSTITUTION_ID
                                    type: varchar(5)
                                    constraints:
                                        primaryKey: true

liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset changelogs/init.yaml::HPT_AR_INSTITUTION_SETTING::hp_dev:\n     Reason: liquibase.exception.DatabaseException: ORA-00955: name is already used by an existing object
2

After some research, I’ve found that the error is just in the documentation. The PR mentions that Oracle is not supported for this feature so I guess the docs need to be fixed to show that. I hope that they eventually have a way to support it, but in the meantime I am using changelog-sync-to-tag for my needs.

3

Oracle 23c does support the IF NOT EXISTS syntax. The docs should probably be changed to point that out.

4

@sballance would you mind creating an issue regarding Liquibase making the ifNotExists attribute feature available in Oracle 23c? I have added a docs feedback for removing Oracle from the supports list until we make this change.

Thanks,
Wesley

5

you can use the “precondtions” in the databasechangelog file. I’ve only done it with the xml format file, using a sql script to create the tables, not inline like you have, but the premise should be the same.

<changeSet author="me" id="TEST1" runOnChange="true">
	<preConditions onFail="MARK_RAN">
		<sqlCheck expectedResult="0">select count(*) from user_tables where table_name='TEST1'</sqlCheck>
	</preConditions>
	<sqlFile dbms="oracle"
			 path="./indexes/create_test1.sql"

The expectedResult of the sqlCheck is 0, so if the table exists, the check fails. The onFail is set to MARK_RAN, which means don’t run the script (so no db errors) and update/insert the databasechangelog table with the new MD5 hash, so it looks like it ran and won’t try again.

6

This was really good for getting me on the right track. I actually ended up using the tableExists precondition. It only requires the table name and an optional schema name.

7

Right, I tried that first (and forgot about it) but it threw an error because my schema didn’t have enough privs. It said I needed select on dba_recyclebin, which my dbas wouldn’t give me. That’s when I figured out the method of selecting from user_Tables.

Liquibase INFO The user you used to connect to the database (****) needs to have “SELECT ON SYS.DBA_RECYCLEBIN” permissions set before we can perform this operation. Please run the following SQL to set the appropriate permissions, and try running the command again.