换行符 \n 给出“java.sql.SQLException: ORA-00911: invalid character\n” Oracle 11g
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20607524/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
newline charter \n gives "java.sql.SQLException: ORA-00911: invalid character\n" Oracle 11g
提问by Learner
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 脚本并将其存储在一个String 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\nat 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: 无效字符\nat 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) 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)在 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 Developerit runs fine.
请注意，当我复制粘贴此文件的内容并通过Oracle SQL Developer运行脚本时，它运行良好。
采纳答案by Alex Poole
I think the \nreference 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 executehas to be a single statement.
我认为该\n参考是一个红鲱鱼，是如何记录错误的人工制品。您正在尝试在一个execute. 这是不允许的。分号是 SQL*Plus 中的语句分隔符，而不是 SQL，即使只有一条语句也会生成 ORA-00911。并且execute必须是单个语句。
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 triggerbecause 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 executehas a single statement, and will be much happier.
您需要将每个语句放在一个单独的文件中（没有结尾的分号create sequence；您仍然需要它，create trigger因为它结束了触发器的 PL/SQL 块，而不是充当语句分隔符 - 令人困惑，我知道） ，并分别读取和执行它们。然后每个人execute都有一个单一的陈述，并且会更快乐。
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;
回答by Sach
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 然后运行它会起作用
回答by atripathi
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
