Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Community Answers
1

In new to liquibase, Kindly pardon if this is a silly question

Below is my changelog

<?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"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd         http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.3.xsd">
   <changeSet author="PS,Adithya" id="1643127110260">
      <comment>operation 'run' by 'PS,Adithya' for 'mysql.sql'</comment>
      <sqlFile path="./run/mysql.sql"
               relativeToChangelogFile="true"
               endDelimiter="/"
               encoding="UTF-8"
               splitStatements="true"
               stripComments="true"/>
   </changeSet>
</databaseChangeLog>

Here is my sql file

GRANT SELECT,UPDATE,INSERT,DELETE ON xxxxdb.yyyy TO xxxxdb;
GRANT SELECT,UPDATE,INSERT,DELETE ON xxxxdb.yyyyzzz TO xxxxdb;
GRANT EXECUTE ON xxxxdb.xxxxvvvv TO xxxxdb;
GRANT EXECUTE ON xxxxdb.xxxxvvv TO xxxxdb;
COMMIT;
/

Im getting the below error while running update, Any idea what mistake im doing ? It executes fine with sqlplus though.

Caused by: Error : 933, Position : 75, Sql = GRANT SELECT,UPDATE,INSERT,DELETE ON xxxxdb.yyyy TO xxxxdb;
COMMIT;, Error Msg = ORA-00933: SQL command not properly ended

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
	... 79 more
2

Each statement in mysql.sql must be terminated by “/”, not semicolon (";"), since you set that as the endDelimiter.

GRANT SELECT,UPDATE,INSERT,DELETE ON xxxxdb.yyyy TO xxxxdb
/
GRANT SELECT,UPDATE,INSERT,DELETE ON xxxxdb.yyyyzzz TO xxxxdb
/
GRANT EXECUTE ON xxxxdb.xxxxvvvv TO xxxxdb
/
GRANT EXECUTE ON xxxxdb.xxxxvvv TO xxxxdb
/
COMMIT
/

Side note: commit is not needed for grant statements in Oracle

1 Like
3

Thank you so much !!

4

@daryldoak How about execute statements, I tried the same formatting and it didn’t go through

execute xxxxdb.yyyy004_rule
/
execute xxxxdb.yyyy005_rule
/
execute xxxxdb.yyyy006_rule
/
5

I should have mentioned, there’s no reason to change the endDelimiter for “basic” sql statements, which only contain one semicolon. You typically only need to change the endDelimiter for PL/SQL, which contain many semicolons.

In general, I would question why you are using the sqlFile include method instead of just putting the sql in the xml, like this:

<changeSet author="PS,Adithya" id="1643127110260">
      <comment>operation 'run' by 'PS,Adithya' for 'mysql.sql'</comment>
      <sql>
        GRANT SELECT,UPDATE,INSERT,DELETE ON xxxxdb.yyyy TO xxxxdb;
        GRANT SELECT,UPDATE,INSERT,DELETE ON xxxxdb.yyyyzzz TO xxxxdb;
        GRANT EXECUTE ON xxxxdb.xxxxvvvv TO xxxxdb;
        GRANT EXECUTE ON xxxxdb.xxxxvvv TO xxxxdb;
      </sql>
   </changeSet>

No need for an endDelimiter.

6

including sqlfile gives more comfort while checking in DB changes(just a personnel preference) into source control. I use ‘update’ to perform run and rollback as each execution is minimal.

I am considering to switch to liquibase formatted sql in future until i get comfortable. With your suggestions im able to execute grant statements without any issues, Should i continue the same formatting to execute statements as well ?

7

That’s fine. sqlFile works also, it just requires an extra changelog file.

Yes, execute statements should not need endDelimiter since they each end in a semicolon.

1 Like
8

Thank you very much, seems we are not supposed to use execute statements as its SQLPLUS specefic according to - oracle - ORA-00900: invalid SQL statement error - Stack Overflow

Covering the block inside a BEGIN END; / solved the issue for execute statements

1 Like