SQL Error: ORA-00911: Invalid Character

Question

I'm an new to Oracle and I'm trying to create a table but I keep getting an invalid character warning. I've tried deleting it and retyping it and have checked for any of the invalid characters and I can't seem to find any. My table creation code is:

CREATE TABLE DEPARTMENT (
DNUMBER CHAR(1) PRIMARY KEY, 
DNAME VARCHAR2(20), 
MGRSSN CHAR(11), 
MGRSTARTDATE CHAR(10), 
CONSTRAINT DEPARTMENT_FK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN));

CREATE TABLE EMPLOYEE (
SSN CHAR(11) PRIMARY KEY, 
FNAME VARCHAR2(20), 
MINIT CHAR(1), 
LNAME VARCHAR2(20), 
BIRTHDATE CHAR(10), 
ADDRESS VARCHAR2(30), 
SEX CHAR(1), 
SALARY INTEGER, 
SUPERSSN CHAR(11), 
DNO CHAR(1), 
CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN), 
CONSTRAINT EMPLOYEE_FK2 FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER));

Answer 1

As far as I can tell there's no "bad character" issue here, as I couldn't provoke the error as originally reported. However, a couple of the constraints on these tables - DEPARTMENT_FK on DEPARTMENT, and EMPLOYEE_FK1 on EMPLOYEE - are causing issues. The DEPARTMENT_FK constraint attempts to reference the EMPLOYEE table before it has been created. In the second case, the self-referential EMPLOYEE_FK1 constraint fails to be created because the EMPLOYEE table doesn't exist when the CREATE TABLE for EMPLOYEE is executed.

To work around this these constraints should be removed from the table definition. The tables should then be created, and the constraints added out-of-line:

CREATE TABLE DEPARTMENT (
DNUMBER CHAR(1) PRIMARY KEY, 
DNAME VARCHAR2(20), 
MGRSSN CHAR(11), 
MGRSTARTDATE CHAR(10)
--CONSTRAINT DEPARTMENT_FK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
);

CREATE TABLE EMPLOYEE (
SSN CHAR(11) PRIMARY KEY, 
FNAME VARCHAR2(20), 
MINIT CHAR(1), 
LNAME VARCHAR2(20), 
BIRTHDATE CHAR(10), 
ADDRESS VARCHAR2(30), 
SEX CHAR(1), 
SALARY INTEGER, 
SUPERSSN CHAR(11), 
DNO CHAR(1), 
--CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN), 
CONSTRAINT EMPLOYEE_FK2 FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER));

ALTER TABLE DEPARTMENT
  ADD CONSTRAINT DEPARTMENT_FK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN);

ALTER TABLE EMPLOYEE
  ADD CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN);

Done this way the tables will be created with the desired constraints.

dbfiddle here

Best of luck.

Answer 2

I have faced the same problem in SQLplus. I have to copy some create table sql from the other files which worked before and change it , the changed sql could work fine. After compare this two files in Eclipse, there is difference only in white space . It seems that something wrong in the file character format.