Invalid Identifier for Department ID in Oracle SQL [closed]

Solution for Invalid Identifier for Department ID in Oracle SQL [closed]
is Given Below:

The code seems to be fine but for some unexplained reason I cant insert the values into the table. I only want to insert specific values but its showing as invalid for some reason

Table:

CREATE TABLE employees
(
    EMPLOYEE_ID  NUMBER(6) PRIMARY KEY NOT NULL,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(25) NOT NULL,
    PHONE_NUMBER VARCHAR(20),
    HIRE_DATE DATE NOT NULL,
    JOB_ID VARCHAR2(10) NOT NULL,
    SALARY NUMBER(8,2),
    COMMISSION_PCT NUMBER(2,2),
    MANAGER_ID NUMBER(6),
    DEPARTMENT_ID NUMBER(4)
);

Table is created and works as it should
Its when this gets added where it goes wrong:

INSERT INTO employee (EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID) 
VALUES(176, 'Taylor', 'SA_REP', 80);

Hope I can get an answer for this

After you fixed “something” (as comments suggest), now it is about invalid date you’re trying to insert. This is what you’re doing (note how SQL*Plus points to exact place where error happened):

SQL> INSERT INTO employees(
  2    employee_id,
  3    last_name,
  4    email,
  5    hire_date,
  6    job_id,
  7    department_id
  8  )VALUES(
  9    176,
 10    'Taylor',
 11    '[email protected]',
 12    31-AUG-1999,
 13    'SA_REP',
 14    80
 15  );
  31-AUG-1999,
     *
ERROR at line 12:
ORA-00984: column not allowed here

See? You can’t insert 31-AUG-1999, literally. It looks as a date to us, humans, but – it is not. If you try to enclose it into single quotes, yet another – different – error:

SQL> INSERT INTO employees(
  2    employee_id,
  3    last_name,
  4    email,
  5    hire_date,
  6    job_id,
  7    department_id
  8  )VALUES(
  9    176,
 10    'Taylor',
 11    '[email protected]',
 12    '31-AUG-1999',
 13    'SA_REP',
 14    80
 15  );
  '31-AUG-1999',
  *
ERROR at line 12:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>

What’s wrong with that? It is that my NLS settings don’t recognize such a format. Oracle tried to implicitly convert a string (which is what '31-AUG-1999' is) to a valid DATE datatype value, but failed.

So, what to do? Take control over it! Specify a valid DATE value, for example using a date literal which always looks like DATE 'YYYY-MM-DD':

SQL> INSERT INTO employees(
  2    employee_id,
  3    last_name,
  4    email,
  5    hire_date,
  6    job_id,
  7    department_id
  8  )VALUES(
  9    176,
 10    'Taylor',
 11    '[email protected]',
 12    DATE '1999-08-31',
 13    'SA_REP',
 14    80
 15  );

1 row created.

SQL>

Or, use the TO_DATE function:

SQL> INSERT INTO employees(
  2    employee_id,
  3    last_name,
  4    email,
  5    hire_date,
  6    job_id,
  7    department_id
  8  )VALUES(
  9    177,
 10    'Taylor',
 11    '[email protected]',
 12    to_date('31.08.1999', 'dd.mm.yyyy'),
 13    'SA_REP',
 14    80
 15  );

1 row created.

SQL>

Or, alter session and set NLS settings as you wanted, but that’s not a good idea. You’d rather do as demonstrated earlier:

SQL> alter session set nls_date_format="mon-yyyy-dd";

Session altered.

SQL> alter session set nls_date_language="croatian";

Session altered.

SQL> INSERT INTO employees(
  2    employee_id,
  3    last_name,
  4    email,
  5    hire_date,
  6    job_id,
  7    department_id
  8  )VALUES(
  9    178,
 10    'Taylor',
 11    '[email protected]',
 12    'kol-1999-31',
 13    'SA_REP',
 14    80
 15  );

1 row created.

SQL>

enclose date values with comma:

INSERT INTO employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)  
VALUES(176, 'Taylor', '[email protected]', '31-AUG-1999', 'SA_REP', 80);

see fiddle :
http://sqlfiddle.com/#!4/feb15f/1/0