Error while trying to compile package using ant sql task

[Gaurav Kr. Arora]

Hi,

I am facing issue while running a sql script using ant task sql.
In my sql I have a package and when I run the package from sql developer console, it gets compiled successfully but when i run using ant command, it gives below error:

Failed to execute: FUNCTION get_result (p_transaction_id IN NUMBER, p_result_type IN VARCHAR2) RETURN VARCHAR2
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)


The .sql file, contains the below piece:

CREATE OR REPLACE PACKAGE UTIL
AS
   FUNCTION get_result (p_lot              IN VARCHAR2,
                        p_result_type      IN VARCHAR2,
                        p_transaction_id   IN NUMBER := NULL)
      RETURN VARCHAR2;

   FUNCTION get_result (p_transaction_id   IN NUMBER,
                        p_result_type      IN VARCHAR2)
      RETURN VARCHAR2;

   
   PROCEDURE change_mes_header_status (p_sakey           IN NUMBER,
                                       p_status          IN VARCHAR2,
                                       p_date_modified   IN DATE,
                                       p_error_message   IN VARCHAR2);

END UTIL;

the way I run using ant is:

<sql driver="oracle.jdbc.driver.OracleDriver"
      url="jdbc:oracle:thin:@${jdbc.connectstring}"
      userid="${sys_user} as sysdba"
      password="${sys_user_password}"
      print="yes"
      showheaders="true">
      
        <transaction src="/tmp/Sample.sql"/>      
  </sql>

Please help in resolving this.

[Peter Johnson]

Try running ant with -v, that might give a clue as to exactly what the <sql> task is sending to the database. Also check your database logs, those should also tell you exactly what the database is seeing. Either of those might give a clue as to what went wrong.