Cannot execute Oracle stored procedure

General Discussion
1

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=“Liquibase | Liquibase XML Schema Definitions”
  xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”
  xsi:schemaLocation=“Liquibase | Liquibase XML Schema Definitions
         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

2

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;
      ]]>
     
   

3

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;
                ]]>
       
       
   




4

Use

< sqlFile
path=“xxx.sql”
relativeToChangelogFile=“true”
stripComments=“true”
splitStatements=“false”
endDelimiter=“/”
dbms=“oracle”
/>

and .sql file should have

BEGIN
package.function();
END;
/