Maven:具有sql-maven-plugin的PL/SQL脚本引发错误PLS-00103 [英] Maven: PL/SQL script with sql-maven-plugin throws error PLS-00103

问题描述

我正在尝试使用sql-maven-plugin在Oracle 11数据库上执行PL/SQL脚本.尽管该脚本是有效的PL/SQL(据我所知)，但执行过程却给我一个PLS-00103错误:

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:

SQL脚本:(drop_all_tables.sql)

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;

我的插件配置:

<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>

这是Maven执行的输出:

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

推荐答案

我猜想该插件正在用分号分隔sql脚本，并尝试独立执行每个部分.第一个语句是

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';

就oracle而言，哪一个还不完整.该插件确实有两个配置参数可以更改此行为，定界符和 delimiterType .通过更改如下配置，并单独在一行上用/分隔BEGIN块，您应该能够执行此脚本.

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>

这篇关于Maven:具有sql-maven-plugin的PL/SQL脚本引发错误PLS-00103的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！