For the record, is just with a couple defaults that makes it work better (mainly the end delimiter setting). Some people also like the fact that it at least somewhat describes what is happening in the change than the generic
It is still failing, what I meant is that it was working before and now it fails, here is an example:
BEGIN
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW MY_MVIEW';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -12003 THEN
RAISE;
END IF;
END;
CREATE MATERIALIZED VIEW MY_MVIEW
BUILD DEFERRED
REFRESH FORCE ON DEMAND
AS
SELECT * FROM test_table;
BEGIN
dbms_mview.refresh('MY_MVIEW');
END;
DROP MATERIALIZED VIEW MY_MVIEW
The first sql should work but it fails, while the third works but because of the createProcedure. So I assume it is something wrong in the custom sql tag
As far as I understand the createProcedure has splitStatements=true as opposed to raw sql. Would it make a difference if you have splitStatements=true in your tag?
Yes it fails no matter what option I put:
test:
[echo] -----------------------------------
[echo] Testing scripts
[echo] -----------------------------------
[exec] Aug 17, 2010 11:13:05 AM liquibase.logging.jvm.JavaUtilLogger info
[exec] INFO: Successfully acquired change log lock
[exec] Aug 17, 2010 11:13:05 AM liquibase.logging.jvm.JavaUtilLogger info
[exec] INFO: Reading from DATABASECHANGELOG
[exec] Aug 17, 2010 11:13:06 AM liquibase.logging.jvm.JavaUtilLogger info
[exec] INFO: Reading from DATABASECHANGELOG
[exec] Aug 17, 2010 11:13:06 AM liquibase.logging.jvm.JavaUtilLogger info
[exec] INFO: Successfully released change log lock
[exec] Liquibase Update Failed: Error executing SQL BEGIN
[exec] EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW MY_MVIEW’. For more information, use the --logLevel flag)
[exec] Aug 17, 2010 11:13:06 AM liquibase.logging.jvm.JavaUtilLogger info
[exec] INFO: Error executing SQL BEGIN
[exec] EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW MY_MVIEW’
[exec] liquibase.exception.MigrationFailedException: Migration failed for change set test.xml::test-custom-sql::alexis:
[exec] Reason: liquibase.exception.DatabaseException: Error executing SQL BEGIN
[exec] EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW MY_MVIEW’:
[exec] Caused By: Error executing SQL BEGIN
[exec] EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW MY_MVIEW’:
[exec] Caused By: ORA-06550: line 2, column 65:
[exec] PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following:
[exec] * & = - + ; < / > at in is mod remainder not rem return
[exec] returning <an exponent ()> <> or != or ~= >= <= <> and or
[exec] like like2 like4 likec between into using || multiset bulk
[exec] member submultiset
[exec] at liquibase.changelog.ChangeSet.execute(ChangeSet.java:286)
[exec] at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
[exec] at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
[exec] at liquibase.Liquibase.update(Liquibase.java:108)
[exec] at liquibase.integration.commandline.Main.doMigration(Main.java:676)
[exec] at liquibase.integration.commandline.Main.main(Main.java:110)
[exec] Caused by: liquibase.exception.DatabaseException: Error executing SQL BEGIN
[exec] EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW MY_MVIEW’
[exec] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
[exec] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:97)
[exec] at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:982)
[exec] at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:969)
[exec] at liquibase.changelog.ChangeSet.execute(ChangeSet.java:257)
[exec] … 5 more
[exec] Caused by: java.sql.SQLException: ORA-06550: line 2, column 65:
[exec] PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following:
[exec] * & = - + ; < / > at in is mod remainder not rem return
[exec] returning <an exponent ()> <> or != or ~= >= <= <> and or
[exec] like like2 like4 likec between into using || multiset bulk
[exec] member submultiset
[exec] at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
[exec] at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
[exec] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
[exec] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
[exec] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
[exec] at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
[exec] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
[exec] at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:936)
[exec] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
[exec] at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
[exec] at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
[exec] at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:298)
[exec] at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:88)
[exec] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
[exec] … 9 more
I’m going to be on on vacation until Aug 29th, so I’ll get back to you after then. Sorry I wasn’t able to give you a good answer beforehand. I looked into it a bit, but not enough to get a good answer. Ended up with too much going on…
No problem Nathan, I did a local change to my copy in order to make it run, basically I added some logic to support splitStatemetnts=false. This is just temporary until your fix is ready
