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.