how to execute pl/sql procedure using ant.

[prateek sharmaa]

hello everyone,

i am writing an ant target to execute pl/sql procedure which is as follows

<target name="exportSizeRatio" description="Exporting size ratio">
                <sql
                        classpath="../../../../explodedear/lib/ojdbc6.jar" driver="oracle.jdbc.OracleDriver"
                        url="jdbc:oracle:thin:@${db.hostname}:${db.port}:${tns_alias}"
                        userid="${main_user_name}"
                        password="${main_password}"
                        onerror="continue" >
                             exec plandatacsv;
                </sql>
</target>

running the ant target throwing below error

exportSizeRatio:
[sql] Executing commands
[sql] Failed to execute: exec plandatacsv
[sql] java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
[sql] 0 of 1 SQL statements executed successfully

can anyone please help?

thanks,
pratz

[Peter Johnson]

Oracle doesn't like the SQL statement (line 08) that you are passing to it. Have you tried running that command using Oracle command line SQL interpreter?

[prateek sharmaa]

hey peter,

Peter Johnson wrote:Oracle doesn't like the SQL statement (line 08) that you are passing to it. Have you tried running that command using Oracle command line SQL interpreter?

i tried running exec plandatacsv; using sqlplus and it executed successfully. may be there is some other way to do it using ant. do you have any idea?

thanks,
pratz

[Peter Johnson]

No, I do not know if there are any special ways of calling 'exec' via the JDBC driver (which is what is happening with this Ant task). Perhaps the Oracle JDBC driver docs will provide some clues.

Another thought: is there anything in the Oracle error log? Or could you turn on request logging within Oracle to see exactly what is being passed to Oracle?