newline charter \n gives "java.sql.SQLException: ORA-00911: invalid character\n" Oracle 11g

Question

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.

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

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;