Maven: PL / SQL script with sql-maven-plugin throw error PLS-00103

Question

I am trying to use sql-maven-plugin to execute a PL / SQL script in an Oracle 11 database. Although the script is valid PL / SQL (as far as I can tell), the execution throws error PLS-00103:

SQL script: (drop_all_tables.sql)

BEGIN EXECUTE IMMEDIATE 'DROP TABLE MY_TABLE'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; 

And my plugin configuration:

 <plugin> <groupId>org.codehaus.mojo</groupId> <artifactId>sql-maven-plugin</artifactId> <version>1.5</version> <dependencies> <dependency> <groupId>oracle</groupId> <artifactId>jdbc</artifactId> <version>11.2.0.2.0</version> </dependency> </dependencies> <executions> <execution> <id>create-schema</id> <phase>process-test-resources</phase> <goals> <goal>execute</goal> </goals> <configuration> <driver>oracle.jdbc.driver.OracleDriver</driver> <url>${jdbc.url}</url> <username>${jdbc.username}</username> <password>${jdbc.password}</password> <autocommit>true</autocommit> <srcFiles> <srcFile>src/main/resources/sql/drop_all_tables.sql</srcFile> </srcFiles> </configuration> </execution> </executions> </plugin> 

And this is the result of running Maven:

 [ERROR] Failed to execute: BEGIN EXECUTE IMMEDIATE 'DROP TABLE MY_TABLE'; [INFO] ------------------------------------------------------------------------ [ERROR] BUILD ERROR [INFO] ------------------------------------------------------------------------ [INFO] ORA-06550: line 2, column 43: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge 

Answer 1

I assume the plugin splits the sql script into semicolons and tries to execute each part independently. The first statement would be

 BEGIN EXECUTE IMMEDIATE 'DROP TABLE MY_TABLE'; 

What is incomplete regarding oracle. The plugin has two configuration options to change this behavior, delimiter and delimiterType . By changing the configuration as shown below and dividing the BEGIN blocks on / on the same line, you can execute this script.

 <configuration> <delimiter>/</delimiter> <delimiterType>row</delimiterType> </configuration>