Fixed Oracle update error - unquoted statement

[mukhanov]

ORA-00904: "PASSWORD": invalid identifier Caused by:
java.sql.SQLSyntaxErrorException: ORA-00904: "PASSWORD": invalid
identifier at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452) at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400) at
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:884) at
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:471) at
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:199) at
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:535) at
oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:197) at
oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:116
5) at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.
java:1444) at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.
java:1662) at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:195
8) at
oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWr
apper.java:1695) at
liquibase.snapshot.jvm.ColumnSnapshotGenerator.readColumn(ColumnSnapshot
Generator.java:137) at
liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnap
shotGenerator.java:53) ... 21 more

[nvoxland]

I added PASSWORD as a reserved word without the escapeObjectName() method since I didn't see the reason for overriding that.

[mukhanov]

The reason why I've override that method is here:

Parent method:

    public String escapeObjectName(String objectName, Class<? extends DatabaseObject> objectType) {
        if (objectName == null || quotingStrategy == ObjectQuotingStrategy.LEGACY) {
            return objectName;
        } else if (objectName.contains("-") || startsWithNumeric(objectName) || isReservedWord(objectName)) {
            return quotingStartCharacter + objectName + quotingEndCharacter;
        } else if (quotingStrategy == ObjectQuotingStrategy.QUOTE_ALL_OBJECTS) {
            return quotingStartCharacter + objectName + quotingEndCharacter;
        }
        return objectName;
    }

Oracle method:

 public String escapeObjectName(String objectName, Class<? extends DatabaseObject> objectType) {
     if (objectName != null) {
         if (objectName.contains("-") || startsWithNumeric(objectName) || isReservedWord(objectName)) {
             return quotingStartCharacter + objectName + quotingEndCharacter;
         } else if (quotingStrategy == ObjectQuotingStrategy.QUOTE_ALL_OBJECTS) {
             return quotingStartCharacter + objectName + quotingEndCharacter;
         }
     }
     return objectName;
 }

The quotingStrategy == ObjectQuotingStrategy.LEGACY in parent, so it's doesn't event check the reserved words.
With only fixed the reserved word I still can reproduce the error -

liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00904: "PASSWORD": invalid identifier

at liquibase.database.AbstractJdbcDatabase.checkDatabaseChangeLogTable(AbstractJdbcDatabase.java:621)
at liquibase.Liquibase.checkLiquibaseTables(Liquibase.java:688)
at liquibase.Liquibase.update(Liquibase.java:177)
at liquibase.Liquibase.update(Liquibase.java:160)
at liquibase.integration.commandline.Main.doMigration(Main.java:848)
at liquibase.integration.commandline.Main.main(Main.java:138)

Caused by: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00904: "PASSWORD": invalid identifier

at liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnapshotGenerator.java:58)
at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:59)
at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:42)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:75)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:144)
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:107)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:94)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:144)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:157)
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:107)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:94)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:124)
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:107)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:94)
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:128)
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:139)
at liquibase.snapshot.SnapshotGeneratorFactory.getDatabaseChangeLogTable(SnapshotGeneratorFactory.java:146)
at liquibase.database.AbstractJdbcDatabase.checkDatabaseChangeLogTable(AbstractJdbcDatabase.java:619)
... 5 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "PASSWORD": invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:884)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:471)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:199)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:535)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:197)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:1165)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1444)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1662)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1958)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:1695)
at liquibase.snapshot.jvm.ColumnSnapshotGenerator.readColumn(ColumnSnapshotGenerator.java:137)
at liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnapshotGenerator.java:53)
... 22 more

[nvoxland]

I see that now, thanks. I made the change in AbstractJDbcDatabase because
it should apply to all database types

Nathan

On Fri, Aug 16, 2013 at 3:35 AM, Nikolay Mukhanov
notifications@github.comwrote:

The reason why I've override that method is here:
Parent method:

public String escapeObjectName(String objectName, Class<? extends DatabaseObject> objectType) {
    if (objectName == null || quotingStrategy == ObjectQuotingStrategy.LEGACY) {
        return objectName;
    } else if (objectName.contains("-") || startsWithNumeric(objectName) || isReservedWord(objectName)) {
        return quotingStartCharacter + objectName + quotingEndCharacter;
    } else if (quotingStrategy == ObjectQuotingStrategy.QUOTE_ALL_OBJECTS) {
        return quotingStartCharacter + objectName + quotingEndCharacter;
    }
    return objectName;
}

