ORA-06550 第 2 行，第 44 列：PLS-00103：遇到符号"end-of-file"

Question

我正在尝试使用一些rxjava2方法从Java执行SQL脚本。我有一个名为init_db.sql的资源文件。

其内容：

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE animals';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
CREATE TABLE animals (
id        NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
name      VARCHAR2(40) NOT NULL,
amount    INT,
CONSTRAINT animals_pk PRIMARY KEY (id));
INSERT INTO animals (name, amount) values ('Cats', 10);
INSERT INTO animals (name, amount) values ('Dogs', 10);
INSERT INTO animals (name, amount) values ('Rats', 10);

执行Java代码的方法是下一个：

public  Completable initOracleDatabase(Vertx vertx, JDBCClient jdbc) {
System.out.println("Init Oracle Database **** executing..sql script...");
Completable completable =
jdbc.rxGetConnection().flatMapCompletable(sqlConnection -> vertx.fileSystem().rxReadFile("init_db.sql")
.flatMapObservable(buffer -> Observable.fromArray(buffer.toString().replaceAll(";.*$", "").split(";")))
.flatMapCompletable(sqlConnection::rxExecute)
.doAfterTerminate(sqlConnection::close));

return completable;

这是我从日志中得到的错误：

java.sql.SQLException: ORA-06550: line 2, column 44:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset

以及错误跟踪日志：

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)

基本上，我想知道我是否可以在一个唯一的语句中使用这个SQL脚本？或者如果我遗漏了什么，或者在SQL脚本和Java之间转换了一些错误代码？

Answer 1

您将PL/SQL和SQL代码混合在同一个脚本中。

• SQL语句可以由;终止
• PL/SQL块由换行符上的/终止，并将包含嵌套的PL/SQL(或SQL(语句，每个语句由;终止

因此，您不能天真地在;上分割代码，并期望它能正常工作，因为您会错误地分割PL/SQL块。

除了使用;作为SQL语句终止符外，还可以在换行符上使用/；这样你就可以把你的脚本重写为：

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE animals';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
CREATE TABLE animals (
id        NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
name      VARCHAR2(40) NOT NULL,
amount    INT,
CONSTRAINT animals_pk PRIMARY KEY (id)
)
/
INSERT INTO animals (name, amount) values ('Cats', 10)
/
INSERT INTO animals (name, amount) values ('Dogs', 10)
/
INSERT INTO animals (name, amount) values ('Rats', 10)
/

并在n/n上拆分脚本。

或者，您可以忽略捕获ORA-00942异常，只使用SQL并继续使用;语句终止符：

DROP TABLE animals;
CREATE TABLE animals (
id        NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
name      VARCHAR2(40) NOT NULL,
amount    INT,
CONSTRAINT animals_pk PRIMARY KEY (id)
);
INSERT INTO animals (name, amount) values ('Cats', 10);
INSERT INTO animals (name, amount) values ('Dogs', 10);
INSERT INTO animals (name, amount) values ('Rats', 10);

或者，您可以创建一个更复杂的解决方案，实际解析PL/SQL和SQL语句，并可以根据语法树将脚本拆分为单个语句。