Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Liquibase.dropAll() throws an error when trying to delete a table in Oracle/DB2, when the table has a quoted lower-case name

Description

I created a table in Oracle as follows (note the quotes around the table name):

create table "testrecord" ("id" number(19,0) not null, "name" varchar2(255 char) not null, primary key ("id"));

When I call Liquibase.dropAll(), I get this error:

[junit] SEVERE 2/3/12 7:53 PM:liquibase: Error executing SQL DROP TABLE testrecord [junit] java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist [junit] [junit] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) [junit] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) [junit] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) [junit] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) [junit] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) [junit] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) [junit] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194) [junit] at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000) [junit] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307) [junit] at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1882) [junit] at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1847) [junit] at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301) [junit] at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92) [junit] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55) [junit] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:105) [junit] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:70) [junit] at liquibase.database.AbstractDatabase.dropDatabaseObjects(AbstractDatabase.java:585) [junit] at liquibase.Liquibase.dropAll(Liquibase.java:485) [junit] at liquibase.Liquibase.dropAll(Liquibase.java:472)

It looks like Liquibase generated the following SQL:

DROP TABLE testrecord

Since the table name is not quoted, Oracle can't find the table and throws an exception.

Running

DROP TABLE "testrecord"

drops the table successfully.

Environment

Windows 7, Oracle 11g, DB2 9.7

Activity

Show:

Nathan Voxland June 12, 2013 at 6:21 PM

Fixed in 3.0

Nikita D March 23, 2012 at 3:46 PM
Edited

The same error happens on DB2 as well:

Caused by: liquibase.exception.DatabaseException: Error executing SQL DROP TABLE person_tbl: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.PERSON_TBL, DRIVER=4.9.78 [junit] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62) [junit] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:105) [junit] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:70) [junit] at liquibase.database.AbstractDatabase.dropDatabaseObjects(AbstractDatabase.java:585) [junit] at liquibase.Liquibase.dropAll(Liquibase.java:485) [junit] at liquibase.Liquibase.dropAll(Liquibase.java:472) [junit] Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.PERSON_TBL, DRIVER=4.9.78 [junit] at com.ibm.db2.jcc.am.fd.a(fd.java:676) [junit] at com.ibm.db2.jcc.am.fd.a(fd.java:60) [junit] at com.ibm.db2.jcc.am.fd.a(fd.java:127) [junit] at com.ibm.db2.jcc.am.en.c(en.java:2553) [junit] at com.ibm.db2.jcc.am.en.d(en.java:2541) [junit] at com.ibm.db2.jcc.am.en.b(en.java:1957) [junit] at com.ibm.db2.jcc.t4.cb.h(cb.java:221) [junit] at com.ibm.db2.jcc.t4.cb.b(cb.java:47) [junit] at com.ibm.db2.jcc.t4.q.b(q.java:38) [junit] at com.ibm.db2.jcc.t4.rb.h(rb.java:114) [junit] at com.ibm.db2.jcc.am.en.hb(en.java:1952) [junit] at com.ibm.db2.jcc.am.en.a(en.java:3040) [junit] at com.ibm.db2.jcc.am.en.e(en.java:1028) [junit] at com.ibm.db2.jcc.am.en.execute(en.java:1012) [junit] at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92) [junit] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)

(NOTE: -204 is DB's error code for "undefined name": http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.messages.sql.doc%2Fdoc%2Fmsql00204n.html)

Fixed

Details

Reporter

Fix versions

Affects versions

Priority

Created February 3, 2012 at 8:58 PM
Updated June 20, 2013 at 3:31 AM
Resolved June 12, 2013 at 6:21 PM