Oracle method:

public String escapeObjectName(String objectName, Class<? extends DatabaseObject> objectType) {
if (objectName != null) {
if (objectName.contains("-") || startsWithNumeric(objectName) || isReservedWord(objectName)) {
return quotingStartCharacter + objectName + quotingEndCharacter;
} else if (quotingStrategy == ObjectQuotingStrategy.QUOTE_ALL_OBJECTS) {
return quotingStartCharacter + objectName + quotingEndCharacter;
}
}
return objectName;
}

The quotingStrategy == ObjectQuotingStrategy.LEGACY in parent, so it's
doesn't event check the reserved words.
With only fixed the reserved word I still can reproduce the error -

liquibase.exception.UnexpectedLiquibaseException:
liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException:
ORA-00904: "PASSWORD": invalid identifier

at liquibase.database.AbstractJdbcDatabase.checkDatabaseChangeLogTable(AbstractJdbcDatabase.java:621)
at liquibase.Liquibase.checkLiquibaseTables(Liquibase.java:688)
at liquibase.Liquibase.update(Liquibase.java:177)
at liquibase.Liquibase.update(Liquibase.java:160)
at liquibase.integration.commandline.Main.doMigration(Main.java:848)
at liquibase.integration.commandline.Main.main(Main.java:138)

Caused by: liquibase.exception.DatabaseException:
java.sql.SQLSyntaxErrorException: ORA-00904: "PASSWORD": invalid identifier

at liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnapshotGenerator.java:58)
at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:59)
at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:42)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:75)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:144)
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:107)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:94)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:144)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:157)
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:107)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:94)
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:124)
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:107)
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:94)
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:128)
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:139)
at liquibase.snapshot.SnapshotGeneratorFactory.getDatabaseChangeLogTable(SnapshotGeneratorFactory.java:146)
at liquibase.database.AbstractJdbcDatabase.checkDatabaseChangeLogTable(AbstractJdbcDatabase.java:619)
... 5 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "PASSWORD":
invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:884)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:471)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:199)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:535)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:197)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:1165)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1444)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1662)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1958)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:1695)
at liquibase.snapshot.jvm.ColumnSnapshotGenerator.readColumn(ColumnSnapshotGenerator.java:137)
at liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnapshotGenerator.java:53)
... 22 more

—
Reply to this email directly or view it on GitHubhttps://github.com//pull/154#issuecomment-22754240
.

[blirp]

PASSWORD is not a reserved word for Oracle: http://docs.oracle.com/database/121/SQLRF/ap_keywd001.htm

Eks:
create table test (
password varchar2(10)
);
-- This works
select password from test;

create table test2 (
"password" varchar2(10)
);
-- This fails
select password from test2;
-- This works
select "password" from test2;

-- This is my current workaround (using CAPITALS in the changeset)
create table test3 (
"PASSWORD" varchar2(10)
);
select passWOrd from test3

[nvoxland]

From what I can tell, it looks like it was a reserved word in Oracle 10g but removed in 11g.

@mukhanov were you running against 10g?

[mukhanov]

@nvoxland No it was 11g.

[nvoxland]

https://docs.oracle.com/cd/B28359_01/appdev.111/b31231/appb.htm#CJHIIICD doesn't list password for 11g. Do you have an example of the failing SQL?

[axdotl]

List of Oracle keywords is available in view V$RESERVED_WORDS

SELECT * FROM v$reserved_words WHERE keyword = 'PASSWORD';

KEYWORD                            LENGTH RESERVED RES_TYPE RES_ATTR RES_SEMI DUPLICATE
------------------------------ ---------- -------- -------- -------- -------- ---------
PASSWORD                                8 N        N        N        N        N        

[blirp]

Note that it says 'N' for all the reserved-fields. IOW it isn't actually reserved. See fx. https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2126.htm#REFRN30204

[nvoxland]

Thanks for the pointer to V$RESERVED_WORDS. It seems like you need non-standard permissions to query it so I can't just pull the word list from there, unfortunately.

I created https://liquibase.jira.com/browse/CORE-2143 to track the change. It is more than I would like to do in the 3.3.1 patch release. For now the best work-around is to store password as uppercase in the changelog or use to fix the generated SQL.

[ghost]

If 10g is different from 11g. should they support with two different driver (or oracledatabase)?

Or version specific different should be handled in driver like MSSQL have too many "if majorVersion > 9" around?