SQL ERROR: ORA-00904: “TIMESTAMP”: INVALID IDENTIFIER

2
votes

Hi I have created a table and wanting to add data now, I have timestamp as the data type and I am getting errors anyone can help me out? 

CREATE TABLE Production(
ProductionID INT PRIMARY KEY,
ProductionName VARCHAR2(100) NOT NULL,
ProductionDescription VARCHAR2(250) NOT NULL,
ProductionDate DATE NOT NULL,
ProductionTime TIMESTAMP NOT NULL,
ProductionTypeID INT NOT NULL,
NoOfHelpers CHAR(1) NOT NULL);

INSERT INTO PRODUCTION(PRODUCTIONID, PRODUCTIONNAME, PRODUCTIONDATE, PRODUCTIONTIME, PRODUCTIONTYPEID, NOOFHELPERS)
VALUES(1,'THELONDONSINFONIA', TO_DATE('31-AUG-2004','DD-MON-YYYY'), TIMESTAMP('2014-05-05 20:15:00'), 1, 7);

I am using oracle sql developer to create the database

this is the error :

Error starting at line : 10 in command -

INSERT INTO PRODUCTION(PRODUCTIONID, PRODUCTIONNAME, PRODUCTIONDATE, PRODUCTIONTIME, PRODUCTIONTYPEID, NOOFHELPERS) VALUES(1,'THELONDONSINFONIA', TO_DATE('31-AUG-2004','DD-MON-YYYY'), TIMESTAMP('2014-05-05 20:15:00'), 1, 7)

Error at Command Line : 11 Column : 69

Error report -

SQL Error: ORA-00904: "TIMESTAMP": invalid identifier 00904. 00000 - "%s: invalid identifier"

*Cause:

*Action:

1
sqldatabaseoracle

1 Answers

2
votes

timestamp isn't a function where you need to use ( and ), it is a prefix just like n is to indicate a varchar is actually containing Unicode text (so a varchar2 or nvarchar).

So don't use this:

timestamp('2014-05-05 20:15:00')

But this:

timestamp'2014-05-05 20:15:00'