IfNotExists attribute doesn't work for Oracle

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?

    -   changeSet:
            author: hp_dev
            comment: create HPT_AR_INSTITUTION_SETTING # 24.1.0
                - init
                -   createTable:
                        ifNotExists: true # <------ This doesn't work
                        tableName: HPT_AR_INSTITUTION_SETTING
                            -   column:
                                    name: INSTITUTION_ID
                                    type: varchar(5)
                                        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

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.

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

@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.


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>
	<sqlFile dbms="oracle"

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.

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.

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.