CODEWITHSUNDEEP
oracle-databaseviewliquibase
Jim Zucker
Jim Zucker

Reputation: 870

Liquibase create view always gets error:

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>

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

Upvotes: 1

Views: 3217

Answers (3)

Jim Zucker
Jim Zucker

Reputation: 870

The solution was to remove the semicolon a the end of the view so this works:

Upvotes: 2

pitchblack408
pitchblack408

Reputation: 2983

Is your sql statement correct?

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

Upvotes: 1

mentallurg
mentallurg

Reputation: 5207

You log show that you are trying to create a view in the SYS schema. This is not allowed.

  1. Create (if not yet created) a schema for you application. Like COOL_APP.

  2. Use this schema in your XML. Like COOL_APP.VIEW_TEST.

Upvotes: 1

Related Questions