# Problem with Custom SQL
**URL:** https://forum.liquibase.org/t/problem-with-custom-sql/708
**Category:** General Discussion
**Created:** [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708 "2010-08-12T19:19:00Z")
**Posts on this page:** 16
**Page:** 1
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/1 "2010-08-12T19:19:00Z")
I know… me again :), but I’m dealing with this thing and I can’t make it work, but it shoud drop the materialized view if it exists:
BEGIN EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW MY\_MVIEW'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -12003 THEN RAISE; END IF; END;
DROP MATERIALIZED VIEW MY\_VIEW
basically this part gives me always an error and I don’t know why, because if I run it normaly it works well:
BEGIN
EXECUTE IMMEDIATE ‘DROP MATERIALIZED VIEW MY\_MVIEW’;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -12003 THEN
RAISE;
END IF;
END;
is similar to the example given in the sql custom liquibase manual
Thanks,
Alexis.
---
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/2 "2010-08-12T19:19:00Z")
This simple statement also fails:
BEGIN dbms\_mview.refresh('MY\_MVIEW'); END;
So it seems that anything between BEGIN END fails ☹
---
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/3 "2010-08-12T19:19:00Z")
Well with createProcedure it works, but I like the more 😉
BEGIN dbms\_mview.refresh('MY\_MVIEW'); END;
---
### Author:  [@taranenko](https://forum.liquibase.org/u/taranenko)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/4 "2010-08-12T19:19:00Z")
> _Originally posted by: Alexis_
> This simple statement also fails:
> BEGIN dbms\_mview.refresh('MY\_MVIEW'); END;
>
> So it seems that anything between BEGIN END fails ☹
Have you with attributes endDelimiter etc. tested?
---
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/5 "2010-08-12T19:19:00Z")
Yes everything… I also added the / at the end with that delimiter and so on… and nothing… I’ll try again today.
---
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/6 "2010-08-12T19:19:00Z")
Actually… I have another RC6 build, I think revision 1680 which works perfectly with this:
BEGIN EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW MY\_MVIEW'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -12003 THEN RAISE; END IF; END;
---
### Author:  [@nvoxland](https://forum.liquibase.org/u/nvoxland)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/7 "2010-08-12T19:19:00Z")
So, it is working for you now? Or is there still a problem?
Nathan
---
### Author:  [@nvoxland](https://forum.liquibase.org/u/nvoxland)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/8 "2010-08-12T19:19:00Z")
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
Nathan
---
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/9 "2010-08-12T19:19:00Z")
hi Nathan,
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
---
### Author:  [@alwyn\_schoeman](https://forum.liquibase.org/u/alwyn_schoeman)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/10 "2010-08-12T19:19:00Z")
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?
Wild guess.
---
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/11 "2010-08-12T19:19:00Z")
hi Alwyn:
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 \ \<\> 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 \ \<\> 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
---
### Author:  [@nvoxland](https://forum.liquibase.org/u/nvoxland)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/12 "2010-08-12T19:19:00Z")
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…
Nathan
---
### Author:  [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/13 "2010-08-12T19:19:00Z")
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
---
### Author:  [@nvoxland](https://forum.liquibase.org/u/nvoxland)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/14 "2010-08-12T19:19:00Z")
The splitStatemetns should be working now. Could you test it with a build from trunk or RC6 when it is out?
Nathan
---
### Author:  [@Guest](https://forum.liquibase.org/u/Guest)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/15 "2010-08-12T19:19:00Z")
Hi Nathan,
I’m running a changeLogFile from maven using the version 2.0.5.
Here is my changeSet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Here is what I get
1.
2. SEVERE 17/05/12 1:26 PM:liquibase: Change Set /local/changelog.xml::insertdata-1::Maven failed. Error: Error executing SQL DECLARE
3. \
### Author:  [@nvoxland](https://forum.liquibase.org/u/nvoxland)
#### Post date: [August 12, 2010, 7:19pm UTC](https://forum.liquibase.org/t/problem-with-custom-sql/708/16 "2010-08-12T19:19:00Z")
It appears to be an issue introduced in 2.0.5 [https://liquibase.jira.com/browse/CORE-1170](https://liquibase.jira.com/browse/CORE-1170)
2.0.4 should work correctly for you.
Nathan