Create view always gets an error: ORA-00933: SQL command not properly ended

Description

*We have a simple create view test case on Liquibase 3.6.1 command line:*

We are trying to understand how to solve the error: ORA-00933: SQL command not properly ended

We have tried
1. <sql>
2. <createview>
3. Note the SQL generated by the <createview> executes correctly if we take it and run it manually.

Below is a simple test case.

Here is the changelog:

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<changeSet author="jim" id="test1">
<createView viewName="VIEW_TEST" replaceIfExists="true" >
select table_name FROM user_tables ORDER BY table_name;
</createView>
</changeSet>
</databaseChangeLog>

*Command*
{{time ./liquibase --changeLogFile=../changelog/test.xml --url=jdbc:oracle:thin:@//localhost:1521/ORCL --username "sys as sysdba" --password oracle --logLevel debug update

We always get this exception: (FYI we also tried as <sql> same result)

➜ liquibase-3.6.1 time ./liquibase --changeLogFile=../changelog/test.xml --url=jdbc:oracle:thin:@//localhost:1521/ORCL --username "sys as sysdba" --password oracle --logLevel debug update
Starting Liquibase at Mon, 04 Jun 2018 12:52:44 PDT (version 3.6.1 built at 2018-04-11 08:41:04)
*Unexpected error running Liquibase: ORA-00933: SQL command not properly ended*
[Failed SQL: CREATE OR REPLACE VIEW SYS.VIEW_TEST AS select table_name FROM user_tables ORDER BY table_name;]
liquibase.exception.MigrationFailedException: Migration failed for change set ../changelog/test.xml::test1::jim:
Reason: liquibase.exception.DatabaseException: ORA-00933: SQL command not properly ended
*[Failed SQL: CREATE OR REPLACE VIEW SYS.VIEW_TEST AS select table_name FROM user_tables ORDER BY table_name;]*
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:78)
at liquibase.Liquibase.update(Liquibase.java:202)
at liquibase.Liquibase.update(Liquibase.java:179)
at liquibase.integration.commandline.Main.doMigration(Main.java:1205)
at liquibase.integration.commandline.Main.run(Main.java:191)
at liquibase.integration.commandline.Main.main(Main.java:129)
Caused by: liquibase.exception.DatabaseException: ORA-00933: SQL command not properly ended
[Failed SQL: CREATE OR REPLACE VIEW SYS.VIEW_TEST AS select table_name FROM user_tables ORDER BY table_name;]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:356)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1211)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600)
... 7 common frames omitted
*Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended*

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:933)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1718)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1678)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:332)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
... 12 common frames omitted

For more information, please use the --logLevel flag
./liquibase --changeLogFile=../changelog/test.xml --username "sys as sysdba" 4.61s user 0.27s system 238% cpu 2.048 total

The sql test case gets the same error.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<changeSet author="jim" id="test2">
<sql dbms="h2, oracle" splitStatements="false" stripComments="true">
CREATE OR REPLACE VIEW SYS.VIEW_TEST AS select table_name FROM user_tables ORDER BY table_name;
</sql>
</changeSet>
</databaseChangeLog>

Environment

Linux

Activity

Show:

Jim Zucker June 7, 2018 at 4:36 PM

we got it to work by removing the semi-colon from the end of the view definition.

Jim Zucker June 5, 2018 at 1:27 PM

To confirm/clarify a couple of points:
1. The same error occurs if we use a schema, we removed it in the test case to make it simpler to test

ORA-00933: SQL command not properly ended Reason: liquibase.exception.DatabaseException: ORA-00933: SQL command not properly ended [Failed SQL: CREATE OR REPLACE VIEW ARO.USER_V AS select table_name FROM user_tables ORDER BY table_name;]

2. The sql produced by liquibase does execute correctly if we do updateSQL

Details

Reporter

Components

Affects versions

Priority

Created June 4, 2018 at 8:18 PM
Updated June 7, 2018 at 4:37 PM