newline charter \n gives "java.sql.SQLException: ORA-00911: invalid character\n" Oracle 11g
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.
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.
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.
As an aside, you don't need to select your sequence value into your variable in 11g; you can now do it like this:
CREATE OR REPLACE TRIGGER VerHist_SeqNum_TRG
BEFORE INSERT
ON VerHist
FOR EACH ROW
BEGIN
:NEW.SequenceNumber := VerHist_SeqNum.NEXTVAL;
END;
Related videos on Youtube
Learner
Updated on July 08, 2022Comments
-
Learner almost 2 years
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
: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)
Following is my code to execute the sql block:
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.
-
s_beiwhy you have \n characters? remove them...
-
-
Learner over 10 yearsI tried replacing
\n
with single space, but still getting the same error message. -
Sachin over 10 years@John could you please add code which explains how you have read the file
-
atripathi over 10 yearsCan you paste the entire statement instead of
String sqlBlock = //"this contains the content of the file (it contains \n charters)";
-
Lawrence over 9 yearsI had the same issue. Turns out my query string had the ; at the end! :-O Silly me!