newline charter \n提供了“java.sql”。SQLException: ORA-00911:无效字符\n " Oracle 11g。

I have Oracle DB 11g Enterprise Edition and I want to create a table by reading the sql script from a file.Through java code I am reading following sql script from a file and storing it in a String sqlBlock:

我有Oracle DB 11g企业版，我想通过从一个文件读取sql脚本创建一个表。通过java代码，我从一个文件中读取sql脚本并将其存储在一个字符串sqlBlock中:

CREATE SEQUENCE VerHist_SeqNum
   START WITH 1 
   INCREMENT BY 1;
CREATE TABLE VerHist
(
  SequenceNumber NUMBER(10,0)  NOT NULL,
  SQLFileName VARCHAR2(100)  NOT NULL,
  STATUS VARCHAR2(10)  NOT NULL,
  AppliedDate DATE  NOT NULL,
  DateCreated DATE 
   DEFAULT (SYSDATE),
  DateUpdated DATE 
   DEFAULT (SYSDATE),
  CONSTRAINT PK_VerHist PRIMARY KEY( SequenceNumber ),
  CONSTRAINT UC_VerHist_SQLFileNa UNIQUE( SQLFileName )
);
CREATE OR REPLACE TRIGGER VerHist_SeqNum_TRG
   BEFORE INSERT 
   ON VerHist
   FOR EACH ROW
   BEGIN
      SELECT VerHist_SeqNum.NEXTVAL INTO :NEW.SequenceNumber
        FROM DUAL;
   END;

When I execute this query it gives

当我执行这个查询时，它给出。

java.sql.SQLException: ORA-00911: invalid character\n at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168) at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1687) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653)

java.sql。SQLException异常:ora - 00911:mso - hansi - font - family: tahoma; mso - hansi - font - family: tahoma; mso - hansi - font - family: tahoma ' >， doexecutewithtimeout (OracleStatement.java:1168) at oracle.jdbc.driver. executeinternal (OracleStatement.java:1687)在oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653)。

Following is my code to execute the sql block:

下面是执行sql块的代码:

Statement stmt = conn.createStatement();
String sqlBlock = //"this contains the content of the file (it contains \n charters)";
stmt.execute(sqlBlock);

Is the newline charter invalid here, if yes, how to get this working otherwise?

这里的换行执照是否无效，如果是的话，如何让这个工作不正常?

Please note that when I copy paste the contents of this file and run the script through Oracle SQL Developer it runs fine.

请注意，当我复制粘贴该文件的内容并通过Oracle SQL Developer运行脚本时，它运行良好。

3 个解决方案

#1

I think the \n reference is a red-herring, and an artefact of how the error is being logged. You're trying to run two SQL statements, separated by a semi-colon, in one execute. That is not allowed. The semi-colon is a statement separator in SQL*Plus, not in SQL, and will generate an ORA-00911 even with a single statement. And execute has to be a single statement.

我认为\n引用是一个红鲱鱼，以及一个关于错误如何被记录的人工信息。您正在尝试运行两个SQL语句，其中一个执行中由一个分号分隔。这是不允许的。分号是SQL*Plus中的语句分隔符，而不是SQL，即使只有一个语句，它也会生成一个ORA-00911。执行必须是一个单独的语句。

If you were doing DML you could wrap the statements in a PL/SQL block and execute that, but since this is DDL you can't do that unless you resort to dynamic SQL, which is going to be overly complicated and messy for what you're trying to do.

如果你在做DML，你可以将语句包装在PL/SQL块中并执行，但由于这是DDL，你不能这样做，除非你使用动态SQL，这将会变得过于复杂和混乱，因为你正在尝试做的事情。

You need to put each statement in a separate file (without the trailing semi-colon on the create sequence; you still need it on the create trigger because there it is ending the trigger's PL/SQL block, not acting as a statement separator - confusing, I know), and read and execute them separately. Then each execute has a single statement, and will be much happier.

您需要将每个语句放入一个单独的文件中(在创建序列中不使用尾随分号;您仍然需要它在create触发器上，因为它正在结束触发器的PL/SQL块，而不是作为语句分隔符——我知道)，并分别读取和执行它们。然后每个执行都有一个单独的语句，并且会更快乐。

As an aside, you don't need to select your sequence value into your variable in 11g; you can now do it like this:

顺便说一下，您不需要在11g中选择您的变量的序列值;你可以这样做:

CREATE OR REPLACE TRIGGER VerHist_SeqNum_TRG
BEFORE INSERT 
ON VerHist
FOR EACH ROW
BEGIN
  :NEW.SequenceNumber := VerHist_SeqNum.NEXTVAL;
END;

#2

When you copy paste the contains of the file to browser. Browser will treat \n as new line. Whereas for the code /n is character only. Try to replace \n with single space and then run it will work

复制粘贴到浏览器的文件。浏览器将处理\n作为新行。而对于代码/n仅是字符。尝试用单个空格替换\n，然后运行它。

#3

sqlBlock = sqlBlock.replaceAll("\n"," ");

sqlBlock = sqlBlock。replaceAll(" \ n "," ");

Remove the \n from the query. If you want the query to be formatted in sql developer, you can select the query and press Ctrl+F7

从查询中删除\n。如果您希望查询在sql developer中被格式化，您可以选择查询并按Ctrl+F7。

#1