11

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

The 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 output from the Maven execution:

[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
Tunaki
  • 132,869
  • 46
  • 340
  • 423
Guillaume
  • 22,694
  • 14
  • 56
  • 70
  • The code output in your Maven error message doesn't quite match the code you posted at the top of your thread. – APC Sep 28 '11 at 08:35
  • Oh true sorry, I edited out the table name to be generic, forgot to do it in the error output - done now :) – Guillaume Sep 28 '11 at 08:36
  • the point is, the code you're actually *executing* is different from what *you're showing us*. How do you expect us to interpret a syntax error if you don't show us the actual code? – APC Sep 28 '11 at 11:19
  • No, no, the code I'm executing produces the same error - I modified it now to show the correct output error (the previous one was the same error, but on a different table) – Guillaume Sep 28 '11 at 12:02

1 Answers1

16

I guess the plugin is splitting the sql script by semicolons and trying to execute each part independently. The first statement would be

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE MY_TABLE';

Which is incomplete as far as oracle is concerned. The plugin does have two configuration parameters to change this behaviour, delimiter and delimiterType. By changing the configuration like below and separating your BEGIN blocks by a / on a line by itself you should be able to execute this script.

<configuration>
    <delimiter>/</delimiter>
    <delimiterType>row</delimiterType>
</configuration>
Jörn Horstmann
  • 33,639
  • 11
  • 75
  • 118
  • 1
    Thanks, that may work, and I'll certainly check it out, but I've found an alternate way of doing what I wanted without having to use PL/SQL. I added continue to the configuration section of my Maven plugin, and the SQL is now the much simpler DROP TABLE MY_TABLE; – Guillaume Sep 28 '11 at 15:18
  • I would add that as an answer, but cannot add an answer to my own question for now as I'm a newbie here :) – Guillaume Sep 28 '11 at 15:19
  • 1
    The answer (maven configuration) works, and the script is correct – Ralph Apr 23 '12 at 09:26