Applying Oracle PL/SQL via JDBC

Question

Is it possible to create/replace PL/SQL via the Oracle JDBC driver, i.e not via SQLPLus* ?

Update 1: Driver: ojdbc14 (JDBC)

Update 2: Change is being applied to oracle via Ant <SQL> task, which is passed a sql script file.

In this instance using SQLPlus in the Ant script is not possible (long story).

Answer 1

Sure, you can use JDBC to execute arbitrary DDL (such as CREATE OR REPLACE) via JDBC. You just need to pass the execute method of the Statement object the PL/SQL block. There is an example of creating a trigger via JDBC in the Sun forums.

Answer 2

Yes, the standard Ant sql task will do this. These options are working for me:

sql driver="oracle.jdbc.driver.OracleDriver"
       delimiter="/"
       delimitertype="row"
       keepformat="yes"
       caching="true"
       escapeprocessing="no"

One thing I've found is that the processing is sensitive to the last line with the delimiter - if you have any whitespace after the "/" the execution will fail.