Cannot execute Oracle stored procedure

Hi,
I am new to Liquibase and trying to get my head around it.
I’d like Liquibase to execute an existing stored procedure on Oracle 11g, but I’m getting an error.
This is an existing, well tested stored proc and I can run it with no problems from SQLDeveloper.
Does Liquibase support execution of stored procs in Oracle?

Here is my test.xml file:

<?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-2.0.xsd
         logicalFilePath=“test.xml”>
   
      exec ODS.UTIL_PKG.TRUNCATE(‘MY_TABLE’);
     
   


But I have also tried running in through the include sql file, with the same effect, i.e. error.

The trace is:
Executing the Liquibase command ‘update’ in the schema 'ods’
INFO 7/23/13 2:46 PM:liquibase: Successfully acquired change log lock
INFO 7/23/13 2:46 PM:liquibase: Reading from ods.DATABASECHANGELOG
INFO 7/23/13 2:46 PM:liquibase: Reading from ods.DATABASECHANGELOG
SEVERE 7/23/13 2:46 PM:liquibase: Change Set test.xml::1::user failed.  Error: Error executing SQL exec ODS.DDL_UTL_PKG.TRUNCATE(‘MY_TABLE’): ORA-00900: invalid SQL statement

liquibase.exception.DatabaseException: Error executing SQL exec ODS.DDL_UTL_PKG.TRUNCATE(‘MY_TABLE’): ORA-00900: invalid SQL statement

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
        at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)
        at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1075)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
        at liquibase.Liquibase.update(Liquibase.java:114)
        at liquibase.integration.commandline.Main.doMigration(Main.java:825)
        at liquibase.integration.commandline.Main.main(Main.java:134)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at org.birch.cryptomainia.DecryptArgv.callTarget(Unknown Source)
        at org.birch.cryptomainia.DecryptArgv.main(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)  at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1909)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1871)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
        at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
        … 15 more
INFO 7/23/13 2:46 PM:liquibase: Successfully released change log lock
Liquibase Update Failed: Error executing SQL exec ODS.DDL_UTL_PKG.TRUNCATE(‘MY_TABLE’): ORA-00900: invalid SQL statement

SEVERE 7/23/13 2:46 PM:liquibase: Error executing SQL exec ODS.DDL_UTL_PKG.TRUNCATE(‘MY_TABLE’): ORA-00900: invalid SQL statement

liquibase.exception.MigrationFailedException: Migration failed for change set test.xml::1::user:
     Reason: liquibase.exception.DatabaseException: Error executing SQL exec ODS.DDL_UTL_PKG.TRUNCATE(‘MY_TABLE’): ORA-00900: invalid SQL statement
:
          Caused By: Error executing SQL exec ODS.DDL_UTL_PKG.TRUNCATE(‘MY_TABLE’): ORA-00900: invalid SQL statement
:
          Caused By: ORA-00900: invalid SQL statement

etc, etc…

I will greatly appretiate some help.
Thanks.

– tinyboson

I have actually changed the code to the following and it seems to call the SP. Seems like I need the begin-end block.

<?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-2.0.xsd
         logicalFilePath=“test.xml”>
   
      <![CDATA[
        BEGIN
          ODS.UTIL_PKG.TRUNCATE('MY_
TABLE');
        END;
      ]]>

     
   

I guess Execution will not support, It will create the procedure only… can you try to use below tag

<?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-2.0.xsd
         logicalFilePath=“test.xml”>
   
         
            <![CDATA[
                exec ODS.UTIL_PKG.TRUNCATE('MY_TABLE');
                    ]]>
       
       
       
       
        <![CDATA[
                  select * from dual;
                ]]>